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

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>

CodePudding user response：

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>

CodePudding user response：

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