Single result for database using mysqli

Question

This is my first time trying mySQLi. I've done it in a loop. The loop results are showing, but when I try to show a single record, I get stuck. This is the loop code in action.

<?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 display a single record, any record, name or email, from the first row or whatever, just a single record, how do I do that? In case of single record, consider removing all the above looping parts and let us display any single record without looping.

Answer 1

Use mysqli_fetch_row(). Try this,

$query = "SELECT ssfullname, ssemail FROM userss WHERE user_id = ".$user_id;
$result = mysqli_query($conn, $query);
$row   = mysqli_fetch_row($result);

$ssfullname = $row['ssfullname'];
$ssemail    = $row['ssemail'];

Answer 2

Loops should not be used when only a single result is required. Just get the row now.

• If you need to extract the entire row into an associative array:

  $row = $result->fetch_assoc();

• If you only need one value, starting with PHP 8.2:

  $value = $result->fetch_column();

• Or for older versions:

  $value = $result->fetch_row()[0] ?? false;

Here are complete examples of different use cases

Variables used in queries

When you want to use variables in a query, you must use prepared statements. For example, let's say 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();

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

Old PHP version:

// 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();

// in case you need 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;

A detailed explanation of the above process can be found in my article . As to why it must be followed, see this famous question

There are no variables in the query

In your case, if no variables are used in the query, you can use the query() method:

$query = "SELECT fullname, email FROM users ORDER BY ssid";
$result = $conn->query($query);
// in case you need an array
$row = $result->fetch_assoc();
// OR in case you need just a single value
$value = $result->fetch_row()[0] ?? false;