CODEWITHSUNDEEP
phpmysqlpdo
Michael
Michael

Reputation: 4390

What's wrong with this INNER JOIN SQL query?

I have three tables, tblMembers, tblBooks and tblReviews. The idea is a book review website.

Currently the Book Title is only displayed if there are reviews written for it, however I want the book title to always be displayed along with; either a list of reviews, or, the text, 'this title has not yet been reviewed.'

What is wrong with this SQL query?

$sql = "
SELECT *
FROM tblBooks
INNER JOIN tblReviews
ON tblBooks.bookID = tblReviews.bookID
INNER JOIN tblMembers
ON tblReviews.userID = tblMembers.userID
WHERE tblReviews.bookID = :bookID";


$query = $odb->prepare($sql);

    $query->execute(array(":bookID" => $bookID));

    $results = $query->fetchAll();
    echo "<h1>".$results[0]['longTitle']."</h1>";
    if($results !== FALSE && $query->rowCount()>0) {
        foreach($results as $result) {
            echo "Reviewed by: " . $result['firstName'] . " " . $result['lastName'];
            echo "<br />";
            echo "Rating: " . $result['rating']."/100";
            echo "<br />";
            echo "Date: " . $result['reviewDate'] . " " . $result['reviewTime'];
            echo "<br />";
            echo "Review:";
            echo "<br />";
            echo $result['reviewText'];
            echo "<hr />";
        }
    } else { // the book has not been reviewed yet
        echo "There are no reviews for this title yet";
    }

As mentioned above, the line echo "<h1>".$results[0]['longTitle']."</h1>"; is only executed if the book has reviews, I want to get the book details prior to checking if it has reviews or not. How can I do this?

On another note, how can I check which books haven't been reviewed yet and populate a drop down list with the results? - This has been answered :D

How can I either:

Populate two separate drop down lists, one for books that have reviews and one for books that haven't been reviewed yet (this one I can do as I have the SQL from the question above).

OR

Populate the single dropdown list but put books that have reviews at the top, separated by a string of characters, let's say "---------- THESE BOOKS HAVEN'T BEEN REVIEWED YET ----------" followed by the books that haven't yet been reviewed. Is this possible?

Upvotes: 0

Views: 121

Answers (1)

xQbert
xQbert

Reputation: 35323

SELECT *
FROM tblBooks
LEFT JOIN tblReviews
ON tblBooks.bookID = tblReviews.bookID
LEFT JOIN tblMembers
ON tblReviews.userID = tblMembers.userID
WHERE tblReviews.bookID = :bookID

Will return all books regardless if there is a review or a member affiliated with it.

IF there is a review return it, and if the review has a member it as well. otherwise NULL values will come back in fields related to tblMembers or tblReviews.

Upvotes: 1

Related Questions