PHP mysqli: query() function

Last update on August 19 2022 21:50:40 (UTC/GMT +8 hours)

mysqli_query() function / mysqli::query

The mysqli_query() function / mysqli::query performs a query against the database.

Syntax:

Object oriented style

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

Procedural style

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

Parameter:

Name	Description	Required/Optional
link	A link identifier returned by mysqli_connect() or mysqli_init()	Required for procedural style only and Optional for Object oriented style
query	The query string. Data inside the query should be properly escaped.	Required for procedural style only and Optional for Object oriented style
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() With MYSQLI_ASYNC (available with mysqlnd), it is possible to perform query asynchronously. mysqli_poll() is then used to get results from such queries.	Optional

Usage: Procedural style

mysqli_query(connection,query,resultmode);

Parameter:

Name	Description	Required / Optional
connection	Specifies the MySQL connection to use	Required
query	Specifies the query string	Required
resultmode	A constant. Either: MYSQLI_USE_RESULT (Use this if we have to retrieve large amount of data) MYSQLI_STORE_RESULT (This is default)	Optional

Return value:

Returns FALSE on failure. For successful SELECT, SHOW, DESCRIBE or EXPLAIN queries mysqli_query() will return a mysqli_result object. For other successful queries mysqli_query() will return TRUE.

Version: PHP 5, PHP 7

Example of object oriented style:

<?php
$mysqli = new mysqli("localhost", "user1", "datasoft123", "hr");

/* 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 of procedural style:

<?php
$link = mysqli_connect("localhost", "user1", "datasoft123", "hr");

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

Output:

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

See also

PHP Function Reference

Previous: prepare
Next: real_connect

Weekly Trends and Language Statistics
Weekly Trends and Language Statistics