SQL JOIN with PDO prepared statement

[Dano1066]

Ive never used joins before some in general I'm not good but i also want to do is using a prepared statement which is why i am finding this difficult.

I have a users table and a comments table. When the comments are being displayed they also need to go to the users table to find out the name of the user that posted the comment. I can make this work with 2 queries but i want to reduce it to a single query.

 

 

this is the non prepared version but this is the code i want to make into a join

 

SELECT * from Comments where ParentCommentID=".$CommentID." //<--this may return more than 1

...........

$UserID = $row ->UserID;

 

SELECT UserName from Users where ID=".$UserID." ";

 

This is my attempt at a prepared statement to do this in 1 go but i'm lost now and dont really know what to do next.

 

 

$stmt = $db->prepare("SELECT * FROM Comments INNER JOIN Users ON Comments.UserID = Users.ID WHERE Comments.ParentCommentID = ':id'");

$stmt->execute(array(':id' => $CommentID));

$row = $stmt->fetch();

Edited February 23, 2013 by Dano1066

[kicken]

Prepared statements don't affect how joins work, they only affect how you use values in the query.

 

When you use a placeholder, you should not put quotes around it.  Like this:

$db->prepare("
SELECT 
   * 
FROM Comments 
INNER JOIN Users ON Comments.UserID = Users.ID 
WHERE 
   Comments.ParentCommentID = :id
");

 

The placeholder token will be recognized and the proper value used in it's place automatically.  When you put quotes around it, rather than treating it as a placeholder it gets treated as the literal string ':id'.