Helping ordinary people create extraordinary websites!

Showing MySQL Results in Pages

By Ben Sinclair
2005-04-19

Showing MySQL Results In Pages
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.

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: 

<?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> \n";

}

if ($page>1) {

$prev_p=$page-1;

$prev_list="<a href='PHP_SELF?page=$prev_p'><< Prev</a>\n";

}

?>
Connecting To The MySQL Database

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

<?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;

}

?>
Putting It All Together

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: 


<?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> \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;

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

Enjoy!


Tutorial pages:
  • Showing MySQL Results In Pages

  Votes

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!
GET OUR NEWSLETTERS