Question:

How to make a single result from a database using MySQL?

Problem:

I am using MySQL for the first time. I have done it in the case of a loop. Loop results are showing but I need help when I try to show a single record. Here is the 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']; ?>

<?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 parts removed, and let's show any single record without a loop.

Solution 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.2:

$value = $result->fetch_column();

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

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

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

Answered by: >Your Common Sense

Credit: >Stackoverflow

Solution 2:

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

Answered by: >Edwin Alex

Credit: >Stackoverflow

Solution 3:

If you assume just one result you could do this as in >Edwin suggested by using a 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 selects just one user as >Your Common Sense said

$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

Answered by: >MFAL

Credit: >Stackoverflow

>Complete guide to Perform crud operation in angular using modal popup

>What is pipe in Angular?

>How To Create Nested Table Structure In Angular?

>How to Upload files and JSON data in the same request with Angular?

Mysql

Ritu Singh

Your Answer

Submit

Ritu Singh

0 Answers

Top Questions

Ritu Singh

0 Answers

Top Questions

Related Questions