PHP MySQL While Loop Is Only Returning The Last Row

Question

I have been tasked with presenting a list of all photographs in relation to a photographer, when that photographer is clicked. I need to echo the list of photographs but currently, only the last row is being echoed.

The rows returned should look like this (minus the bullet points):

1 Amager Standpark Sunset 2010-07-01 _img/1/1.jpg 1
1 Field Landscape 2010-07-09 _img/1/2.jpg 1

However my code is only returning this:

1 Field Landscape 2010-07-09 _img/1/2.jpg 1

Below is my code:

            // SQL query to get all photo data from specific photographer
$query = mysqli_query($con, "SELECT * FROM Photograph WHERE PhotographerId='$photographerID'");
$photos = mysqli_fetch_assoc($query);
$num = mysqli_num_rows($query);

if ($num == 0) {
    echo 'This are no photographs present for this photographer.';
} else if ($num > 0) {

    $list = '';

    while ($photos = mysqli_fetch_array($query)) {

        $list .= $photos['PhotographerId'] . ' ' . $photos['PhotographName'] . ' ' .
                $photos['Date'] . ' ' . $photos['PhotographURL'] . ' ' .
                $photos['Visible'] . ' </br>';
    }

    echo $list;

}

mysqli_fetch_assoc($query); fetches the first row, then mysqli_fetch_array($query) fetches the subsequent rows within the loop. But you never use the first result set for anything. Also, make up your mind which kind of array you want! — ADyson
– ADyson, Commented May 1, 2018 at 13:14

John Conde · Accepted Answer · 2018-05-01 13:14:25Z

You're calling mysqli_fetch_assoc() right after you run your query effectively using the first set of results. Then once you start your loop you start t=at the second row.

Removing it will solve your issue and make the first result set available to your loop.

You can also just use mysqli_fetch_assoc() instead of mysqli_fetch_array() inside of your loop. mysqli_fetch_array() returns both the associative and numerically indexed results of your query which you don't need.

// SQL query to get all photo data from specific photographer
$query = mysqli_query($con, "SELECT * FROM Photograph WHERE PhotographerId='$photographerID'");
$num = mysqli_num_rows($query);

if ($num == 0) {
    echo 'This are no photographs present for this photographer.';
} else if ($num > 0) {

    $list = '';

    while ($photos = mysqli_fetch_assoc($query)) {

        $list .= $photos['PhotographerId'] . ' ' . $photos['PhotographName'] . ' ' .
                $photos['Date'] . ' ' . $photos['PhotographURL'] . ' ' .
                $photos['Visible'] . ' </br>';
    }

    echo $list;

}

I don't know where you get $photographerID from but if it is user input this code is vulnerable to SQL Injection Attacks. You should consider using prepared parameterized statements to avoid this risk.

Collectives™ on Stack Overflow

PHP MySQL While Loop Is Only Returning The Last Row

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related