mysqli::query

mysqli_query

(PHP 5, PHP 7, PHP 8)

mysqli::query -- mysqli_query — Ejecuta una consulta en la base de datos

Descripción

Estilo orientado a objetos

public mysqli::query(string $query, int $result_mode = MYSQLI_STORE_RESULT): mysqli_result|bool

Estilo por procedimientos

mysqli_query(mysqli $mysql, string $query, int $result_mode = MYSQLI_STORE_RESULT): mysqli_result|bool

Ejecuta una consulta en la base de datos.

Advertencia

Security warning: SQL injection

If the query contains any variable input then parameterized prepared statements should be used instead. Alternatively, the data must be properly formatted and all strings must be escaped using the mysqli_real_escape_string() function.

Para consultas no-DML (que no son un INSERT, un UPDATE o un DELETE), esta función es similar a llamar a mysqli_real_query() seguida de mysqli_use_result() o mysqli_store_result().

Nota:
Si se pasa una consulta a mysqli_query() que es más larga que max_allowed_packet, los códigos de error devueltos serán diferentes según si se utiliza MySQL Native Driver (mysqlnd) o la MySQL Client Library (libmysqlclient). El comportamiento se define como sigue:

mysqlnd en Linux devuelve un código de error 1153. El mensaje de error será got a packet bigger than max_allowed_packet bytes.

mysqlnd en Windows devuelve un código de error 2006. El mensaje será del tipo server has gone away.

libmysqlclient en cualquier plataforma devuelve el código de error 2006. El mensaje será del tipo server has gone away.

Parámetros

link

Sólo estilo por procediminetos: Un identificador de enlace devuelto por mysqli_connect() o mysqli_init()

query

La consulta, en forma de string.

result_mode

El modo de resultado puede ser una de las 3 constantes que indican cómo el resultado será devuelto por el servidor MySQL.

MYSQLI_STORE_RESULT (por omisión) - devuelve un objeto mysqli_result con un conjunto de resultados almacenados en búfer.

MYSQLI_USE_RESULT - devuelve un objeto mysqli_result con un conjunto de resultados no almacenados en búfer. Mientras haya registros pendientes de ser recuperados, la línea de conexión estará ocupada y todas las llamadas siguientes devolverán el error Commands out of sync. Para evitar el error, todos los registros deben ser recuperados del servidor o el conjunto de resultados debe ser descartado llamando a la mysqli_free_result().

MYSQLI_ASYNC (disponible con mysqlnd) - la consulta se ejecuta de manera asíncrona y ningún conjunto de resultados es devuelto inmediatamente. mysqli_poll() se utiliza entonces para obtener los resultados de tales consultas. Utilizada en combinación con la constante MYSQLI_STORE_RESULT o MYSQLI_USE_RESULT.

Valores devueltos

Devuelve false en caso de fallo. Para consultas exitosas que producen un conjunto de resultados como SELECT, SHOW, DESCRIBE o EXPLAIN, mysqli_query() devolverá un objeto mysqli_result. Para otros tipos de consultas exitosas, mysqli_query() devolverá true.

Errores/Excepciones

If mysqli error reporting is enabled (MYSQLI_REPORT_ERROR) and the requested operation fails, a warning is generated. If, in addition, the mode is set to MYSQLI_REPORT_STRICT, a mysqli_sql_exception is thrown instead.

Ejemplos

Ejemplo #1 Ejemplo con mysqli::query()

Estilo orientado a objetos

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* "Create table" no devolverá ningún conjunto de resultados */
$mysqli->query("CREATE TEMPORARY TABLE myCity LIKE City");
printf("Tabla myCity creada con éxito.\n");

/* Consulta "Select" devuelve un conjunto de resultados */
$result = $mysqli->query("SELECT Name FROM City LIMIT 10");
printf("Select ha devuelto %d líneas.\n", $result->num_rows);

/* Si tenemos que recuperar muchos datos, utilizamos MYSQLI_USE_RESULT */
$result = $mysqli->query("SELECT * FROM City", MYSQLI_USE_RESULT);

/* Tenga en cuenta que no podemos ejecutar ninguna función que actúe en el servidor mientras
   el conjunto de resultados no esté cerrado. Todas las llamadas devolverán un 'out of sync' */
$mysqli->query("SET @a:='this will not work'");

Estilo por procedimientos

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$link = mysqli_connect("localhost", "my_user", "my_password", "world");

/* "Create table" no devolverá ningún conjunto de resultados */
mysqli_query($link, "CREATE TEMPORARY TABLE myCity LIKE City");
printf("Tabla myCity creada con éxito.\n");

/* Consulta "Select" devuelve un conjunto de resultados */
$result = mysqli_query($link, "SELECT Name FROM City LIMIT 10");
printf("Select ha devuelto %d líneas.\n", mysqli_num_rows($result));

/* Si tenemos que recuperar muchos datos, utilizamos MYSQLI_USE_RESULT */
$result = mysqli_query($link, "SELECT * FROM City", MYSQLI_USE_RESULT);

/* Tenga en cuenta que no podemos ejecutar ninguna función que actúe en el servidor mientras
   el conjunto de resultados no esté cerrado. Todas las llamadas devolverán un 'out of sync' */
mysqli_query($link, "SET @a:='this will not work'");

El resultado de los ejemplos sería:

Tabla myCity creada con éxito.
Select ha devuelto 10 líneas.
Fatal error: Uncaught mysqli_sql_exception: Commands out of sync; you can't run this command now in...

Ver también

mysqli_execute_query() - Prepara, vincula los parámetros y ejecuta una sentencia SQL
mysqli_real_query() - Ejecuta una consulta SQL
mysqli_multi_query() - Ejecuta una o varias consultas en la base de datos
mysqli_prepare() - Prepara una consulta SQL para su ejecución
mysqli_free_result() - Libera la memoria asociada a un resultado

Found A Problem?

Learn How To Improve This Page • Submit a Pull Request • Report a Bug

＋add a note

User Contributed Notes 8 notes

down

NUNTIUS ¶

17 years ago

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.



<?php

$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.

down

theyranos at gmail dot com ¶

14 years ago

The cryptic "Couldn't fetch mysqli" error message can mean any number of things, including:

1. You're trying to use a database object that you've already closed (as noted by ceo at l-i-e dot com). Reopen your database connection, or find the call to <?php mysqli_close($db); ?> or <?php $db->close(); ?> and remove it.
2. Your MySQLi object has been serialized and unserialized for some reason. Define a wakeup function to re-create your database connection. http://php.net/__wakeup 
3. Something besides you closed your mysqli connection (in particular, see http://bugs.php.net/bug.php?id=33772)
4. You mixed OOP and functional calls to the database object. (So, you have <?php $db->query() ?> in the same program as <?php mysqli_query($db) ?>).

down

petrus.jvr ¶

14 years ago

When calling multiple stored procedures, you can run into the following error: "Commands out of sync; you can't run this command now".
This can happen even when using the close() function on the result object between calls. 
To fix the problem, remember to call the next_result() function on the mysqli object after each stored procedure call. See example below:

<?php
// New Connection
$db = new mysqli('localhost','user','pass','database');

// Check for errors
if(mysqli_connect_errno()){
 echo mysqli_connect_error();
}

// 1st Query
$result = $db->query("call getUsers()");
if($result){
     // Cycle through results
    while ($row = $result->fetch_object()){
        $user_arr[] = $row;
    }
    // Free result set
    $result->close();
    $db->next_result();
}

// 2nd Query
$result = $db->query("call getGroups()");
if($result){
     // Cycle through results
    while ($row = $result->fetch_object()){
        $group_arr[] = $row;
    }
     // Free result set
     $result->close();
     $db->next_result();
}
else echo($db->error);

// Close connection
$db->close();
?>

down

xa at sagexa dot com ¶

2 years ago

"In PHP 8.1, the default error handling behavior of the MySQLi extension has changed from silencing errors to throw an Exception on errors. "
This is true even for the procedural use of mysqli, i.e. mysqli_query.
Hence, using if(!mysqli_query($c, $sql)) [...] is pointless.
To disable Exception throwing : mysqli_report(MYSQLI_REPORT_OFF);

down

Anonymous ¶

7 years ago

Here is an example of a clean query into a html table

<table>
   <tr>
     <th>First Name</th>
     <th>Last Name</th>
     <th>City</th>
   </tr>
   <?php while ($row = $myquery->fetch_assoc()) { ?>
   <tr>
     <td><?php echo $row["firstname"]; ?></td>
     <td><?php echo $row["lastname"]; ?></td>
     <td><?php echo $row["city"];?></td>
   </tr>
   <?php } ?>
 </table>

down

Beeners ¶

19 years ago

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.

down

-1

registrations at jdfoxmicro dot com ¶

14 years ago

I like to save the query itself in a log file, so that I don't have to worry about whether the site is live.



For example, I might have a global function:



<?php

function UpdateLog ( $string , $logfile )  {

   $fh = fopen ( $logfile , 'a' );

   $fwrite ( $fh , strftime ('%F %T %z')." ".$string."\n";

   fclose ( $fh );

}

?>



Then in my mysql function error trapper, something like this:



<?php

   $error_msg = "Database error in [page].php / ";

   $error_msg .= mysqli_error ( $link )." / ";

   $error_msg .= $query;

   UpdateLog ( $error_msg , DB_ERROR_LOG_FILE );

?>



I also include the remote IP, user agent, etc., but I left it out of these code samples.  And have it e-mail me when an error is caught, too.



Jeff

down

-1

ceo at l-i-e dot com ¶

16 years ago

Translation:
"Couldn't fetch mysqli"

You closed your connection and are trying to use it again.

It has taken me DAYS to figure out what this obscure error message means...

＋add a note