Row count using limit

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
User avatar
icarpenter
Forum Commoner
Posts: 84
Joined: Mon Mar 07, 2005 8:12 am
Location: Kent, England

Row count using limit

Post by icarpenter »

Hi I am running a query using the limit function

Code: Select all

SELECT * FROM table limit 40,5
But I would also like to return a row count of the select query without the limit function...

Does anyone know a way of doing this as I want to avoid running the query again without using limit to get a row count if I can help it...

Rgds Ian
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

The only way that I can see is a subquery,

Code: Select all

SELECT *, COUNT(SELECT * FROM `table`) AS `total` LIMIT 40,5
Of course this is only supported by mysql 4.1 and greater
User avatar
icarpenter
Forum Commoner
Posts: 84
Joined: Mon Mar 07, 2005 8:12 am
Location: Kent, England

Post by icarpenter »

Thanks Jcart...

I tried this on version 5.0.0 but I recieved an SQL syntax error, do you have any idea what I might be doing wrong?

Code: Select all

SELECT *, COUNT(SELECT * FROM `my_table`) AS `total` LIMIT 40,5
Thanks Ian
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post by phpScott »

what was the error?
User avatar
icarpenter
Forum Commoner
Posts: 84
Joined: Mon Mar 07, 2005 8:12 am
Location: Kent, England

Post by icarpenter »

phpscott this was the error!

Code: Select all

[localhost] ERROR 1064: 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 'SELECT * FROM `my_table`) AS `total` LIMIT 40,5' at line 1
Rgds Ian
Post Reply