Delicious Bookmark this on Delicious Share on Facebook SlashdotSlashdot It! Digg! Digg



PHP : Function Reference : MySQL Improved Extension : mysqli_query

mysqli_query

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

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

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

jcwebb

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


andrey

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

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


antiriad

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']}
";
 }
?>


beeners

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

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


info

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.


Change Language


Follow Navioo On Twitter
mysqli_affected_rows
mysqli_autocommit
mysqli_bind_param
mysqli_bind_result
mysqli_change_user
mysqli_character_set_name
mysqli_client_encoding
mysqli_close
mysqli_commit
mysqli_connect_errno
mysqli_connect_error
mysqli_connect
mysqli_data_seek
mysqli_debug
mysqli_disable_reads_from_master
mysqli_disable_rpl_parse
mysqli_dump_debug_info
mysqli_embedded_server_end
mysqli_embedded_server_start
mysqli_enable_reads_from_master
mysqli_enable_rpl_parse
mysqli_errno
mysqli_error
mysqli_escape_string
mysqli_execute
mysqli_fetch_array
mysqli_fetch_assoc
mysqli_fetch_field_direct
mysqli_fetch_field
mysqli_fetch_fields
mysqli_fetch_lengths
mysqli_fetch_object
mysqli_fetch_row
mysqli_fetch
mysqli_field_count
mysqli_field_seek
mysqli_field_tell
mysqli_free_result
mysqli_get_charset
mysqli_get_client_info
mysqli_get_client_version
mysqli_get_host_info
mysqli_get_metadata
mysqli_get_proto_info
mysqli_get_server_info
mysqli_get_server_version
mysqli_get_warnings
mysqli_info
mysqli_init
mysqli_insert_id
mysqli_kill
mysqli_master_query
mysqli_more_results
mysqli_multi_query
mysqli_next_result
mysqli_num_fields
mysqli_num_rows
mysqli_options
mysqli_param_count
mysqli_ping
mysqli_prepare
mysqli_query
mysqli_real_connect
mysqli_real_escape_string
mysqli_real_query
mysqli_report
mysqli_rollback
mysqli_rpl_parse_enabled
mysqli_rpl_probe
mysqli_rpl_query_type
mysqli_select_db
mysqli_send_long_data
mysqli_send_query
mysqli_server_end
mysqli_server_init
mysqli_set_charset
mysqli_set_local_infile_default
mysqli_set_local_infile_handler
mysqli_set_opt
mysqli_slave_query
mysqli_sqlstate
mysqli_ssl_set
mysqli_stat
mysqli_stmt_affected_rows
mysqli_stmt_attr_get
mysqli_stmt_attr_set
mysqli_stmt_bind_param
mysqli_stmt_bind_result
mysqli_stmt_close
mysqli_stmt_data_seek
mysqli_stmt_errno
mysqli_stmt_error
mysqli_stmt_execute
mysqli_stmt_fetch
mysqli_stmt_field_count
mysqli_stmt_free_result
mysqli_stmt_get_warnings
mysqli_stmt_init
mysqli_stmt_insert_id
mysqli_stmt_num_rows
mysqli_stmt_param_count
mysqli_stmt_prepare
mysqli_stmt_reset
mysqli_stmt_result_metadata
mysqli_stmt_send_long_data
mysqli_stmt_sqlstate
mysqli_stmt_store_result
mysqli_store_result
mysqli_thread_id
mysqli_thread_safe
mysqli_use_result
mysqli_warning_count
eXTReMe Tracker