Single result from database using mysqli

Question

I am trying to use mySQLi for the first time. I have done it in the case of loop. Loop results are showing but I am stuck when I try to show a single record. Here is loop code that is working.

<?php
// Connect To DB
$hostname="localhost";
$database="mydbname";
$username="root";
$password="";

$conn = mysqli_connect($hostname, $username, $password, $database);
?>

<?php
$query = "SELECT ssfullname, ssemail FROM userss ORDER BY ssid";
$result = mysqli_query($conn, $query);
$num_results = mysqli_num_rows($result);
?>

<?php
/*Loop through each row and display records */
for($i=0; $i<$num_results; $i++) {
$row = mysqli_fetch_assoc($result);
?>

Name: <?php print $row['ssfullname']; ?>
<br />
Email: <?php print $row['ssemail']; ?>
<br /><br />

<?php 
// end loop
} 
?>

How do I show a single record, any record, name, or email, from the first row or whatever, just a single record, how would I do that? In a single record case, consider all the above loop part removed and let's show any single record without a loop.

Answer 1

When just a single result is needed, then no loop should be used. Just fetch the row right away.

• In case you need to fetch the entire row into associative array:

    $row = $result->fetch_assoc();
  

• in case you need just a single value:

    //starting from PHP 8.1
    $value = $result->fetch_column();
    // for older versions:
    $value = $result->fetch_row()[0] ?? false;
  

Below are complete examples for different use cases

Variables to be used in the query

When variables are to be used in the query, then a prepared statement must be used. For example, given we have a variable $id:

PHP >= 8.2

// get a single row
$sql = "SELECT fullname, email FROM users WHERE id=?";
$row = $conn->execute_query($query, [$id])->fetch_assoc();

// just a single value
$sql = "SELECT count(*) FROM users WHERE id=?";
$count = $conn->execute_query($query, [$id])->fetch_column();

Legacy PHP versions:

// get a single row
$query = "SELECT fullname, email FROM users WHERE id=?";
$stmt = $conn->prepare($query);
$stmt->bind_param("s", $id);
$stmt->execute();
$result = $stmt->get_result();
$row = $result->fetch_assoc();

// just a single value
$query = "SELECT count(*) FROM userss WHERE id=?";
$stmt = $conn->prepare($query);
$stmt->bind_param("s", $id);
$stmt->execute();
$result = $stmt->get_result();
$count = $result->fetch_row()[0] ?? false;

The detailed explanation of the above process can be found in my article. As to why you must follow it is explained in this famous question

No variables in the query

When no variables to be used in the query, you can 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];