PHP query not returning all results

Question

I'm trying to display a table of dates, albums and reviews made by users. The date and review show up with no problem, but at the moment my query is only displaying the first CD Title in the list.

Table 'cdreview' has columns 'CDID', 'userID', 'reviewDate', 'reviewText'. Table 'cd' has 'CDTitle', so I've used a natural JOIN to link the 2 tables by the CDID, but I can't display the correct CDTitle.

Any help would be extremely grateful.

<?php

    require_once 'database_conn.php';

        $userid = $_SESSION['userSession'];

$sql = "SELECT * FROM cdreview JOIN cd WHERE '$userid'=cdreview.userID ORDER BY reviewDate ASC";

$result = mysqli_query($conn, $sql) or die(mysqli_error($conn));


$row = mysqli_fetch_assoc($result);

    $date = $row['reviewDate'];
    $album = $row['CDTitle'];
    $review = $row['reviewText'];
    $cdid = $row['CDID'];

?>

    <tr align='center'>
        <td><?php echo $date;?></td>
            <td><?php echo $album;?></td>
                <td><?php echo $review;?></td>
                    <td><a href="edit_review.php?id=<?php echo $cdid;?>">Edit</td>
                        <td><a href="album.php?id=<?php echo $cdid;?>">Delete</td>
    </tr>


</table>

You are only fetching one row from the result set: $row = mysqli_fetch_assoc($result); — jeroen
– jeroen, Commented Dec 14, 2016 at 12:08

Yoleth · Accepted Answer · 2016-12-14 12:09:08Z

1

You have to iterate through results :

while ($row = mysqli_fetch_assoc($result) ){

    $date = $row['reviewDate'];
    $album = $row['CDTitle'];
    $review = $row['reviewText'];
    $cdid = $row['CDID'];

    // print stuff
}

answered Dec 14, 2016 at 12:09

Yoleth

1,2727 silver badges16 bronze badges

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

Comments

secelite · Accepted Answer · 2016-12-14 12:12:55Z

1

mysqli_fetch_assoc is returning a result set.

You have to loop through this result set and handle each result separately.

while ($row = mysqli_fetch_assoc($result)) {
    $date = $row['reviewDate'];
    $album = $row['CDTitle'];
    $review = $row['reviewText'];
    $cdid = $row['CDID'];
    ?>
    <tr align='center'>
        <td><?php echo $date; ?></td>
        <td><?php echo $album; ?></td>
        <td><?php echo $review; ?></td>
        <td><a href="edit_review.php?id=<?php echo $cdid; ?>">Edit</td>
        <td><a href="album.php?id=<?php echo $cdid; ?>">Delete</td>
    </tr>
    <?php
}

answered Dec 14, 2016 at 12:12

secelite

1,3381 gold badge11 silver badges19 bronze badges

1 Comment

PeeHaa Over a year ago

Actually mysqli_fetch_assoc doesn't return a result set. But rather "the fetched row in the result set".

Collectives™ on Stack Overflow

PHP query not returning all results

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related