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];

Answer 2

As of PHP 8.1, mysqli_result::fetch_column() is available

You can use mysqli_result::fetch_column() to fetch a single scalar value from the result set.

The new method accepts 0-based position of the column you want to read. The default value is 0.

$query = "SELECT ssfullname, ssemail FROM userss WHERE ud=?";
$stmt = $conn->prepare($query);
$stmt->execute([$id])
$result = $stmt->get_result();
$ssemail = $result->fetch_column(1);

Beware that this method will move the internal pointer to the next row, just like the other fetch_* methods do. When it reaches the end, it will return false. Therefore, in the above example, you would be better off using fetch_assoc() to fetch the entire row into an array.

It can also be used with mysqli::query() for SQL statements without parameters.

$query = "SELECT count(*) FROM userss";
$count = $conn->query($query)->fetch_column();

Answer 3

The first answer will do the job but in this case you don't need to loop the result.

$row = $result->fetch_row();

Just echo the column

  echo   $row['column']; 

This should do the trick

Answer 4

Instead of using $row = $query->fetch(); try to use:

$result = $query->get_result();
$row=$result->fetch_assoc();

every time you can check if you get data from mysql tables by displaying them using:

var_export( $row['put your column name here'] );

Answer 5

If you assume just one result you could do this as in Edwin suggested by using specific users id.

$someUserId = 'abc123';

$stmt = $mysqli->prepare("SELECT ssfullname, ssemail FROM userss WHERE user_id = ?");
$stmt->bind_param('s', $someUserId);

$stmt->execute();

$stmt->bind_result($ssfullname, $ssemail);
$stmt->store_result();
$stmt->fetch();

ChromePhp::log($ssfullname, $ssemail); //log result in chrome if ChromePhp is used.

OR as "Your Common Sense" which selects just one user.

$stmt = $mysqli->prepare("SELECT ssfullname, ssemail FROM userss ORDER BY ssid LIMIT 1");

$stmt->execute();
$stmt->bind_result($ssfullname, $ssemail);
$stmt->store_result();
$stmt->fetch();

Nothing really different from the above except for PHP v.5

Answer 6

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'];