How can I limit the number of rows returned by MySQL? (Pagination)

This is one of the most asked question I've ever seen in dev communities! it's called pagination.
It's been asked too many times, so I thought this might be the first tip I should add here !
Simply, all you have to do is add the LIMIT clause to your MySQL query and it should work fine!
Sounds easy, doesn't it ? ;)

Now all let's see an example:

PHP Code:

<?PHP //this query will fetch 3 records only! $fetch = mysql_query("SELECT * FROM table LIMIT 3")or die(mysql_error()); ?>

so the above query will fetch only the first 3 rows in the table.

There is another way to use LIMIT (which is the one we need!):

PHP Code:

<?PHP $fetch = mysql_query("SELECT * FROM table LIMIT 0, 10")or die(mysql_error()); ?>

This code will allow you to fetch 10 records starting from record 0 (the first), this is helpful when you have hundreds of records in the table and you want to show only part of it or make every set of the result in a page.
Now how can you make the next page show the next 10 records?
you simply have to store the value of the starting row in a variable and pass it in the URL as a GET variable.
We also have to check if there was a value already passed or not so we can set a default value in case it wasn't (zero to start from first row):

PHP Code:

<?PHP //check if the starting row variable was passed in the URL or not if (!isset($_GET['startrow']) or !is_numeric($_GET['startrow'])) {   //we give the value of the starting row to 0 because nothing was found in URL   $startrow = 0; //otherwise we take the value from the URL } else {   $startrow = (int)$_GET['startrow']; } ?>

Now your query should have this new variable ($startrow) in the LIMIT clause

PHP Code:

<?PHP //this part goes after the checking of the $_GET var $fetch = mysql_query("SELECT * FROM table LIMIT $startrow, 10")or die(mysql_error()); ?>

Now to see the next 10 records you should have a link which will add 10 to $startrow so you can view the next 10 records

PHP Code:

<?PHP //now this is the link.. echo '<a href="'.$_SERVER['PHP_SELF'].'?startrow='.($startrow+10).'">Next</a>'; ?>

It's that simple !!
if you want to have a previous link, just substract 10 from $startrow like this:

PHP Code:

<?PHP $prev = $startrow - 10; //only print a "Previous" link if a "Next" was clicked if ($prev >= 0)     echo '<a href="'.$_SERVER['PHP_SELF'].'?startrow='.$prev.'">Previous</a>'; ?>