PHP & SQL: fetch_assoc / while loop goes infinite when displaying data

Question

I am trying to connect to my database (it is working) and I want to select all data within the table "users". When I am displaying data, while loop with the fetch_assoc() never finishes. I don't understand why this is happening.

Any ideas?

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>My first project!</title>
    <link rel="stylesheet" href="css/style.css">
</head>
<body>

<?php
    $servername = "localhost";
    $user = "root";
    $password = "";
    $db = "mydb";

    $connection = new mysqli($servername, $user, $password, $db);

    if($connection->connect_error) {
        die("Es ist ein Fehler aufgetreten: $connection->connect_error");
    }

    $sql = "SELECT * FROM users";
    $result = $connection->query($sql);
    $resultArray = $result->fetch_assoc();

    if($result->num_rows > 0) {
        while($i = $resultArray) {
            echo "ID: " . $i["id"] . "<br>Name: " . $i["username"];
        }
    } 
?>
</body>
</html>

If I try this version, it is working - so I don't understand why "my version" isn't. I just tried to adapt it a bit.

<!DOCTYPE html>
<html>
<head>
    <meta charset="utf-8">
    <title>My first project!</title>
    <link rel="stylesheet" href="css/style.css">
</head>
<body>

<?php
    $servername = "localhost";
    $user = "root";
    $password = "";
    $db = "mydb";

    $connection = new mysqli($servername, $user, $password, $db);

    if($connection->connect_error) {
        die("Es ist ein Fehler aufgetreten: $connection->connect_error");
    }

    $sql = "SELECT * FROM users";
    $result = $connection->query($sql);

    if($result->num_rows > 0) {
        while($i = $result->fetch_assoc()) {
            echo "ID: " . $i["id"] . "<br>Name: " . $i["username"];
        }
    }
?>
</body>
</html>

while($i = $result->fetch_assoc()) calls fetch_assoc on every loop iteration. Your version only calls it once. — deceze
– deceze ♦, Commented Dec 27, 2018 at 11:40
For my understanding: The first version is not working because fetch_assoc() is not in a while-loop, so that is the reason why I only get one row and it is never changing, while in the second version the fetch_assoc() is included in the loop, thats why it is giving me every row until there is no row? Thanks in advance, much appreciated! — user10682705
– user10682705, Commented Dec 27, 2018 at 11:48

Deji · Accepted Answer · 2018-12-27 11:43:33Z

1

Right here is where you're causing an infinite loop:

while($i = $resultArray) {

This will cause the code to simply read $resultArray into $i (the first row of data returned by fetch_assoc) over and over again. $resultArray never changes and neither does $i, so the expression will never evaluate to false.

You need to be aware that when you do:

$resultArray = $result->fetch_assoc();

You are just getting a single row of data from the database - you will not be able to get multiple rows from the database with $resultArray.

Hence the second version is correct:

while($i = $result->fetch_assoc()) {

answered Dec 27, 2018 at 11:43

Deji

7751 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mureinik · Accepted Answer · 2018-12-27 11:41:50Z

1

In your version, you just fetched one row from the database. In the second snippet, each iteration of the loop fetches another row from the database, until the cursor is exhausted and NULL is returned.

answered Dec 27, 2018 at 11:41

Mureinik

316k54 gold badges402 silver badges406 bronze badges

Collectives™ on Stack Overflow

PHP & SQL: fetch_assoc / while loop goes infinite when displaying data

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related