CODEWITHSUNDEEP
phpmysql
user1920678
user1920678

Reputation: 51

MYSQL query check if user like

I'm asking for query statement I have two tables first table 'posts' :

post_id     title    Userid
1           test     1

The other table is 'likes'

userid  post_id
1       1

I need single query to check if user like this photo or not in bulk select my solution is very poor :

SELECT * FROM `table` WHERE x = x LIMIT 100;

and give it foreach and make query for every row :

foreach($results as $v)
{
   $data[1] = $v;
   $data[1]['is_like'] = SELECT COUNT(*) FROM likes WHERE userid = 1;
}

1 is the already login user id

I need to single query to return post_id,.. etc and filed like is_like

Upvotes: 0

Views: 1500

Answers (3)

a1ex07
a1ex07

Reputation: 37382

Assuming likes can have only 1 row with the same (user_id,post_id) :

SELECT p.* , 
CASE WHEN 
l.id IS NULL THEN 0
ELSE 1
END as is_liked
FROM posts p
LEFT JOIN likes l ON l.user_id = p.user_id and l.post_id =p.post_id

If not (multiple rows in likes for a given (user_id,post_id) ) :

SELECT p.* , 
CASE WHEN 
l.user_id IS NULL THEN 0
ELSE 1
END as is_liked
FROM posts p
LEFT JOIN 
(
   SELECT DISTINCT user_id,post_id FROM likes 
) l ON l.user_id = p.user_id and l.post_id =p.post_id;

Or

SELECT p.* , 
CASE 
WHEN  EXISTS (SELECT NULL FROM likes l 
  WHERE l.user_id = p.user_id and l.post_id =p.post_id) THEN 1
ELSE 0
END as is_liked
FROM posts p

Update I hope I got a better understanding of the question now. My assumption : posts.user_id is id of user who created post; like table stores information about who likes the post. Thus, to check all posts and whether a particular like them you need ($login_user_id should be escaped properly)

SELECT p.* , 
CASE 
WHEN  EXISTS (SELECT NULL FROM likes l 
  WHERE l.user_id = $login_user_id and l.post_id =p.post_id) THEN 1
ELSE 0
END as is_liked
FROM posts p

Upvotes: 3

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

SELECT 
    p.post_id,
    p.title,
    IF(l.post_id IS NOT NULL,1,0) as like
FROM posts as p
LEFT JOIN likes as l ON l.post_id = p.post_id AND l.userid = p.userid
WHERE p.Userid = 1

If post_id in likes table is available it will return 1 else 0.

Upvotes: 2

Ken White
Ken White

Reputation: 125729

Something like this should work as a single query (untested, of course - you didn't provide much data to use for testing):

select 
  p.title, count(l.post_id) 
from 
  `posts` p
inner join 
  `likes` l
on 
  l.userid = p.userid and l.post_id = p.post_id
where 
  p.userid = loggedinuserID
group by 
  p.userid

This will give you a count of the total posts the specified user has liked.

Upvotes: 0

Related Questions