mysql php join statement

Question

I am not sure if that join statement is writen in the right way:

<?php

function generateComment($commentID) {

   $avatar_Q=mysql_query("
            SELECT * FROM comments com
            INNER JOIN users us ON com.user_id=us.user_id
            WHERE comment_id=$commentID // will that $commentID be red in that query string or will it treat it as a string "commentID"
   ");
   if($row=mysql_fetch_array($avatar_Q))
   {
       $userName=$row["us.user_name"]; // do I refer to the fields like that
       $avatarPath=$row["us.avatar"];
       $avatarRep=$row["us.reputation"];
       $message=$row["com.comment"];
       $date=$row["com.date"];
   }
   mysql_close();

   if(!isset($avatarPath))
   {
       $avatarPath="blank picture";
   }

?>

Is this the most efficient way to write a join statement

Marcx · Accepted Answer · 2011-11-05 08:49:11Z

1

Your query is written correctly, but you can improve it by specifing the table on comment_id, and for a better returning I suggest you to specify with column you want back, also I will use the variable outside the "string", because it's a numeric value (I suppose you omit the single quote like...

$sql = "Select com.command_it, etc 
 FROM ..... WHERE com.comment_id = ".$commentID."";

If otherwise the com.comment_id is text or varchar you must use the single quote like:

$sql = "Select com.command_it, etc 
 FROM ..... WHERE com.comment_id = '".$commentID."'";

Moreover this way you get only the rows where there is a user and a comment, if one user have no comment you don't retrieve that user...

If you want the user also if he has no comments you must use a LEFT JOIN

$sql = "SELECT com.comment_id, etc FROM users us
            LEFT JOIN comments com ON com.user_id=us.user_id
            WHERE com.comment_id=".$commentID."";

answered Nov 5, 2011 at 8:49

Marcx

6,8365 gold badges50 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CD001 Over a year ago

I'd generally put the AS in there just for the sake of legibility and curly-brace the inserted string com.comment_id={$commentID} but yup.

Nick Rolando · Accepted Answer · 2011-11-05 08:43:31Z

0

Not sure what you're doing with this line

WHERE comment_id=$commentID // will that $commentID be red in that query string or will it treat it as a string "commentID"

");

But you need to specify which table comment_id is coming from, say comments, then you might do something like

$avatar_Q=mysql_query("SELECT * FROM comments com 
    INNER JOIN users us ON com.user_id=us.user_id 
    WHERE com.comment_id=$commentID");

answered Nov 5, 2011 at 8:43

Nick Rolando

26.2k13 gold badges84 silver badges120 bronze badges

Comments

David · Accepted Answer · 2011-11-05 08:45:22Z

0

Not sur if it's the best way, but you could try to mysqlslap a mysql DB with it. Compare it with left join and other types of join and see which one is the best in your case. MySQL is all about slapping.

answered Nov 5, 2011 at 8:45

David

3251 gold badge3 silver badges11 bronze badges

Collectives™ on Stack Overflow

mysql php join statement

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related