Search the Community

Hi there everyone! I've got a problem combining two queries. The situation is this. I've got a list of links and I want to allow the user to get a result list of all, only unread, all in a particular category and then only unread in a particular category. My query for all: /* No filtering and no unread */ $q_urls = "SELECT * FROM shortenified_urls WHERE is_social = '1' AND active = '1' ORDER BY date_added DESC"; My query for in a particular category: /* All links in a particular category */ $q_urls = " SELECT * FROM shortenified_urls WHERE shortenified_urls.primary_cat = $vu_cat_view OR EXISTS ( SELECT link_id FROM linkcats WHERE link_id = shortenified_urls.id AND cat_id = $vu_cat_view ) AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1' ORDER BY date_added DESC" ; My query for Unread in all cats: /* Unread only for all categories */ $q_urls = "SELECT * FROM shortenified_urls WHERE shortenified_urls.date_added > $vu_mark_as_read AND NOT EXISTS ( SELECT lid FROM linkviews WHERE lid = shortenified_urls.id AND uid = $viewing_user_id ) AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1' ORDER BY date_added DESC" ; These seem to be working well. It's the next one that isn't working for me. I tried to combine the "All in a particular category" and "Unread in all categories" using the two above as starting points. The result, however, seems to be that I'm getting all in a particular category. It seems to be ignoring the unread status. /* Unread links in a particular category */ $q_urls = " SELECT * FROM shortenified_urls WHERE shortenified_urls.primary_cat = $vu_cat_view AND shortenified_urls.date_added > $vu_mark_as_read OR EXISTS ( SELECT link_id FROM linkcats WHERE link_id = shortenified_urls.id AND cat_id = $vu_cat_view ) AND NOT EXISTS ( SELECT lid FROM linkviews WHERE lid = shortenified_urls.id AND uid = $viewing_user_id ) AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1' ORDER BY date_added DESC" ; I'm not smart enough to know why it's not working, but it seems to me like I need to somehow bracket the first two parts containing the category retrieval to make this work right. The only problem is I don't know how to do that. Any help on how to get this to work would be greatly appreciated!

I'm stumped on how to get this join to work: Statement: SELECT * FROM votes WHERE NOT EXISTS (`votes`.`poll_id` = `user_votes`.`vote_poll_id` AND `user_votes`.`user_id`='1') limit 1 order by rand() Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`votes`.`poll_id` = `user_votes`.`vote_poll_id`) limit 1 order by rand()' at line 3 Possibly, but doubt it: the table is completely blank. Goal: Find all the entries that the user hasn't voted on as of yet, then submit them randomly to the user.