query

$mysqli->query() runs a single SQL statement against a MySQL database over an open MySQLi connection. It is the simplest way to talk to MySQL from PHP, so it's the right starting point — but as you'll see below, the moment your query contains user input you should reach for prepared statements instead.

This page covers what query() returns, how to read a result set, how it behaves for write queries, and the SQL-injection trap you must avoid.

Syntax

// Object-oriented style
$result = $mysqli->query($sql);

// Procedural style
$result = mysqli_query($mysqli, $sql);

Both forms do the same thing; the OOP style is shown throughout this page.

What query() returns

The return value depends on the kind of statement you run:

Because failure is always false, you can branch on the return value with a plain if. Never assume a query succeeded — a typo'd column name or a missing table both come back as false.

Running a SELECT query

For a SELECT, loop over the returned result and pull each row with fetch_assoc() (or fetch_array()), then free the result when you're done:

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");

// Stop early if the connection failed.
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
    exit();
}

$result = $mysqli->query("SELECT id, name FROM users");

if ($result) {
    while ($row = $result->fetch_assoc()) {
        echo $row["id"] . ": " . $row["name"] . "\n";
    }
    $result->free();          // release the result set's memory
} else {
    echo "Query failed: " . $mysqli->error;
}

$mysqli->close();
?>

The while loop ends when fetch_assoc() returns null (no more rows). $result->free() releases the result set, which matters in long-running scripts that run many queries.

Running INSERT, UPDATE and DELETE

Write queries don't return a result set — they return true on success. To find out how many rows were affected, read $mysqli->affected_rows; to get the auto-increment id of a freshly inserted row, read $mysqli->insert_id:

<?php
$mysqli = new mysqli("localhost", "username", "password", "database");

if ($mysqli->query("UPDATE users SET active = 1 WHERE active = 0")) {
    echo $mysqli->affected_rows . " rows updated";
} else {
    echo "Query failed: " . $mysqli->error;
}

$mysqli->close();
?>

Never put user input directly into a query

query() takes a raw SQL string, so concatenating user input into it opens the door to SQL injection — the most common database security flaw on the web:

// DANGEROUS — do NOT do this
$id = $_GET['id'];
$result = $mysqli->query("SELECT * FROM users WHERE id = $id");

If a visitor sends id=0 OR 1=1, that query returns every user. The fix is a prepared statement, which sends the SQL and the values separately so the values can never be interpreted as SQL:

$stmt = $mysqli->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("i", $_GET['id']);   // "i" = integer
$stmt->execute();
$result = $stmt->get_result();

Use query() only for fixed SQL with no user input; use prepared statements for anything containing values that come from outside your code. See MySQL prepared statements for a full walkthrough.

Summary

• $mysqli->query() executes one SQL statement on an open MySQLi connection.
• A SELECT returns a mysqli_result you iterate with fetch_assoc(); other statements return true; any failure returns false.
• Read affected_rows after a write, and insert_id after an INSERT.
• Switch to prepared statements the instant user input is involved.

Practice