CODEWITHSUNDEEP
phpmysql
jk1984
jk1984

Reputation: 39

PHP query not returning all results

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>

Upvotes: 2

Views: 88

Answers (2)

secelite
secelite

Reputation: 1373

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
}

Upvotes: 1

Yoleth
Yoleth

Reputation: 1272

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
}

Upvotes: 1

Related Questions