Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
icarpenter
Forum Commoner
Posts: 84 Joined: Mon Mar 07, 2005 8:12 am
Location: Kent, England
Post
by icarpenter » Tue Jul 12, 2005 11:30 am
Hi I am running a query using the limit function
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
John Cartwright
Site Admin
Posts: 11470 Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:
Post
by John Cartwright » Tue Jul 12, 2005 11:50 am
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
icarpenter
Forum Commoner
Posts: 84 Joined: Mon Mar 07, 2005 8:12 am
Location: Kent, England
Post
by icarpenter » Wed Jul 13, 2005 2:59 am
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
phpScott
DevNet Resident
Posts: 1206 Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.
Post
by phpScott » Wed Jul 13, 2005 4:45 am
what was the error?
icarpenter
Forum Commoner
Posts: 84 Joined: Mon Mar 07, 2005 8:12 am
Location: Kent, England
Post
by icarpenter » Wed Jul 13, 2005 6:49 am
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