Jordyn Jordyn - 5 months ago 43
SQL Question

PHP voting system check votes

I’m working on a PHP voting system and I want to check logged in user voted on posts and display voted if the user have voted. I would like to do it in one single query without using multiple quires.
Posts table

+---------+--------+--------+
| post_id | title | c_id |
+---------+--------+--------+
| 1 | post 1 | 2 |
| 2 | post 2 | 3 |
| 3 | post 3 | 2 |
| 4 | post 4 | 1 |
+---------+--------+--------+


MySQL Loop

SELECT *
FROM posts
LEFT
JOIN categories
ON categories.cat_id = posts.c_id
WHERE posts.c_id = categories.cat_id
AND posts. active = 1
ORDER
BY posts.post_id DESC
LIMIT 0, 10


Votes table

+---------+--------+---------+
| vote_id | p_id | u_id |
+---------+--------+---------+
| 1 | 1 | 1 |
| 2 | 2 | 1 |
| 3 | 2 | 2 |
| 4 | 4 | 1 |
+---------+--------+---------+


Logged in user =
$uid

So if I run query inside the above MySQL loop it work fine

SELECT * FROM votes WHERE u_id = $uid AND p_id= $post_id


Is there a way to combine these two queries?

Answer
SELECT * 
  FROM posts P
  LEFT 
  JOIN categories C
    ON C.cat_id = P.c_id 
  LEFT JOIN (SELECT p_id FROM votes WHERE u_id = $uid)  V 
    ON V.p_id=P.post_id
   WHERE P. active = 1 AND 
 ORDER 
    BY P.post_id DESC 
 LIMIT 0, 10 ;