PHP MySQLi loop through all rows in SQL query

Question

so I have a MySQL database table with the following contents:

+----+----------+---------------------+-------+------------------+------------------+----------+
| id | username | created_at          | name  | time-start       | time-end         | comments |
+----+----------+---------------------+-------+------------------+------------------+----------+
|  1 | test     | 2022-11-29 20:23:06 | test  | 2022-11-29T22:22 | 2022-11-29T13:23 | NULL     |
|  2 | test     | 2022-11-29 20:36:51 | test1 | 2022-11-29T22:23 | 2022-11-29T14:12 | NULL     |
+----+----------+---------------------+-------+------------------+------------------+----------+

I have some code that runs a query on the database:

<?php
            require_once "connect.php";
            session_start();

            $username = $_SESSION['username'];
            $sql = "SELECT `name`, `time-start`, `time-end` FROM `reminders` WHERE username = '$username';";

            while ($row = mysqli_fetch_assoc(mysqli_query($conn, $sql))){
            
                echo $row["name"] . "<br>";
            }
                
        ?>

I expect this to code to loop over the results and echo both of the names from the database. Like this:

test
test1

But, instead, i get this:

testtesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttesttest.....

If anyone knows how to fix, that would be great!

Thanks!

Every time your while loop executes, you are executing your query _again_. The `mysqli_query` does not belong nested into your loop condition like that, it needs to happen _once_ before the loop. — CBroe, Nov 30 '22 at 07:30
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, Nov 30 '22 at 10:25
Why are you using mysqli? Why aren't you using PDO? It would be much easier for you. Also, using `while` loop like this can be confusing, why aren't you using `foreach` here instead? — Dharman, Nov 30 '22 at 10:28

score 0 · Accepted Answer · edited Nov 30 '22 at 10:27

I changed the code to only execute mysqli_query once outside the loop and it works now:

<?php
    require_once "connect.php";
    session_start();

    $username = $_SESSION['username'];
    $sql = "SELECT `name`, `time-start`, `time-end` FROM `reminders` WHERE username = '$username';";

    $result = mysqli_query($conn, $sql);

    while($row = mysqli_fetch_assoc($result)) {
        // var_dump($row);
        echo $row['name'] . "<br>";
    }

?>

PHP MySQLi loop through all rows in SQL query

1 Answers1