How to Efficiently Fetch a Single Record from a MySQLi Database?

Obtaining a Single Record from Database Using MySQLi

Retrieving single rows from a database using MySQLi is distinct from iterating through a series of records. To accomplish this, we utilize the fetch methods provided by MySQLi, eliminating the need for looping.

Fetching a Single Row as an Associative Array

If we require the entire row as an associative array, we can use the following syntax:

$row = $result->fetch_assoc();

This assigns the first row of the result set to the $row variable, which can then be utilized to access individual column values.

Fetching a Single Value

For cases where only a single value is required, we can use:

// PHP 8.1+
$value = $result->fetch_column();

// Older PHP versions
$value = $result->fetch_row()[0] ?? false;

The latter syntax checks for null values and returns false if encountered.

Example Usage

Consider the example query to fetch the first row of the "users" table:

$sql = "SELECT * FROM users LIMIT 1";
$row = mysqli_query($conn, $sql)->fetch_assoc();

Now, we can access specific values using $row['column_name'], such as:

$name = $row['name'];
$email = $row['email'];

Using Variables in Queries

When using variables in SQL queries, it's crucial to employ prepared statements to prevent SQL injection. For instance, to fetch a row based on a specific ID:

// PHP 8.2+
$id = 123;
$sql = "SELECT * FROM users WHERE id = ?";
$row = $conn->execute_query($sql, [$id])->fetch_assoc();

// Older PHP versions
$id = 123;
$stmt = $conn->prepare("SELECT * FROM users WHERE id = ?");
$stmt->bind_param("s", $id);
$stmt->execute();
$row = $stmt->get_result()->fetch_assoc();

The above is the detailed content of How to Efficiently Fetch a Single Record from a MySQLi Database?. For more information, please follow other related articles on the PHP Chinese website!