How to Efficiently Retrieve a Single Record from a MySQL Database Using mysqli?

Retrieving a Single Record Using mysqli

When working with MySQL databases, situations may arise where you need to retrieve a single record instead of iterating through multiple results. This guide will demonstrate how to fetch a single row or value from a database using mysqli.

Fetching an Associative Array

To fetch an entire row as an associative array, use the following syntax:

$row = $result->fetch_assoc();

This returns an array where the keys are the column names and the values are the respective data.

Fetching a Single Value

If only a single value is required, you can use:

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

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

In the case of PHP versions less than 8.1, the fetch_row() method is used and the first index of the returned array is assigned to the $value variable. The ?? false operator assigns the default value of false if the column is NULL.

Query with Variables

When using variables in a query, a prepared statement must be employed:

// PHP >= 8.2
$row = $conn->execute_query($query, [$id])->fetch_assoc();

// Legacy PHP versions
$stmt = $conn->prepare($query);
$stmt->bind_param("s", $id);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();

No Variables in Query

If no variables are involved, use the query() method:

// Array
$user = $conn->query("SELECT * FROM users LIMIT 1")->fetch_assoc();

// Value
$count = $conn->query("SELECT count(*) FROM users")->fetch_column();

// Value < 8.1
$count = $conn->query("SELECT count(*) FROM users")->fetch_row()[0];

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