Change display of an element based upon criteria

Question

I'd like users to be able to distinguish if they've voted for something (by bolding it) or haven't voted for it yet (not bolded).

For example:

       Voted For Post     Unvoted for
Votes:  77 ↑ ↓             12 ↑ ↓

Here is how my database is set up:

intro

Contains messages

    message_id    intro            user_id      up_vote
     10            Voted For Post   5            77
     11            Unvoted for      5            12

voting

Contains votes

    Voting_id     message_id_fk      user_id
     18             10               5   
     19             10               3     

users

Contains user names

    first_name    user_id
     BOB           5

I don't know how to properly query the database then make the proper if statement that will distinguish between voted for and unvoted for posts.

Here's what I have so far:

$sql = mysql_query("SELECT * FROM intro
         INNER JOIN users ON intro.user_id = users.user_id
         ORDER BY `up` DESC ");

echo $row['first_name'] . " " .  $row['intro'];

if( ??? ) {
  echo "<strong>" . $row['up_vote'] . "</strong>";
} else {
  echo $row['up_vote'];
}

Any ideas?

Answer 1

Left join the Voting table and check if any corresponding items were found:

SELECT intro.message_id, intro.intro, intro.user_id, intro.up_vote,
    IF(Voting.user_id IS NULL, 0, 1) AS has_voted
FROM intro
INNER JOIN users ON intro.user_id = users.user_id
LEFT JOIN Voting ON Voting.message_id_fk=intro.message_id
    AND Voting.user_id = 5
ORDER BY `up` DESC 

Then in PHP:

if($row['has_voted'){
    echo "<strong>".$row['up_vote']."</strong>";
}else {
    echo $row['up_vote'];
}

Some explanations:

• Columns from a LEFT JOINed table are NULL if no matching rows were found
• IF() is a function that returns the second parameter if the first parameter evaluates to true, otherwise returns the third parameter. As a function it can be easily used in the SELECT clause
• I replaced SELECT * by explicit selection of the needed columns, which is considered a best practise and in this case necessary because of ambiguous column names
• Of course you will have to replace the literal 5 with your current user id. Use prepared statements or concatenate the query like this: "...Voting.user_id = " . intval($current_user_id) . "..."

Answer 2

In case you would like to have only the posts where the current logged in user (say the user having user_id = 3) had upvoted them, you could use this query:

SELECT u.first_name,
       m.message_id,
       m.message,
       m.up_vote,
       v.Voting_id
FROM Users u,
     Messages m
LEFT JOIN Voting v ON v.message_id_fk = m.message_id AND v.user_id = 3
WHERE u.user_id = m.user_id;

In your code, use the proper procedure to replace the 3 above with the user_id from the currently logged in user.

SQL Fiddle

If you execute this query, you'll notice that I've added a column to the result: voting_id.

If the user upvoted this post, it will show the id of the vote. If the user did not upvote, it will show NULL value.

You can then check this field in your code for not null and you're done!