请帮我处理我的评论部分sql查询

Question

OK guys, I am in need of a little help here! Using PHP and mysql, I made a comment section that users can add comments to. First off, the page is based off of the ItemID and displays the correct item. When they add a comment, I take their username and the ItemID and put it in the comment table as comment.username and messageailmentID. So the page displays correctly, and the comment section works great, except that I cannot seem to get the SQL query right. I have tried many variations, with no luck. I want the comment that anyone posts under the pages ItemID to show only those comments under that ItemID (specifically messageailmentID).

MY query ranges, but here is the one I have at the moment:

SELECT `comment`.messageID, `comment`.message, `comment`.username, `comment`.messageailmentID, Items.ItemID
FROM `comment`, Items
WHERE varItemID = `comment`.messageailmentID

(Using dreamweaver, the varItemID is basically $_GET['Items.ItemID'])

EDIT: As an example, The user clicks an item which takes them to the itemdetails.php?ItemID=8. When they leave a comment, it will add Items.ItemID into comment.messageailmentID.

The important part of the table columns are this:

Items: ItemID

comment: CommentID, message, comment.username, messageailmentID(which is the pages ItemID)

The varItemID isn't a column, but does $_GET['ItemID'], which may not even be needed in this query because it is done on another query.

Answer 1

Your query is fetching all rows.

You should use:

• A JOIN clause to specify the relationship between the tables.
• A WHERE clause to specify which particular items you want to fetch.

Try this:

SELECT
    comment.messageID,
    comment.message,
    comment.username,
    comment.messageailmentID,
    Items.ItemID
FROM Items
LEFT JOIN comment
ON Items.ItemID = comment.messageailmentID
WHERE Items.ItemID = 42

Using a left join means that it will still return one row even if there are no comments. Use INNER JOIN if you don't want this.