Showing MySQL Results in Pages
By Ben Sinclair2005-04-19
Showing MySQL Results In Pages
Generating The Pages
First of all you need to find out how many links are in the database and then place them in pages Here's how:
<?phpConnecting To The MySQL Database
// ==============
// 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> \n";
}
if ($page>1) {
$prev_p=$page-1;
$prev_list="<a href='PHP_SELF?page=$prev_p'><< Prev</a>\n";
}
?>
Now that the script knows how many records and pages it has to create, you now retreive your information from the database:
<?phpPutting It All Together
$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;
}
?>
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:
<?phpThat's it! Hope this helps you with your page problems!
// ==============
// 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> \n";
}
if ($page>1) {
$prev_p=$page-1;
$prev_list="<a href='PHP_SELF?page=$prev_p'><< Prev</a>\n";
}
// 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;
?>
Enjoy!
Tutorial pages:
|
|
|||||||||
You might also want to check these out:
|
Leave a Comment on "Showing MySQL Results in Pages"
You must be logged in to post a comment.
Link to This Tutorial Page!

