MySQL Limit related question

PHP
1

Hai folks,

example :


$query="SELECT * FROM properties LIMIT 10,6;
if ($result=mysql_query($query) or die (mysql_error())); 
$tot=mysql_num_rows($result);
echo "number of records found : " . $tot;

Assume data base will have 50 matching records for the above query.
Now $tot will show 6 records found since we limit the results.
my question is, do we need to run the same query once without the LIMIT command to get the actual total 50? any other solutions ?

2

I think something like this should work…

SELECT COUNT(id) AS total, other_fields FROM properties LIMIT 10,6

$tot = $result['total'];
3

You can use the query hint SQL_CALC_ROWS and then FOUND_ROWS() to obtain the total number of rows that would have been found without the LIMIT.

SELECT SQL_CALC_ROWS etc etc

See MySQL :: MySQL 5.0 Reference Manual :: 11.13 Information Functions

:slight_smile:

4

Thanks folks for the reply, let me chk your suggessions…

5

alright :slight_smile:

6

what is this (id) here? i know count() will count the no.of elemnts in an array.
but here id :rolleyes:

7

I was using the id (primary key) field of the table as an example. You may have it named something else. :slight_smile:

8

please check post #3

9

hai tgavin,

$query="SELECT count(*) AS total FROM properties WHERE status='1' AND type='$type' ORDER BY dt DESC LIMIT $from,6";

thats the query now i am using with count(),

but,

$totrecs = $result['total'];
echo $totrecs;

returnes nothing though many records returned. any syntax issue?

10

This works charm for me !!! :blush:
then ill settle with this method folks :smiley:


$query="SELECT SQL_CALC_FOUND_ROWS * FROM properties WHERE status='1' AND type='$type' ORDER BY dt DESC LIMIT $from,6";
if ($result=mysql_query($query) or die (mysql_error()));
$tot = mysql_query( "SELECT FOUND_ROWS( )" );
echo "found " . mysql_result($tot , 0) . " Records";
11

Worked :smiley: