Web студия "GrandView"

Description

Procedural style:

mixed mysqli_query ( mysqli link, string query [, int resultmode] )

Object oriented style (method):

class mysqli {

mixed query ( string query [, int resultmode] )

}

The mysqli_query() function is used to simplify the act of performing a query against the database represented by the link parameter.

Functionally, using this function is identical to calling mysqli_real_query() followed either by mysqli_use_result() or mysqli_store_result() where query is the query string itself and resultmode is either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, if the resultmode is not provided MYSQLI_STORE_RESULT is used.

If you execute mysqli_query() with resultmode MYSQLI_USE_RESULT all subsequent calls will return error Commands out of sync unless you call mysqli_free_result().

Примеры

Пример 1. Object oriented style


<?php

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");



/* check connection */

if (mysqli_connect_errno()) {

    printf("Connect failed: %s\n", mysqli_connect_error());

    exit();

}



/* Create table doesn't return a resultset */

if ($mysqli->query("CREATE TEMPORARY TABLE myCity LIKE City") === TRUE) {

    printf("Table myCity successfully created.\n");

}



/* Select queries return a resultset */

if ($result = $mysqli->query("SELECT Name FROM City LIMIT 10")) {

    printf("Select returned %d rows.\n", $result->num_rows);



    /* free result set */

    $result->close();

}



/* If we have to retrieve large amount of data we use MYSQLI_USE_RESULT */

if ($result = $mysqli->query("SELECT * FROM City", MYSQLI_USE_RESULT)) {



    /* Note, that we can't execute any functions which interact with the

       server until result set was closed. All calls will return an 

       'out of sync' error */

    if (!$mysqli->query("SET @a:='this will not work'")) {

        printf("Error: %s\n", $mysqli->error);

    }

    $result->close();

}



$mysqli->close();

?>

Пример 2. Procedural style


<?php

$link = mysqli_connect("localhost", "my_user", "my_password", "world");



/* check connection */

if (mysqli_connect_errno()) {

    printf("Connect failed: %s\n", mysqli_connect_error());

    exit();

}



/* Create table doesn't return a resultset */

if (mysqli_query($link, "CREATE TEMPORARY TABLE myCity LIKE City") === TRUE) {

    printf("Table myCity successfully created.\n");

}



/* Select queries return a resultset */

if ($result = mysqli_query($link, "SELECT Name FROM City LIMIT 10")) {

    printf("Select returned %d rows.\n", mysqli_num_rows($result));



    /* free result set */

    mysqli_free_result($result);

}



/* If we have to retrieve large amount of data we use MYSQLI_USE_RESULT */

if ($result = mysqli_query($link, "SELECT * FROM City", MYSQLI_USE_RESULT)) {



    /* Note, that we can't execute any functions which interact with the

       server until result set was closed. All calls will return an 

       'out of sync' error */

    if (!mysqli_query($link, "SET @a:='this will not work'")) {

        printf("Error: %s\n", mysqli_error($link));

    }

    mysqli_free_result($result);

}



mysqli_close($link);

?>

Результат выполнения данного примера:

Table myCity successfully created.
Select returned 10 rows.
Error: Commands out of sync;  You can't run this command now

add a note User Contributed Notes
mysqli_query

jcwebb at dicoe dot com
15-May-2007 01:42


When building apps, i like to see the whole statement when if fails.

<?php

$q="SELECT somecolumn FROM sometable"; //some instruction

$r=mysqli_query($DBlink,$q) or die(mysqli_error($DBlink)." Q=".$q);

?>

If theres an error (like my numerous typing mistakes) this shows the entire instruction.

Good for development (not so good on production servers - simply find and replace when finished: $r=mysqli_query($DBlink,$q); )



Hope it helps. Jon

antiriad at gmail dot com
12-Mar-2007 11:55


this is a variant of mysqli_query that returns output parameters as a rowset.



<?php

  function mysqli_exec($link, $command)

  {

      $select = '';

      $i2 = 0;

      

      while (true)

      {

      $i1 = strpos($command, '@', $i2);

      

      if ($i1 === false)

          break;

          

      $field = '';

      $i2 = $i1 + 1;

      

      while ($i2 < strlen($command) && 

          ($command[$i2] >= '0' && $command[$i2] <= '9') ||

          ($command[$i2] >= 'A' && $command[$i2] <= 'Z') ||

          ($command[$i2] >= 'a' && $command[$i2] <= 'z') ||

          ($command[$i2] == '_'))

          $i2++;

          

      $field = substr($command, $i1 + 1, $i2 - $i1 - 1);

          

      if (strlen($select) == 0)

        $select = "select @{$field} as $field";

      else                    

          $select = $select . ", @{$field} as $field";

      }

      

      if (strlen($select) > 0)

      {      

        mysqli_query($link, $command);

        return mysqli_query($link, $select);

      }

      else

        return mysqli_query($link, $command);

  }

?>



an example:



<?php

  $link = mysqli_connect('localhost', 'myusr', 'mypass') or die ('Error connecting to mysql: ' . mysqli_error($link));

  mysqli_select_db($link, 'clips');

  

  $user_name = 'test';

  $result = mysqli_exec($link, "call do_user_login('$user_name', @session_id, @msg)");

  

  while ($row = mysqli_fetch_assoc($result))

  {

    echo "session_id : {$row['session_id']} <br>";

    echo "msg        : {$row['msg']} <br>";

  }

?>

info at ff dot net
08-May-2006 04:12


Calling Stored Procedures



Beeners' note/example will not work. Use mysqli_multi_query() to call a Stored Procedure. SP's have a second result-set which contains the status: 'OK' or 'ERR'. Using mysqli_query will not work, as there are multiple results.



<?php

$sQuery="CALL SomeSP('params')";

if(!mysqli_multi_query($sqlLink,$sQuery)) {

  // your error handler 

}

$sqlResult=mysqli_store_result($sqlLink);



if(mysqli_more_results($this->sqlLink))//Catch 'OK'/'ERR'

  while(mysqli_next_result($this->sqlLink));

?>



You will have to rewrite/expand this a bit for more usability of course, but it's just an example.

Beeners
08-Jan-2006 12:16


Stored Procedures.



Use mysqli_query to call a stored procedure that returns a result set. 



Here is a short example:



<?php

$mysqli = new mysqli(DBURI,DBUSER,DBPASS,DBNAME);

if (mysqli_connect_errno()) 

{

  printf("Connection failed: %s\n", mysqli_connect_error());

  exit();

}



$SQL = "CALL my_procedure($something)";

if ( ($result = $mysqli->query($SQL))===false )

{

  printf("Invalid query: %s\nWhole query: %s\n", $mysqli->error, $SQL);

  exit();

}



while ($myrow = $result->fetch_array(MYSQLI_ASSOC))

{

  $aValue[]=$myrow["a"];

  $bValue[]=$myrow["b"];

}

$result->close();

$mysqli->close();

?>

I hope this saves someone some time.

ole_v2 at dsl dot pipex dot com
25-Aug-2005 08:22


SENDING MULTIPLE QUERIES



use mysqli_multi_query()



This is ilegal:

<?

// assume $db is a sucessful connection to database

$result = mysqli_query($db,'SELECT * FROM sometable; SHOW DATABASES;'); // always false

if ($result) {

   // code here will never execute

}

?>

andrey at php dot net
07-Aug-2005 12:03


WARNING: This function was buggy on 64bit machines till 5.0.5. Affected versions 5.0.0-5.0.4. The problem appears when a value for the third parameter is passed - this will lead to instant FALSE returned by the function. Therefore if you need to use unbuffered query don't use this function with the aforementioned versions but you mysqli_real_query() and mysqli_use_result().

If you have the rights to patch you PHP installation the fix is easy:

In file ext/mysqli/myslqi_nonapi.c, function PHP_FUNCTION(mysqli_query)

change

unsigned int resultmode=0;

to

unsigned long resultmode=0;



Thanks!

hunreal at gmail dot com
13-Aug-2004 02:24


Use difference collation/character for connect, result.

You can set the collation before your query.



E.g. want to set the collation to utf8_general_ci 

you can send the query "SET NAMES 'utf8'" first



<?php

$mysqli=new mysqli('localhost', 'root', 'password', 'test');

$mysqli->query("SET NAMES 'utf8'");

$q=$mysqli->query("select * from test");

while($r=$q->fetch_assoc()) {

    print_r($r);

}

?>



There are many variables about character settings.

By running sql command, SHOW VARIABLES LIKE 'char%'; 

There are some variables control the character usage.



character_set_client

character_set_connection

character_set_database

character_set_results

character_set_server

character_set_system



Also SET NAMES can repalce with one or some settings like SET character_set_results='utf8';

add a note

mysqli_query

Description

Возвращаемые значения

Смотрите также

Примеры