On Friday 07 September 2001 15:37, Torgil Zechel wrote:
> Is it possible to get the total number of records when doing a select with
> LIMIT?
No. As least, not in MySQL, unless I am missing something.
> For example, if I do:
>
> SELECT * FROM tbl LIMIT 0,10
>
> I would like to get the total number of records in tbl along with the 10
> first ones..
>
> I need this to display "showing 0 - 10 of 354", and my query is much more
> complex so I dont wan't to make two queries:
>
> SELECT COUNT(*) FROM tbl
> SELECT * FROM tbl LIMIT 0,10
COUNT is an aggregate function which works on the result set returned. You
can therefore select a number of rows to be displayed and count these, or
select all rows in the table and count these, but not both at once.
(You can't have both your cake and eat it ;-)
If UNION was supported, you could do something like
SELECT field1, field2, ... fieldx LIMIT 0,10
FROM tbl
UNION
SELECT count(*), '', ... '' <- provide x - 1 blank fields of the same
datatype as 'fieldn' in the first SELECT
which would provide you with the first 10 results as well as the total number
of rows. This is however a pretty messy way of doing things and I would stick
with two SELECT statements.
BTW a simple COUNT(*) without a WHERE clause is optimized in MySQL.
HTH
Ian Barwick
--
Ian Barwick - Developer - [EMAIL PROTECTED]
akademie.de asp GmbH - http://www.akademie.de
"To query tables in a MySQL database is more fun than eating spam"
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
