Change display of an element based upon criteria
mysql, php
Solution
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 JOIN`ed 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) . "..."`
Problem
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?