mysqli::prepare

Last updated: Fri, 10 Oct 2008

mysqli::query

mysqli_query

(PHP 5)

mysqli::query -- mysqli_query — Performs a query on the database

Description

Object oriented style (method):

mixed mysqli::query ( string $query [, int $resultmode ] )

Procedural style:

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

Performs a query against the database.

Functionally, using this function is identical to calling mysqli_real_query() followed either by mysqli_use_result() or mysqli_store_result().

Parameters

link

Procedural style only: A link identifier returned by mysqli_connect() or mysqli_init()

query

The query string.

resultmode

Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

If you use MYSQLI_USE_RESULT all subsequent calls will return error Commands out of sync unless you call mysqli_free_result()

Return Values

Returns TRUE on success or FALSE on failure. For SELECT, SHOW, DESCRIBE or EXPLAIN mysqli_query() will return a result object.

Examples

Example #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();
?>

Example #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);
?>

The above example will output:

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


This may or may not be obvious to people but perhaps it will help someone.



When running joins in SQL you may encounter a problem if you are trying to pull two columns with the same name. mysqli returns the last in the query when called by name. So to get what you need you can use an alias.



Below I am trying to join a user id with a user role. in the first table (tbl_usr), role is a number and in the second is a  text name (tbl_memrole is a lookup table). If I call them both as role I get the text as it is the last "role" in the query. If I use an alias then I get both as desired as shown below.



$sql = "SELECT a.uid, a.role AS roleid, b.role, 

            FROM tbl_usr a

            INNER JOIN tbl_memrole b

            ON a.role = b.id

            ";



    if ($result = $mysqli->query($sql)) {

        while($obj = $result->fetch_object()){

            $line.=$obj->uid;

            $line.=$obj->role;

            $line.=$obj->roleid;

        }

    }

        $result->close();

        unset($obj);

        unset($sql);

        unset($query);

    }



In this situation I guess I could have just renamed the role column in the first table roleid and that would have taken care of it, but it was a learning experience.

jcwebb at dicoe dot com
15-May-2007 04: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 02: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 07: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 03: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.

andrey at php dot net
07-Aug-2005 03: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 05: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::real_connect