PHP mysqli_query() Function

PHP mysqli_query() runs a SQL query on a MySQL database. You can use it to insert, select, update or delete records.

It is the main function of the MySQLi extension. First you connect to the database (see PHP MySQL Connect), and then you send the SQL text to MySQL using mysqli_query().

---

Syntax

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

// Object oriented style
$mysqli->query(string $query, int $result_mode = MYSQLI_STORE_RESULT): mysqli_result|bool

Read about both styles in MySQLi Procedural vs Object Oriented.

Parameters

Parameter	Description
mysql	Required. The connection created by mysqli_connect()
query	Required. The SQL query as a string
result_mode	Optional. MYSQLI_STORE_RESULT (default) loads all rows in memory. MYSQLI_USE_RESULT reads rows one by one, useful for very big results

Return value

Type of query	Returns
SELECT, SHOW, DESCRIBE, EXPLAIN	A mysqli_result object
INSERT, UPDATE, DELETE, CREATE and others	true
Query fails	false (or throws an exception in PHP 8.1 and above)

---

Examples

All examples use the student database and the users table that we created in PHP MySQL Create Table. The connection code is saved in a separate file, so we can include it in every example.

Connection file: db-connection.php

<?php
// db-connection.php
$database_server = 'localhost';
$database_username = 'root';
$database_password = '';
$database_name = 'student';

// Create connection using mysqli_connect()
$conn = mysqli_connect($database_server, $database_username, $database_password, $database_name);

// If $conn is false, connection is failed
if (!$conn) {
    die("Failed to connect to MySQL: " . mysqli_connect_error());
}
?>

---

Example #1: Insert a record

For an INSERT query mysqli_query() returns true. We can use mysqli_affected_rows() to know how many rows were added.

<?php
require_once('db-connection.php');

$sql = "INSERT INTO users (name, mobile, email)
        VALUES ('Ram', '9898989898', 'ram@example.com')";

$result = mysqli_query($conn, $sql);

if ($result) {
    echo "Record added. Rows affected: " . mysqli_affected_rows($conn);
}

mysqli_close($conn);
?>

Output:

The email column is UNIQUE, so if you run this code two times the second run will fail with a “Duplicate entry” error.

---

Example #2: Select records

For a SELECT query the function returns a result object. Here we count the rows with mysqli_num_rows() and read each row using mysqli_fetch_assoc(). Suppose the table has these two records.

<?php
require_once('db-connection.php');

$result = mysqli_query($conn, "SELECT id, name, email FROM users");

echo "Total rows: " . mysqli_num_rows($result) . "<br>";

while ($row = mysqli_fetch_assoc($result)) {
    echo $row["id"] . " - " . $row["name"] . " - " . $row["email"] . "<br>";
}

mysqli_close($conn);
?>

Output:

---

Example #3: Update a record

<?php
require_once('db-connection.php');

$sql = "UPDATE users SET mobile = '9000000000' WHERE id = 1";

if (mysqli_query($conn, $sql)) {
    echo "Rows updated: " . mysqli_affected_rows($conn);
}

mysqli_close($conn);
?>

Output:

---

Example #4: Handle errors

Here we run a query on a table that does not exist. In PHP 8.1 and above, we can catch the exception.

<?php
require_once('db-connection.php');

try {
    $result = mysqli_query($conn, "SELECT * FROM no_such_table");
} catch (mysqli_sql_exception $e) {
    echo "Query failed: " . $e->getMessage();
}

mysqli_close($conn);
?>

Output:

In older PHP versions, check the result and print the error like this: if (!$result) { echo mysqli_error($conn); }

---

Never put user input directly in the query

This is the most common and dangerous mistake. If a visitor can change the value inside the SQL, the visitor can read or delete your data. This attack is called SQL injection.

<?php
// Wrong: the value comes from the URL and goes directly in SQL
$id = $_GET["id"];
$result = mysqli_query($conn, "SELECT * FROM users WHERE id = $id");
?>

If someone opens the page with ?id=1 OR 1=1, the query returns all users. The safe way is to use prepared statements. Learn it in PHP MySQL Prepared Statements.

---

Summary

• mysqli_query() sends a SQL query to the MySQL database.
• It returns a result object for SELECT and true for INSERT, UPDATE and DELETE.
• Use mysqli_affected_rows() and mysqli_num_rows() to know how many rows are changed or found.
• From PHP 8.1 errors throw an exception, so use try and catch.
• Do not use it with raw user input. Use prepared statements instead.

---

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.