php mysql查询组合

Question

I first search all questions info. from "question" table including title, content, user etc. the Code:

$sql  = "select * FROM question where id>0 ORDER BY id ASC";  
    $result1 = mysql_query($sql);
    $res=Array();

And then I want to search the user's point from "user" table. So I must search point for each user in each row from the result1 The Code:

while($rows=mysql_fetch_assoc($result1))
    {
        $res[]=$rows;
        $user = $rows['user'];
        $sql2 = "select point from user where name='$user'";
        $result2 = mysql_query($sql2);  
    }

My problem is how to combine all the users' point(result2) with the questions info.(result1) together so that I can return a json for each row.

Answer 1

Use left join, as my understanding this work for you

$sql  = "SELECT q.*, u.point AS point FROM question AS q LEFT JOIN user AS u ON q.user = u.name WHERE q.id > 0 ORDER BY q.id ASC";  
$result = mysql_query($sql);