CODEWITHSUNDEEP
phpmysql
Six God
Six God

Reputation: 47

PHP MySQL While Loop Is Only Returning The Last Row

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;

}

Upvotes: 1

Views: 691

Answers (1)

John Conde
John Conde

Reputation: 219794

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.

Upvotes: 3

Related Questions