How to calculate the total iterations of a while loop when using PHP and MySQL?

Question

The Default code used to LIMIT the results fetched from the MySQL database using PHP is the following,

$sql="SELECT * 
FROM  `tablename` 
WHERE `Type` LIKE '$var1'
LIMIT 0 , 30";

When we need to display results continuously we can alter that code with the following

$start=0; $end=30;
$start=$_GET['start'];
$end=$start+30;
$sql="SELECT * 
FROM  `tablename` 
WHERE `Type` LIKE '$var1'
LIMIT $start , $end";

So I pass the value for the variable start with a link saying Next like this

<a href="something.php?start=<? $start+30 ?>">Next</a>

Everything goes fine when there are more results to display.

Consider this situation: There are 120 Entries in the Database and the PHP file is currently displaying results 91 to 120. So when I click the Next Link now it shows blank. So how can I make the "Next" link to be disabled when it is showing the last set of results? I think that could be possible if we know the total number of entries in the database. But if it is dynamic, how can we calculate it?

Sure, just send it as a second GET variable. And make sure you escape whatever you put into your LIMIT statement if it's coming from a GET variable: the user could insert malicious code there, so use mysql_real_escape_string or prepared statements. — Daan
– Daan, Commented Mar 27, 2012 at 19:52

Death · Accepted Answer · 2012-03-27 19:26:00Z

If you use a COUNT() function on the result, you can get simple info back.

For example:

$total=mysql_fetch_assoc("SELECT COUNT(*) AS `total` FROM  `tablename` WHERE `Type` LIKE '$var1'");

The value returned is the total number of entries that fit the given criteria. So:

$total['total']==120;

You can then simply do something along the lines of:

if (($start+30)>=$total['total']) {
    //Greyed out link
}
else {
    //Active link
}

It's probably also worth mentioning that your SQL conditions are faulty. The way you have it set up, passing a $start value of '10' to the file would create the query:

$sql="SELECT * 
FROM  `tablename` 
WHERE `Type` LIKE '$var1'
LIMIT 10 , 40";

Which grabs 40 results, starting from result 10, not the results between 10 and 40; a common misconception.

While I'm presuming that's a cut down version of the code, it's always worth using *mysql_real_escape_string()* on anything from the outside that goes into your query. People could easily put the $start value as "; DROP TABLE tablename;", making your query:

$sql="SELECT * 
FROM  `tablename` 
WHERE `Type` LIKE '$var1'
LIMIT ; DROP TABLE `tablename`;";

Which I'm sure you don't want :P

liquorvicar · Accepted Answer · 2012-03-27 19:34:12Z

0

You can use SQL_CALC_FOUND_ROWS as this doesn't take into account the LIMIT clause. You can then use SELECT FOUND_ROWS() to retrieve that value. That way you always get the count at the same time as your original query and avoid any concurrency issues.

Another way to do it would be to always try and get the next result. So always try and retrieve 31 rows, even though you are only displaying 30. You can then check this 31st row and if it exists you know there is another page to display.

answered Mar 27, 2012 at 19:34

liquorvicar

6,1462 gold badges19 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to calculate the total iterations of a while loop when using PHP and MySQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related