Can I do this without two queries?

Question

I'm trying to get ten rows of data from my database that meet a certain criteria. I'm trying to get the first ten users who are registered in the database to take a course, but I also want to get the total number of users taking the course from the database. I know to use limit to return only ten users, but is there any way I can avoid doing two queries in order to also get the total number of users taking the course. My current query is below.

SELECT * FROM users, user_courses 
WHERE users.id = user_courses.user_id AND user_courses.CRN = '$CRN'

Without retrieving the full dataset each time and limiting it within the script performing the query you can't do this in a single query. Just do it as 2 separate queries. Also be careful including variables as you have done, ensure they are properly escaped etc. — Simon at The Access Group, Jul 22 '13 at 08:10
In other databases you could do this using Window queries, but with MySQL I think it's not possible. — Wolph, Jul 22 '13 at 08:13
Check this question, it will give you an answer: http://stackoverflow.com/questions/2439829/how-to-count-all-rows-when-using-select-with-limit-in-mysql-query — Pieter, Jul 22 '13 at 08:14
Logically both queries should be run seperately as well, using Union or any method don't related them in single instance. However if you still want you can run "Count" query as subquery to column. but that will replicate the data in every 10 result and hence is worthless. — Sumit Gupta, Jul 22 '13 at 08:19
I think I'm going to just use two queries. Also, @Simonatmso.net I'm interested as what you are talking about concerning variables being properly escaped. Do you care to elaborate? — user2605381, Jul 22 '13 at 08:38
@user2605381 check my updated answer... it's crude but should work! — jaczes, Jul 22 '13 at 08:40
thanks @jaczes . as far as validating variables. I think I get what you are saying. I will definately get to that. I'm using codeigniter, so all I have to do is go into my controllers and add in filters. I'm not that concerned about injection though because I'm using facebook for validation and login. Thanks everyone for your help! — user2605381, Jul 22 '13 at 08:44
@user2605381 you use `'$CRN'` which is the kind of logic I see too often in PHP questions, where `$CRN` is some variable which *may* be from user input, but has not been sanitised etc. This in turn leads to a potential that this becomes a route for SQL injection. For more information, check out http://bobby-tables.com/ — Simon at The Access Group, Jul 22 '13 at 11:00

Grim · Answer 1 · 2013-07-22T09:16:39.450

1

You can use the UNION statement.

SELECT users.id FROM users, user_courses 
WHERE users.id = user_courses.user_id AND user_courses.CRN = '$CRN' LIMIT 10
UNION SELECT count(*) FROM USERS ...

edited Jul 22 '13 at 09:16

answered Jul 22 '13 at 08:13

Grim

1,938
10
56
123

Take care the column's count and Type match. I Hope in this case the `users.id` is a integer. – Grim Jul 22 '13 at 08:14
this will not return the expected value – Philipp Sander Jul 22 '13 at 08:14
Better now? Wasn't sure what you mean. – Grim Jul 22 '13 at 09:18
This is still fundamentally doing 2 queries though, albeit with 1 request to the server – Simon at The Access Group Jul 22 '13 at 10:56
This is two seperate queries combined to one query using `UNION`. A subquery may be a seperate query too, Subquerys may not match all SQL-Servers. I guess i know `UNION` is SQL-Standard. – Grim Jul 22 '13 at 11:21
btw. The Query-Statistics calculate them to 1 Query. – Grim Jul 22 '13 at 11:31

vikrant singh · Answer 2 · 2013-07-22T12:09:26.893

0

select @max,result.* from (
SELECT @sno:=@sno+1 ,users.*,user_courses.*   FROM users, user_courses ,(select @sno:=0) as initSno 
WHERE users.id = user_courses.user_id AND user_courses.CRN = '$CRN'
)  result,(select @max:=max(@sno)) as maxNo

edited Jul 22 '13 at 12:09

answered Jul 22 '13 at 08:33

vikrant singh

2,091
1
12
16

IF you're doing this you may as well count up the recordcount, as you're not limiting the record set – Simon at The Access Group Jul 22 '13 at 10:58

score 0 · Answer 3 · answered Jul 22 '13 at 12:20

While you can do this with one query, I think that in this case, using two queries is better. It simplifies your code, and the queries will probably be faster too.

If the two separate queries are too slow, you probably need indexes or primary keys for users.id and user_courses.user_id.

SELECT COUNT(*)
FROM users, user_courses 
WHERE users.id = user_courses.user_id 
AND user_courses.CRN = '$CRN'

and

SELECT *
FROM users, user_courses 
WHERE users.id = user_courses.user_id 
AND user_courses.CRN = '$CRN'
LIMIT 10

Can I do this without two queries?

3 Answers3