get the total count of record

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jaylin
Forum Commoner
Posts: 68
Joined: Fri Nov 18, 2005 4:44 am

get the total count of record

Post by jaylin »

hi all,

i want to get the total number of record for the specified query that contains subquery and limit. although i set the limit as like that

Code: Select all

limit 0, 10
, if there is abt 100 records, i want to show total count as a new field data, and the rest data. is there any way?

regards,
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

remove the limit and change the selection to a count()
jaylin
Forum Commoner
Posts: 68
Joined: Fri Nov 18, 2005 4:44 am

Post by jaylin »

but, there are huge amount of record. so, i have to use limit. but, also, i have to know the total records count for the specific criteria. how can i do that? also, i dun want to execute 2 queries.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

No way around it really.
ody
Forum Contributor
Posts: 147
Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK

Post by ody »

I think I understand you (but proberly not), how about something like this?

Code: Select all

select *, (select count(*) from table0) as count from table0 limit 0, 10;
Offcourse change table to whatever your table name it.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Not likely that you're running an older version but AFAIK sub-queries will only work in MySQL 4.1+ if that gives you any bother ;)
Post Reply