Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
jaylin
Forum Commoner
Posts: 68 Joined: Fri Nov 18, 2005 4:44 am
Post
by jaylin » Mon Mar 13, 2006 1:16 am
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
, 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,
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Mon Mar 13, 2006 1:24 am
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 » Mon Mar 13, 2006 1:27 am
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.
feyd
Neighborhood Spidermoddy
Posts: 31559 Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA
Post
by feyd » Mon Mar 13, 2006 1:35 am
No way around it really.
ody
Forum Contributor
Posts: 147 Joined: Sat Mar 27, 2004 4:42 am
Location: ManchesterUK
Post
by ody » Mon Mar 13, 2006 9:42 am
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.
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098 Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia
Post
by Chris Corbyn » Mon Mar 13, 2006 9:46 am
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