Fetching One Row Only with 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).

Fetching one row only with MySQLi

Use $row = $result->fetch_assoc(); - it's the same as fetch_row() but returns an associative array.

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 returns only one row

You need to loop through the results (as TheSmose mentioned)

while ($request_list_row = $request_list_result->fetch_array()) {
    echo $request_list['user_fullname'];
}

AND you need to send the resulting array $request_list to the template rather than $request_list_row.

Change this

'request_list' => $request_list_row //right here

to this

'request_list' => $request_list //right here

If you want more than just user_fullname in your template (and you don't have PHP >= 5.3 required for mysqli_result::fetch_all), then you will need to build up your own array inside the loop.

I don't know what your template code expects, but you could try

while ($request_list_row = $request_list_result->fetch_array()) {
    echo $request_list[] = $request_list_row;
}

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

PHP MySQL query - Show row only once

This also works:

select * from
`msg`
where
`state` = 'Heartbeat'
and
`id` =  
(
select
`id`
from
`msg`
where
`state` = 'Heartbeat'
order by `timestamp` desc
limit 1
)

union

select * from
`msg`
where
`state` <> 'Heartbeat'

order by `timestamp` desc

PHP MYSQLI returns one row instead of many

You need to do a loop, fetch_array is designed to grab one row, return whatever format you requested, and increment the row index so that the next call will get the next row.

Try this instead:

if ($results->num_rows > 0){
    for($rowid=0; $rowid < $results->num_rows; $rowid++) {
        $array=$results->fetch_array(MYSQLI_ASSOC);
        print_r($array); //print the current row array
    }
}

Mysqli query is only returning one row (last one)

My initial method of approaching this was very similar to your method and like your it failed. A little research suggested that this method would not work as we both found as each item in the $comments string really needed it's own placeholder and associated type string which lead me to this:

/* output */
$votes=array();

/* original string of IDS */
$comments='1,3,4,5,6,7,11,12,13';

/* create an array from IDS */
$array=explode(',',$comments);

/* create placeholders for each ID */
$placeholders=implode( ',', array_fill( 0, count( $array ), '?' ) );

/* create a type string for each - all going to be `i` */
$types=implode( '', array_fill( 0, count( $array ), 'i' ) );

/* create the sql statement */
$sql=sprintf( 'select `id` from `comment_votes` where `vote` > 0 and `item_id` in ( %s );', $placeholders );

/* create the prepared statement */
$stmt = $db->prepare( $sql );

/* Add the type strings to the beginning of the array */
array_unshift( $array, $types );

if( $stmt ){

    /* bind types and placeholders - 2nd arg passed by reference */
    call_user_func_array( array( $stmt, 'bind_param'), &$array );

    /* execute the query */
    $result=$stmt->execute();

    /* success */
    if( $result ){

        $stmt->store_result();
        $stmt->bind_result( $id );
        $rows=$stmt->num_rows;

        printf( 'rows found: %d<br />',$rows );

        /* add found IDs to output */
        while( $stmt->fetch() ) {
            $votes[]=$id;
        }
        /* tidy up */
        $stmt->free_result();
        $stmt->close();

        /* do something with output */
        printf( '<pre>%s</pre>', print_r( $votes, true ) );

    } else{
        exit('Error: No results');
    }

} else exit('Error: Prepared statement failed');