How to loop through a mysqli prepared statement and output the results?

Question

I have the below page. The database contains an accounts table, and a hotspots table. The hotspots table has multiple records in it, tied bo the unique user ID of the accounts table - so there may be more than 1 result if the user has more than 1 hotspot. What I want to do is loop through the results and output the name (or any other details) from the database. I've tried a few things, but not been able to get it to work.

Here is my code:

<?php
include 'header.php';

// Get Hotspots for user from DB
$stmt = $con->prepare('SELECT id, owner_id, hs_name, hs_address FROM hotspots WHERE id = ?');
// In this case we can use the account ID to get the hotspot info.
$stmt->bind_param('i', $_SESSION['id']);
$stmt->execute();
$stmt->bind_result($hsid, $ownerid, $hsname, $hsaddress);
$stmt->fetch();
$stmt->close();
?>      <div class="content">

<p          <p class="block">Welcome back, <?=$_SESSION['name']?>!</p>

<table>
<tr>
<td>Hotspot Name</td>
</tr>
<tr>
<td><?=$hsname?></td>
</tr>
</table>
    </div>
    </body>
</html>

Where is the loop? It looks like you are selecting based on an ID, isn't this ID unique? — Dharman, Sep 28 '21 at 20:50
The ID is unique to the accounts table, or the sessiong ID, it's the same as the logged in user - but, it's not unique to the hotspots table as each user could have many records. I want to return all rows for that particular ID rather than just the 1st result. Hope that makes sense. — James Grunshaw, Sep 28 '21 at 20:59

George Sun · Answer 1 · 2021-09-28T20:47:20.477

The following will loop through all results one by one using fetch_assoc();.

$stmt = $con->prepare('SELECT id, owner_id, hs_name, hs_address FROM hotspots WHERE id = ?');
$stmt->bind_param('i', $_SESSION['id']);
$stmt->execute();
$results = $stmt->get_result();
while ($result = $results->fetch_assoc()) {
    // do stuff with your result, e.g. `echo '<td>' . $result['id'] . '</td>';
}
$stmt->close();

Here is it applied in your code to display hs_name for each result in a separate row.

        <?php
            include 'header.php';
        ?>
        <div class="content">
        <p class="block">Welcome back, <?= $_SESSION['name'] ?>!</p>
        <table>
            <tr>
                <td>Hotspot Name</td>
            </tr>
            <?php
                $stmt = $con->prepare('SELECT id, owner_id, hs_name, hs_address FROM hotspots WHERE id = ?');
                $stmt->bind_param('i', $_SESSION['id']);
                $stmt->execute();
                $results = $stmt->get_result();
                while ($result = $results->fetch_assoc()) {
                    echo '<tr><td>' . $result['hs_name'] . '</td</tr>'
                }
                $stmt->close();
            ?>
        </table>
        </div>
    </body>
</html>

score 0 · Answer 2 · answered Sep 28 '21 at 21:07

The simplest way to iterate the results from mysqli prepared statements is to fetch the result object using get_result() and use foreach loop.

$stmt = $con->prepare('SELECT id, owner_id, hs_name, hs_address FROM hotspots WHERE id = ?');
$stmt->bind_param('i', $_SESSION['id']);
$stmt->execute();
$result = $stmt->get_result();
foreach($result as $row) {
    echo $row['hs_name'];
}

If you want more flexibility, you can use while loop to manually iterate using one of the fetch_* functions.

Most of the time, the better option would be to just fetch the results into an array and then pass that to your code that displays HTML. Don't mix the database logic and display logic.

$stmt = $con->prepare('SELECT id, owner_id, hs_name, hs_address FROM hotspots WHERE id = ?');
$stmt->bind_param('i', $_SESSION['id']);
$stmt->execute();
$rows = $stmt->get_result()->fetch_all(MYSQLI_ASSOC);

<html>
<body>
    <div class="content">
        <p class="block">Welcome back, <?= $_SESSION['name'] ?>!</p>
        <table>
            <tr>
                <td>Hotspot Name</td>
            </tr>
            <?php foreach ($rows as $row) : ?>
                <tr>
                    <td><?= $row['hsname'] ?></td>
                </tr>
            <?php endforeach; ?>
        </table>
    </div>
</body>
</html>

If you are using the libmysqlclient (which you probably shouldn't), you can't use get_result(), so you have to bind variables separately and fetch them one by one.

$stmt = $con->prepare('SELECT id, owner_id, hs_name, hs_address FROM hotspots WHERE id = ?');
$stmt->bind_param('i', $_SESSION['id']);
$stmt->execute();
$stmt->bind_result($hsid, $ownerid, $hsname, $hsaddress);
while($stmt->fetch()) {
    echo $hsname;
}

How to loop through a mysqli prepared statement and output the results?

2 Answers2