How to Efficiently Retrieve a Single Database Record Using MySQLi?

Retrieving a Single Record from a Database Using MySQLi

Retrieving a single record from a database can be achieved using MySQLi without resorting to loops. Understanding the syntax and techniques involved is crucial for efficient data fetching.

Fetching an Associative Array Row

To retrieve an entire row as an associative array, use the fetch_assoc() method of the result object. For instance, to retrieve a row from a "users" table:

<?php
// Connect to the database
$query = "SELECT * FROM users LIMIT 1";
$result = $conn->query($query);
$row = $result->fetch_assoc();

// Print the user's name
echo "Name: {$row['name']}";
?>

Fetching a Single Value

If only a specific value is required, the fetch_column() method (PHP 8.1 or later) can be used. For PHP versions prior to 8.1, use the fetch_row()[0] syntax. For example, to retrieve the count of users in the database:

<?php
// Connect to the database
$query = "SELECT COUNT(*) FROM users";
$result = $conn->query($query);
$count = $result->fetch_column();

// Print the user count
echo "Total users: {$count}";
?>

Using Prepared Statements with Variables

If variables need to be used in the query, prepared statements must be employed to prevent SQL injection. For PHP 8.2 or later, use the execute_query() method:

<?php
// Connect to the database
$query = "SELECT * FROM users WHERE id = ?";
$id = 1;
$row = $conn->execute_query($query, [$id])->fetch_assoc();

// Print the user's name
echo "Name: {$row['name']}";
?>

For PHP versions prior to 8.2, prepare and execute the statement manually:

<?php
// Connect to the database
$query = "SELECT * FROM users WHERE id = ?";
$stmt = $conn->prepare($query);
$stmt->bind_param('s', $id); // Bind the variable as a string
$id = 1;
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();

// Print the user's name
echo "Name: {$row['name']}";
?>

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