Single Result from Database Using MySQLi

Single result from database using mysqli

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).

how to get a single database value using mysqli (php)?

You forgot a E in youy WHERE clause

$result = mysqli_query($con, "SELECT * FROM test_table WHERE ID='" . $_GET['ID'] . "'");
$row = $result->fetch_assoc();  
$test= $row['ID'];
echo $test;

If your 'ID' field is a integer, quotes are not necessary.

How to fetch a single row from a MySQL DB using MySQLi with PHP?

Add LIMIT 1 to the end of your query to produce a single row of data.

Your method is vulnerable to SQL injection. Use prepared statements to avoid this. Here are some links you can review:

• What is SQL injection?

• https://en.wikipedia.org/wiki/SQL_injection

• https://phpdelusions.net/mysqli_examples/prepared_select

---

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset("utf8mb4");

$txnid= $_GET['name_of_txnid_input_field'];

// prepare and bind
$stmt = $conn->prepare("SELECT * FROM `testtable1` WHERE `txnid` = ? LIMIT 1");
$stmt->bind_param("i", $txnid);

// set parameters and execute
$stmt->execute();

$result = $stmt->get_result();
$row = $result->fetch_assoc();
echo $row['date_field_you_want_to_display'];

MYSQLI query to get one single result

You can use:

mysqli_fetch_array();

// For Instance

$id_get = mysqli_query($con, "SELECT id FROM membrs WHERE username='$username' LIMIT 1");

$id = mysqli_fetch_array($id_get);

echo $id['id']; // This will echo the ID of that user

// What I use is the following for my site:

$user_get = mysqli_query($con, "SELECT * FROM members WHERE username='$username'");

$user = mysqli_fetch_array($user);

echo $user['username']; // This will echo the Username

echo $user['fname']; // This will echo their first name (I used this for a dashboard)

How to get a single value from a query result in php

Your variable $table is an array, so if you want only the userid, you just have to use

$table[0]['userid']

If you want to query only the userid change you query like this :

$query = mysqli_query( $connection,"select userid from register where password='$log_in_password' AND username='$log_in_username'");
$table = mysqli_fetch_all($query,MYSQLI_ASSOC);

And the result of $table will be :

array (size=1)
  0 => 
    array (size=1)
      'userid' => string '7' (length=1)

$table will also be an array, and to acces userid you have to use again

$table[0]['userid']

How to get single column values using MySQLi?

do fetch_assoc

while($row = $result->fetch_assoc()) {
  $rows[]=$row['EmailAddress'];
}

MySQLi only showing one result

Try a while loop:

while($row = $sql->fetch_row())
{
   var_dump($row);
}

Because fetch_row(), fetch_array(), fetch_assoc() will all return one row every singe time it's being called untill it is 'out of rows'.

Single Result From SUM With MySQLi

Whatever is given in the SELECT statement to mysqli_query is going to return a mysql_result type if the query was successful. So if you have a SELECT statement such as:

SELECT sum(field) FROM table1

you still need to fetch the row with the result, and the value of the sum() function will be the only entry in the row array:

$res = mysqli_query($dbh,'SELECT sum(field) FROM table1');
$row = mysqli_fetch_row($res);
$sum = $row[0];

Get single row using mysqli from mysql db

Just try this:

$getfield = mysqli_query($con,"select name from as_users where user_id=34");
if (mysqli_num_rows($getfield) > 0) 
{
     $rowpwd = mysqli_fetch_array($getfield)['name'];
     echo $rowpwd;
}

OR

$getfield = mysqli_query($con,"select name from as_users where user_id=34");
if (mysqli_num_rows($getfield) > 0) 
{
   $rowpwd = mysqli_fetch_array($getfield);
   echo $rowpwd['name'];
}

---

Related Topics