Paginate mysql results in php

This time I’m going to show you how you can paginate mysql results in php. Basically what were going to do here is to divide the entirety of the records which are being fetched by php from the mysql database. We divide it into multiple pages so that the user will not scroll down endlessly if there are over a thousand records.

Requirements

  • Wampserver
  • EZ Sql

Step

First, let’s include the ez sql class into our script. Then declare an object of it.

<?php
	include('shared/ez_sql_core.php');
	include('mysql/ez_sql_mysql.php');
	
	$db = new ezSQL_mysql("root", "", "payroll", "localhost");
?>

Then let’s capture how many records are returned by selecting all the records from the employee table. And store it in a variable called $max. We also create a variable called $current. Which will just store the upper limit of the mysql query that were going to create later:

$current = $_GET['page'];
$max = $db->query("SELECT * FROM employee");

Next, let’s make sure that we are only going to output something as long as the upper limit ($current) is not greater than the total number of results returned ($max). And that the upper limit ($current) will not be lesser than 0 (negative numbers).

if((!($current > $max)&&($current >= 0))){

}

Anything which will stay inside this script will not be outputted unless the condition is met.

Next, we set how many results are displayed on every page. I choose 5 since there are currently 15 rows in the employee table.

$disp = 5;

Then we set the upper limit to zero. If it doesn’t have any value.

if(!$current)	
$current = 0;

Then we set the value for the next and previous links. Its self explanatory but I’m going to explain it anyway. For the value for $next what were doing is just to add the number of results which are shown, in this case it is 5. To the current upper limit ($current). And for $prev, were just doing the opposite. 

$next = $current + $disp;
$prev = $current - $disp;

Were basically done with setting up the values for the previous and next links. What were going to do now is to put up some pages in the output. So that the user will have the idea how much data he is browsing. First, we declare a variable called $index which we initialize to 0. Were going to use this as the page which will be outputted. We can’t use the upper limit for this, because it increments by 5 every time your turn the page. What we want is something like 1  2  3    and not 5  10   15. Then we declared a for loop which will only loop until the final row. Then the increment value that we used works just like the $next variable.

$index = 0;

for($x=0; $x < $max; $x =$x + $disp){

}

Next, we put something inside the for loop. What the code below does is to output the page numbers. The higher the value of the $disp variable the fewer the pages would be. But of course it all depends on how many rows are being returned. As you can see, there’s an if statement which checks whether the upper limit ($current) is not equal to the value of $x. Which stores the current page value plus the number of rows shown per page. If its not equal, we just output it as plain link. If its equal, then we show some visual to the user by using the bold tag. This way, the user will have an idea what page he is currently is.

if(!($current == $x)){
	echo "  <a href='mysqlpagination1.php?page=$x'>$index</a>  ";
	}else{
	echo "  <b><a href='mysqlpagination1.php?page=$x'>$index</a></b>  ";
	}
	
	$index++;

And we almost forgot the previous and next button. For the previous link, we first check whether we are not already at the first page. This means that the previous link will not show up unless we are at a page greater than 0. For the next link, we check whether the current page is not greater than the value of max rows ($max) and number of rows displayed per page ($disp). This basically means that the next link will not show up if were already at the last page.

<?php if(!($current <= 0)){ ?>
<a href="mysqlpagination1.php?page=<?php echo $prev; ?>">prev</a>
<?php } ?>

<?php if(!($current >= $max - $disp)){?>	
<a href="mysqlpagination1.php?page=<?php echo $next; ?>">next</a>
<?php } ?>	

Finally we display the results in a table. Yup you are not mistaken, were still inside the first if statement that we created earlier.

<?php	
$employees = $db->get_results("SELECT * FROM employee LIMIT $current, $disp");	
?>

<table border="1">
<tr>
<th>Firstname</th>
</tr>	
<?php
	foreach($employees as $e){
		echo '<tr><td>'. $e->Firstname. '</td></tr>';
	}
?>
</table>

 

Conclusion

That’s it for this tutorial. I’ll see you again next time that I have vacant time.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s