LIMIT

Is there a way to get the number of rows that would have been returned had
there not been a LIMIT clause in a SELECT statement?

For example, if
Query #1) SELECT * FROM aTable
would normally return 100 rows. But
Query #2) SELECT * FROM aTable LIMIT 5
will return 5 rows. Is there a way to find out that 100 rows would have
been returned if there was no LIMIT clause, by using only Query #2 and maybe
a PHP function on the $result?

Regards,
Dwight


--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: LIMIT

If you're using MySQL then:

SELECT SQL_CALC_FOUND_ROWS * FROM aTable LIMIT 5
SELECT FOUND_ROWS()

It's in the mysql documentation under SELECT syntax I believe.

Chris

Dwight Altman wrote:
> Is there a way to get the number of rows that would have been returned had
> there not been a LIMIT clause in a SELECT statement?
>
> For example, if
> Query #1) SELECT * FROM aTable
> would normally return 100 rows. But
> Query #2) SELECT * FROM aTable LIMIT 5
> will return 5 rows. Is there a way to find out that 100 rows would have
> been returned if there was no LIMIT clause, by using only Query #2 and maybe
> a PHP function on the $result?
>
> Regards,
> Dwight
>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: LIMIT

So how do I get this information on the PHP side?

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
-> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

I count 2 queries above (unless there is some caching magic). Whatever. I
was hoping to add another column (like FOUND_ROWS) to an existing query and
be able to pull out the FOUND_ROWS when I loop over the result set.
Something like:
SELECT *, FOUND_ROWS FROM aTable LIMIT 5

But since that single query doesn't work, how do I apply the MySQL solution
and extract it on the PHP side?

http://dev.mysql.com/doc/refman/4.1/en/information-functions .html
http://www.mysqlfreaks.com/statements/101.php


Regards,
Dwight

> -----Original Message-----
> From: Chris [mailto:listschris@leftbrained.org]
> Sent: Friday, June 30, 2006 9:46 AM
> To: php-db@lists.php.net
> Subject: Re: [PHP-DB] LIMIT
>
> If you're using MySQL then:
>
> SELECT SQL_CALC_FOUND_ROWS * FROM aTable LIMIT 5
> SELECT FOUND_ROWS()
>
> It's in the mysql documentation under SELECT syntax I believe.
>
> Chris
>
> Dwight Altman wrote:
> > Is there a way to get the number of rows that would have been returned
> had
> > there not been a LIMIT clause in a SELECT statement?
> >
> > For example, if
> > Query #1) SELECT * FROM aTable
> > would normally return 100 rows. But
> > Query #2) SELECT * FROM aTable LIMIT 5
> > will return 5 rows. Is there a way to find out that 100 rows would have
> > been returned if there was no LIMIT clause, by using only Query #2 and
> maybe
> > a PHP function on the $result?
> >
> > Regards,
> > Dwight
> >
> >
> >
>
> --

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php