如何在mysql_query返回的数组中找到一些值

Question

I have voting system in my site and i want to check that user has upvoted,downvoted or not. To reduce the no. of queries i selected complete table containing id equals to user id from voting table table schema for vote table is table schema for answer table is

$query=SELECT answer_id,user_id,vote FROM vote WHERE user_id='{$_SESSION["id"]}'
$result1=mysql_query($query);
$array=mysql_fetch_array($result1);

now when i fetch the answers of question i want to check that if this user has voted or not if answer_id has found in this array than than i want to find the value of vote ?

code is

$query1="SELECT * FROM answers WHERE question_id='{$question_id}'";//$question_id is id of question
$result=mysql_query($query1);
if($result){
    While ($row=mysql_fetch_arrar($result)){
        if(in_array($row["id"],$array){
            echo $array["vote"];
        }
    }
}

I am confused how to fetch two array together...?This query only works for first value because i am not using while loop in $array,Can anybody help me..?

Answer 1

This can be done in one query to determine if a user has voted for an answer. There is no need for a preliminary query to get user votes, or an array comparison inside the fetch loop.

Use a LEFT JOIN against a subquery on votes and supply the userid in its WHERE clause. If the value of uservotes.vote is NULL, the user has not voted for this answer. Otherwise, uservotes.vote will contain the user's vote.

SELECT
  answers.*,
  uservotes.vote
FROM 
  answers 
  LEFT JOIN (
    SELECT answer_id, vote FROM votes WHERE user_id = '{$_SESSION["id"]}'
  ) uservotes ON answers.id = uservotes.answer_id
WHERE answers.question_id = {$question_id}