Pagination - making data from mysql list on multiple pages

[iGuest 3]

when your displaying multiple rows from a database, its a good idea to page them, i.e. so that you dont have a huge list of 100 rows from mysql on one page - rather, 10 on 10 pages, or 5 on 20 pages.

This script allows you to create a flexible version of this.

It can be shown in action here:
http://forums.xisto.com/no_longer_exists/

an example of the way it works:
http://forums.xisto.com/no_longer_exists/&pt=10&order=desc
this shows page one, with 10 on each page, in descending order.

http://forums.xisto.com/no_longer_exists/&pmit=5&order=asc
this shows page 2, 5 on each page, in ascending order.

p.s. if you like to fiddle you may find it doesnt work for big numbers - thats not a fault of the script, but simply that at the time of writing i only have 10 entries in my table. you may also notice that my layout is a bit dodgy - but dont worry about that, thats cos i mucked up the way my css loads by trying to be clever

just adjust the links on your page to show the data however you want it
^simple


Anywhere you need action or to take note there is writing in CAPITAL LETTERS

First create a mysql table with an id column and anythign else you wish
....e.g. a title, author, date, - as long as it has an auto incrementing ID this should work.


If you use this, Please link back to my site at
http://forums.xisto.com/no_longer_exists/, as this system took me a lot of time to make

/*----------------------------------news---------------------------------*/include 'db.php'///connect to db - file shown below INSERT YOUR MYSQL LOGIN DETAILS TO IT	$table = '';////SET THE ABOVE VALUE TO THE NAME OF YOUR TABLE$order = $_GET['order'];$a = $_GET['$a'];if (!$a) {/*-------This sets which way up the data is shown - there may be a quicker way but this works ----------------------*/if ($order == asc) {$order = asc;$order_news = desc;$ord = Descending;/*--------------------------------*/} else {$order = desc;$order_news = asc;$ord = Ascending;}/////DEFAULT IS DESCENDING - CHANGE ABOVEprint "<a href="?page=$page&order=$order_news">Order $ord</a><br>    n";    // get the pager input values    $pagenum = $_GET['pagenum'];if(empty($pagenum)) {$pagenum = 1;}///the above gets the page number from the url/////DEFAULT IS 1   $limit = $_GET['limit'];if(empty($limit)) {$limit = 5;}///the above gets the number per page from the url/////DEFAULT IS 5 - CHANGE ABOVE$result = mysql_query("SELECT id FROM $table");$total = mysql_num_rows($result);///how many rows are there in total? (above)$pages = ceil($total / $limit);$offset = ($pagenum -1) * $limit;/* divide the total rows by the amount per page and use the ceil function to round the value to the next highest integer e.g. 97 items on 10 pages would be 9.7 rounded up to 10 pages */   if ($pagenum == 1) {         echo "Previous";  	}  else  {        echo "<a href="?page=$page&pagenum=".($pagenum -1)."&limit=$limit&order=$order">Previous</a>";        echo " | ";  }/* if the current page is 1, then there is no previous page - so dont link to a previous page. otherwise link to it. */  $i = 1;  while ($i <= $pages) {            echo "<a href="?page=$page&pagenum=$i&limit=$limit&order=$order">Page $i</a>";  	$i++;         echo " | ";  	}  /* while a value is below the total number of pages, starting at 1, link to the intermediate pages */     if ($pagenum == $pages) {        echo "Next";  } else {        echo "<a href="?page=$page&pagenum=" . ($pagenum + 1) . "&limit=$limit&order=$order">Next</a>";   }///next page - using same logic as 'previous page'  /* THE FOLLOWING IS THE 'display items' bit of the script - the previous is the page link script - ALL YOU NEED is $table adjusted (earlier)  to match a table in your database which has some items in it *//* (below) select all items from the specified table that satisfy the conditions set by the url.*/$result = mysql_query("SELECT * FROM $table ORDER BY id $order LIMIT $offset, $limit");/* OUTPUT each article that satisfys the conditions of the url - i.e. all items on the selected page (below)  */while($news=mysql_fetch_array($result)) {require "template.php";/* require the layout of the news item - script it as you wish, my version is shown below, e.g. you could replace "require "template.php";" with  "print $news['title'].'<br>';" to show the title field's for each required item on a separate line*/}    }/*----------------------------------news---------------------------------*/

template.php, this is an edited version of mine, id recommend using your own - but you should already have one if you have enough items to page. even if each item is a few words and its just to print each item on a separate line - that will suffice.

<!--template start--><style type="text/css"><!--td,border {   border-width:1px;	padding: 4px;}--></style>     <tr>      <td width="100%" height="21" valign="top" bgcolor="#777799">   	<font color=#ffffff>                 <b>                   <b> <? echo $news['title'] ?>               </b> </font>    </td>      </tr>      	<tr>        <td height="119" valign="top" bgcolor="#FFFFFF"><p>           <font class="main"> <a name="<? echo $news['id'] ?>"></a>   <br><? echo stripslashes($news['text']);?>   <p>       <font class="comment">              <font class="author">posted by <a href="mailto:<? echo $news['email'] ?>"><? echo $news['name'] ?>	</a>              |</font>   	<font class="time">	<a href=""><? echo $news['time'] ?>	</a>  	</font>  </font>	<p>  </td></tr><!--template end-->

db.php

<?//set database connection variables$hostname='localhost';$uname='';$password='';$database='';//establish database connection$connect = mysql_connect($hostname,$uname,$password);//display error if connection failsif ($connect==FALSE) {   print 'Unable to connect to database: '.mysql_error();} else {//select databasemysql_select_db($database);}?> 

[iGuest 3]

What is this? A news page?Also, the script calls db.php and template.php which you don't include

[iGuest 3]

its just a script to make links to different pages...

template.php is just the file that shows for each item - i dont need to include it cos its not a news page

i.e. if you have 100 items in your db, then want to show 5 on each page... this script will give you links to 20 pages, each running 'template.php' 5 times with a different row from the database.

db.php is just the connection to the databae.

so this would suffice

<?//set database connection variables$hostname='localhost';$uname='';$password='';$database='';//establish database connection$connect = mysql_connect($hostname,$uname,$password);//display error if connection failsif ($connect==FALSE) {   print 'Unable to connect to database: '.mysql_error();} else {//select databasemysql_select_db($database);}?> 

Obviously you'd need to put your own password and db name in.

Then create a table...

once you've done that, set '$table' to be the name of the table...and read through it to change anything else ive written as a /* notice */ or ///notice in the script and it when you recall them it will do this:

an example of the script in action
http://forums.xisto.com/no_longer_exists/

http://forums.xisto.com/no_longer_exists/&pit=5&order=desc

it will show page 1, with 5 on each page, with the table ordered in descending order by the id field.... etc etc