mysqli query results to show all rows

Question

I have the following code:

include $_SERVER['DOCUMENT_ROOT'].'/include/conn.php'; 

$query = "SELECT title FROM news_event";
$result = $mysqli->query($query);
$row = $result->fetch_array(MYSQLI_BOTH);
$row_cnt = $result->num_rows;
$result->free();
$mysqli->close();

This is fine if there is only one result as I can just echo $row['title'] but if there are lots of results, how do I get this to loop through and print every row?

I'm sure this is really simple but I'm just not sure what I need to search for in Google.

I'm looking for a mysqli equivalent of this:

while( $row = mysql_fetch_array($result) )
{
    echo $row['FirstName'] . " " . $row['LastName'];
    echo "<br />";
}

score 30 · Accepted Answer · edited Mar 05 '20 at 19:34

30

Just replace it with mysqli_fetch_array or mysqli_result::fetch_array :)

while( $row = $result->fetch_array() )
{
    echo $row['FirstName'] . " " . $row['LastName'];
    echo "<br />";
}

Almost all mysql_* functions have a corresponding mysqli_* function.

edited Mar 05 '20 at 19:34

jrswgtr

2,287
8
23
49

answered Sep 28 '12 at 20:46

raidenace

12,789
1
32
35

score 10 · Answer 2 · edited Sep 23 '20 at 14:21

Simple mysqli solution:

$db = new mysqli('localhost','user','password','database');
$resource = $db->query('SELECT * FROM table WHERE 1');
while ( $rows = $resource->fetch_assoc() ) {
    print_r($rows);//echo "{$row['field']}";
}
$resource->free();
$db->close();

With Error Handling: If there is a fatal error the script will terminate with an error message.

// ini_set('display_errors',1); // Uncomment to show errors to the end user.
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$db = new mysqli('localhost','user','password','database');
$resource = $db->query('SELECT field FROM table WHERE 1');
while ( $row = $resource->fetch_assoc() ) {
    echo "{$row['field']}";
}
$resource->free();
$db->close();

Using iterators: Support was added with PHP 5.4

$db = new mysqli('localhost','user','password','database');
foreach ( $db->query('SELECT * FROM table') as $row ) {
    print_r($row);//echo "{$row['field']}";
}
$db->close();

Fetch a single record: This code does not require a loop.

$db = new mysqli('localhost','user','password','database');
$resource = $db->query('SELECT field FROM table');
$row = $resource->fetch_assoc();
echo "{$row['field']}";
$resource->free();
$db->close();

Please, [do not use try catch for the error reporting](https://phpdelusions.net/pdo#catch). — Your Common Sense, Oct 29 '16 at 19:14
Hi @YourCommonSense, I agree with you, thanks for the suggestion. As for iterable results, see [example #3 on the PHP documentation for mysqli.](http://php.net/manual/en/mysqli-result.fetch-assoc.php) — jaggedsoft, Oct 29 '16 at 19:16
Please do not handle errors manually. Enable error reporting for mysqli instead. See https://stackoverflow.com/questions/22662488/mysqli-fetch-assoc-expects-parameter-call-to-a-member-function-bind-param — Dharman, Nov 27 '19 at 21:40

score 4 · Answer 3 · answered Sep 28 '12 at 20:49

Use:

while ($row = $result->fetch_array(MYSQLI_BOTH)) {
    // Look inside $row here, do what you want with it.
}

Look at the associative array examples here (should correspond with fetch_array() versions as well):

http://php.net/manual/en/mysqli-result.fetch-assoc.php

score 1 · Answer 4 · edited Mar 06 '20 at 13:11

You can simply loop using foreach

$query = "SELECT title FROM news_event";
$result = $mysqli->query($query);
foreach($result as $row) {
    echo $row['FirstName'] . " " . $row['LastName'];
    echo "<br />";
}

If you would like to keep the mysqli code and HTML separate, then it's good idea to fetch all into an array and loop later.

$query = "SELECT title FROM news_event";
$result = $mysqli->query($query);
$news_events = $result->fetch_all(MYSQLI_ASSOC);

// Later in HTML:    

<div>
<?php foreach($news_events as $row): ?>
    <p><?= $row['FirstName'] ?> <?= $row['LastName'] ?></p>
<?php endforeach ?>
</div>

mysqli query results to show all rows

4 Answers4

Linked

Related