I have the following query:
'SELECT * FROM posts LEFT JOIN taxi ON taxi.taxiID = posts.postID
WHERE (taxi.value = 1 AND taxi.userID ='.$userID.')
AND ??????????????
ORDER BY taxi.ID DESC
LIMIT 10'
The way the site works is the user can tag posts as being "liked". When a post is liked, the taxi table is given a new row with taxiID being the same as postID, userID to store the user that liked the post, and value which is set to 1. Disliking a post sets value to 0.
I want to display all posts where value is 1 and userID is $userID - check. However, I also want the query to display all the posts where the user hasn't liked a post yet. Thing is, if the user hasn't liked a post yet, userID is NULL with a corresponding value of NULL. If I query for that, I'll be skipping those posts that other users have liked but the user hasn't.
Here's the taxi table:
ID taxiID userID value
1 1 1 1
2 1 6 1
3 1 4 0
4 2 1 0
5 2 6 1
6 2 4 0
7 3 6 1
8 3 4 0
Assuming $userID is 1, my query ought to display taxiID 1 and 3 (because user 1 liked ID 1 AND hasn't liked or disliked taxiID 3.
The code I've posted will only result in displaying taxiID 1.
The question is, what is my missing line in my query supposed to be given the above?
It seems you want to find all taxiID that the use has not disliked:
SELECT taxiID
FROM taxi
GROUP BY taxiID
HAVING taxiID NOT IN
( SELECT taxiID
FROM taxi
WHERE userID = '.$userID.'
AND value = 0
)
ORDER BY taxiID DESC
LIMIT 10
Test in SQL-Fiddle
You probably have a post table, so it would be better to use:
SELECT * --- whatever columns from `post` table
FROM post
WHERE postID NOT IN
( SELECT taxiID
FROM taxi
WHERE userID = '.$userID.'
AND value = 0
)
ORDER BY postID
LIMIT 10
If that taxi.taxiID means postID, then you should rename it to taxi.postID. It's very confusing as it is.
Assuming taxi.taxiID is the same as post.ID and you're looking to select all posts not tagged by a user yet, try:
SELECT * FROM post WHERE ID NOT IN
(SELECT taxiID FROM taxi WHERE userID = 1)
this is for user with an ID=1 of course.
I had this as a comment, but I think it's my final answer:
SELECT * FROM posts
LEFT JOIN taxi ON taxi.taxiID = posts.postID
WHERE (taxi.value != 0 AND taxi.userID ='.$userID.')
OR taxi.value is null
ORDER BY taxi.ID DESC
LIMIT 10
Ok, this is what should happen with the above query: