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.

Actually i want to remove the loop part and and without limit i want to get one record with query to show single record. — WebStylePress
– WebStylePress, Commented Jan 31, 2013 at 11:24

Your Common Sense · Accepted Answer · 2023-12-19 12:46:25Z

89

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

edited Dec 19, 2023 at 12:46

answered Jan 31, 2013 at 11:53

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

WebStylePress Over a year ago

Yes that's object oriented programming. It scares me bcoz it seems difficult. But I want to learn it badly. Please recommend a place/source where I can learn about classes and use of class and objects etc.

Your Common Sense Over a year ago

You need to know OOP only to write classes. While using them do not require no special knowledge, it is as easy as calling a regular function. The only difference is a little prefix $class-> before function name. The rest is the same.

Funk Forty Niner · Accepted Answer · 2015-02-15 17:26:58Z

12

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

edited Feb 15, 2015 at 17:26

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

answered Jan 31, 2013 at 11:32

Edwin Alex

5,0944 gold badges31 silver badges51 bronze badges

1 Comment

ironarm Over a year ago

You can't do that with mysqli_fetch_row as it returns an enumerated array. Ex: $row[0], $row[1] You need to use mysqli_fetch_assoc, which returns an associative array, if you want to specify field names. Ex: $row['ssfullname'];

MFAL · Accepted Answer · 2015-04-29 16:01:45Z

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

Ezekiel Arin · Accepted Answer · 2021-07-22 21:30:50Z

2

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

answered Jul 22, 2021 at 21:30

Ezekiel Arin

1456 bronze badges

Comments

Dharman · Accepted Answer · 2021-09-25 12:26:06Z

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

marc_s · Accepted Answer · 2021-07-22 21:03:16Z

0

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

edited Jul 22, 2021 at 21:03

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 27, 2021 at 16:02

Suhad Al Dulaimy

193 bronze badges

Collectives™ on Stack Overflow

Single result from database using mysqli

6 Answers 6

Variables to be used in the query

No variables in the query

2 Comments

1 Comment

Comments

Comments

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

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Variables to be used in the query

No variables in the query

2 Comments

1 Comment

Comments

Comments

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

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related

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