I'm having a bit of trouble getting the correct number of results for pagination in one query. If it's not possible to do in a single query, then I know how to do it with 2, but that seems to be a waste. Here's the query I have right now that gives me the results I'm looking for:
"SELECT b.id, b.bar_name, b.city, b.state, b.zip,
count(d.userid) as 'user_count'
FROM barlist b, destinations d
WHERE b.id = d.barid AND d.date = '$date'
GROUP BY b.id ORDER BY user_count DESC";
I can give the full structures of the table, but the only real important things to know is that barlist is basically a profile and destinations has an id, a barlist.id, a userid, and a date. Each time a user goes to a bar, an entry is created with an id, the id of the bar the user is going to, the user's id, and the date. This query gives me the bar information along with the number of users who are going to the bar on a given date. It gives me every entry.
Since I'm trying to split this up into pages, this doesn't give me everything I want. As I see it, I can do one of two things (edit: realized one of my options didn't work so I took it out):
1. Use the full query to get the total number of results, then use a second query with a LIMIT clause to get the results I want.
2. Use the full query to get the total number of results, then cycle through them until I get the ones I want to display.
Is there a better option?
I'm trying to do the optimal solution, but I really don't know what's best here. Could anyone provide any advice? Is there a way to get the total count while using a LIMIT? Would arrayslice slow down my pages because I'm querying for so many results? Would two queries be faster?
I hope all that is clear, but if it's not, please ask for more info. Any suggestions are appreciated.
