Mysqli fetch single row php

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

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

The last example will return the first column from the first returned row, or false if no row was returned. It can be also shortened to a single line,

$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:

$query = "SELECT ssfullname, ssemail FROM userss 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();
$value = $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

In your case, where no variables to be used in the query, you can use the query() method:

$query = "SELECT ssfullname, ssemail FROM userss 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;

By the way, although using raw API while learning is okay, consider using some database abstraction library or at least a helper function in the future:

// using a helper function
$sql = "SELECT email FROM users WHERE id=?";
$value = prepared_select($conn, $sql, [$id])->fetch_row[0] ?? false;

// using a database helper class
$email = $db->getCol("SELECT email FROM users WHERE id=?", [$id]);

As you can see, although a helper function can reduce the amount of code, a class' method could encapsulate all the repetitive code inside, making you to write only meaningful parts - the query, the input parameters and the desired result format (in the form of the method's name).

❮ PHP MySQLi Reference

Example - Object Oriented style

Fetch rows from a result-set:

$mysqli = new mysqli("localhost","my_user","my_password","my_db");

if ($mysqli -> connect_errno) {
  echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
  exit();
}

$sql = "SELECT Lastname, Age FROM Persons ORDER BY Lastname";

if ($result = $mysqli -> query($sql)) {
  while ($row = $result -> fetch_row()) {
    printf ("%s (%s)\n", $row[0], $row[1]);
  }
  $result -> free_result();
}

$mysqli -> close();
?>

Look at example of procedural style at the bottom.

Definition and Usage

The fetch_row() / mysqli_fetch_row() function fetches one row from a result-set and returns it as an enumerated array.

Syntax

Object oriented style:

$mysqli_result -> fetch_row()

Procedural style:

Parameter Values

Technical Details

Example - Procedural style

Fetch rows from a result-set:

$con = mysqli_connect("localhost","my_user","my_password","my_db");

if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  exit();
}

$sql = "SELECT Lastname, Age FROM Persons ORDER BY Lastname";

if ($result = mysqli_query($con, $sql)) {
  // Fetch one and one row
  while ($row = mysqli_fetch_row($result)) {
    printf ("%s (%s)\n", $row[0], $row[1]);
  }
  mysqli_free_result($result);
}

mysqli_close($con);
?>

❮ PHP MySQLi Reference

Pueblo (USA)
Arvada (USA)
Cape Coral (USA)
Green Bay (USA)
Santa Clara (USA)

How can I get single record in PHP?

How can we fetch data from database row by row in PHP?

How can I get one record details in MySQL database using PHP?

Displaying records using MySQLI functions php"; // MySQL connection string $count="SELECT name,id,class,mark,sex FROM student LIMIT 10"; if($stmt = $connection->query($count)){ echo ""; while ($row = $stmt->fetch_assoc()) { echo "

What does mysql_fetch_row () function do? mysql_fetch_row() retrieves the next row of a result set: When used after mysql_store_result() , mysql_fetch_row() returns NULL if there are no more rows to retrieve. Đọc tiếp

programming php Mysqli_fetch_array Fetch PHP Mysqli_result MySQL num row

Reply 4 0 Chia sẻ