PHP : Function Reference : MySQL Improved Extension : mysqli

Example 1537. 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 1538. Procedural style

copy to clipboard

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

Code Examples / Notes » mysqli_query

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

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!

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';

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']} "; echo "msg : {$row['msg']} "; } ?>

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.

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 } ?>

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.

mysqli_query

Performs a query on the database (PHP 5)
mixed mysqli_query ( mysqli link, string query [, int resultmode] )

Example 1537. Object oriented style

Example 1538. Procedural style

Code Examples / Notes » mysqli_query

Change Language

mysqli_query

Performs a query on the database (PHP 5) mixed mysqli_query ( mysqli link, string query [, int resultmode] )

Example 1537. Object oriented style

Example 1538. Procedural style

Code Examples / Notes » mysqli_query

Change Language

Performs a query on the database (PHP 5)
mixed mysqli_query ( mysqli link, string query [, int resultmode] )