How to find some values in array returned by 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..?

I think you can LEFT JOIN two queries on the common field (here it may be answer_id) and add where condition for that answer_id. — heyanshukla
– heyanshukla, Commented Jul 11, 2012 at 11:47
This might be easier to answer if we could see the schema for those two tables — andrewsi
– andrewsi, Commented Jul 11, 2012 at 12:11
If $_SESSION["id"] and/or $question_id are numerical values, you MUST put them as question_id=$question_id in your query with no single quotes. — hjpotter92
– hjpotter92, Commented Jul 11, 2012 at 14:06

Michael Berkowski · Accepted Answer · 2012-07-11 14:54:45Z

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}

edited Jul 11, 2012 at 14:54

answered Jul 11, 2012 at 14:04

Michael Berkowski

271k47 gold badges450 silver badges395 bronze badges

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

2 Comments

StaticVariable Over a year ago

i have given my database schema would you plz tell me exact values....I am not so familiar with LEFT JOIN ??

Michael Berkowski Over a year ago

@Lokesh I don't know what more you would need. I have given you the complete query, the only one needed. Test it out and verify it gives results

Collectives™ on Stack Overflow

How to find some values in array returned by mysql_query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related