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.
Your query is fetching all rows.
You should use:
JOIN clause to specify the relationship between the tables.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.
Try the following
SELECT comment.messageID, comment.message, comment.username, comment.messageailmentID, Items.ItemID
FROM comment, Items
WHERE Items.ItemID = comment.messageailmentID and Items.ItemID = ". $_GET['Items.ItemID']
Thank you guys for helping me figure out my own stupidity! The query I use that works is this:
SELECT `comment`.messageID, `comment`.message, `comment`.username
, `comment`.ItemID, Items.ItemID
FROM Items
INNER JOIN `comment`
ON Items.ItemID = `comment`.ItemID
WHERE Items.ItemID = varItemID
varItemID is a $_GET['ItemID'] also.