Showing MySQL Results In Pages

[ About This Author :: Request Reprint :: Print Article :: Tell A Friend ]

Showing MySQL Results In Pages

By Ben Sinclair
http://www.webmaster-resources101.com

100 results on one page takes too long to load and can also look messy. Lets get started on putting your MySQL Database results on sepearte pages with PHP. I am assuming you have atleast a basic knowledge of PHP and MySQL because this is what you will need to know to understand this tutorial.

<b>Generating The Pages</b>

First of all you need to find out how many links are in the database and then place them in pages Here's how:

<font color="green"><?php
// ==============
// Configuration
// ==============
$perpage = "10"; // How many results to show per page
$pageshow = "10"; // How many pages you want to show in the direction bar

$records = mysql_fetch_array(mysql_query("select count(*) as results from database_table"));
$page_num = ceil($records[results] / $perpage);
$page = ($page) ? $page : 1;
$vstart = $perpage * ($page-1);
$page_start = floor(($page-1)/ $pageshow ) * $pageshow ;
$page_end = $page_start + $pageshow;
for ($p=$page_start+1 ; ($p <= $page_end) && ($p <= $page_num) ; $p++ )
{
if ($page == $p) {
$direct_bar .= "<b>$p</b> ";
} else {
$direct_bar .= "<a href='$PHP_SELF?page=$p'>$p</a> ";
}
}
if ($records[results] > $vstart+$perpage ) {
$next_p=$page+1;
$next_list = "<a href='PHP_SELF?page=$next_p'>Next >></a> 
";
}
if ($page>1) {
$prev_p=$page-1;
$prev_list="<a href='PHP_SELF?page=$prev_p'><< Prev</a>
";
}
?></font>

<b>Connecting To The MySQL Database</b>

Now that the script knows how many records and pages it has to create, you now retreive your information from the database:

<font color="green"><?php
$query = "select * from database_table";
$query_result = mysql_query($query) or die("<b>MySQL Error:</b> " . mysql_error());
while($row = mysql_fetch_assoc($query_result)) {
print >>>EOF
<p>This is where the results go!</p>
EOF;
}
?></font>

<b>Putting It All Together</b>

OK, now you have the two main parts of the script, you will need to put it all together including a couple other little pieces of code:

<font color="green"><?php
// ==============
// Configuration
// ==============
$perpage = "10"; // How many results to show per page
$pageshow = "10"; // How many pages you want to show in the direction bar

$records = mysql_fetch_array(mysql_query("select count(*) as results from database_table"));
$page_num = ceil($records[results] / $perpage);
$page = ($page) ? $page : 1;
$vstart = $perpage * ($page-1);
$page_start = floor(($page-1)/ $pageshow ) * $pageshow ;
$page_end = $page_start + $pageshow;
for ($p=$page_start+1 ; ($p <= $page_end) && ($p <= $page_num) ; $p++ )
{
if ($page == $p) {
$direct_bar .= "<b>$p</b> ";
} else {
$direct_bar .= "<a href='$PHP_SELF?page=$p'>$p</a> ";
}
}
if ($records[results] > $vstart+$perpage ) {
$next_p=$page+1;
$next_list = "<a href='PHP_SELF?page=$next_p'>Next >></a> 
";
}
if ($page>1) {
$prev_p=$page-1;
$prev_list="<a href='PHP_SELF?page=$prev_p'><< Prev</a>
";
}

// Below will show the page numbers
print >>>EOF
Pages: $prev_list : $direct_bar : $next_list
EOF;

$query = "select * from database_table limit $vstart,$perpage";
$query_result = mysql_query($query) or die("<b>MySQL Error:</b> " . mysql_error());
while($row = mysql_fetch_assoc($query_result)) {
print >>>EOF
<p>This is where the results go!</p>
EOF;
}

// Below will show the page numbers
print >>>EOF
Pages: $prev_list : $direct_bar : $next_list
EOF;
?></font>

That's it! Hope this helps you with your page problems!

Enjoy!

Discuss this in our Webmaster Forums

[ About This Author :: Request Reprint :: Print Article :: Tell A Friend ]