Subscribe to PHP Freaks RSS

PHP Basic Pagination

Print
by Crayon Violent on Jun 18, 2008 6:11:55 PM - 338,521 views

Basic Pagination

As a web developer, you will often be tasked to display large amounts of data to the user in some kind of easy to read format. Let's say for instance you have a list of employees in your database, and you want to be able to list them on your web page. If you only have a dozen or so employees, it's no big deal to just make a simple loop and display them all on the same page, right? Well what happens when you have 50 employees? 100? 1,000? Suddenly listing all of them on the same page doesn't sound so hot.

Pulling out all that data at the same time can leave your user tapping his fingers on the desk wondering what the frak is taking so long, and when he finally does get his info, it's a whole frakking novel on one page! Can you imagine going down to the bookstore and picking up a book and instead of the story being divided up by pages, it's all on one really long page? I heard a rumor they used to do that back in the ancient days. I think they were called scrolls or something, I dunno.

Well anyways, it makes way more sense to break up your list into page-sized chunks, and only query your database one chunk at a time. This drastically reduces server processing time and page load time, as well as gives your user smaller pieces of info to digest, so he doesn't choke on whatever crap you're trying to feed him. The act of doing this is called pagination.

A basic pagination routine seems long and scary at first, but once you close your eyes, take a deep breath, and look at each piece of the script individually, you will find it's actually pretty easy stuff. In fact, in my experience over the years of helping out on the forums, peoples' hardest problem about pagination is figuring out what it's called in the first place! But since we've got that part sorted out, the rest should be a piece of cake, right? :)

First things first, you need a table with some data in your database to work with. Now I'm not going to go into the details of how to setup a database or how to make a table etc.. if you are really at that point of things, then this tutorial isn't really for you. It really doesn't matter what kind of data you have, so if you have an existing table full of info you want to use, you can just plug and chug your table/column info into the script. But for the purposes of this tutorial I will use a table called 'numbers' and it will have two columns one called 'number' type int and the other one called 'id' type int, auto-incremented.

If you opted to make a new table with that info, here's a quick and dirty script to get it populated:

<?php
$conn = mysql_connect('localhost','dbusername','dbpassword') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('dbname',$conn) or trigger_error("SQL", E_USER_ERROR);

for ($x = 0; $x < 106; $x++) {
   $number = rand(100,999);
   $sql = "INSERT INTO numbers (number, id) VALUES ($number, '')";
   $query = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
}
?>

Long story short: make one hundred six rows of random 3 digit numbers. Why 106? It's just a random number, and in the pagination script, we will make each page hold 10 rows, and I wanted the last page to show something less than 10 rows.

Why 3 digits? Why 6 rows at the end??? 3 digits and 6 rows were random numbers I chose and has nothing to do with the mark of the beast or the end of the world....or does it....???

Okay before anything else, here is the code in all it's full and basking glory, because there's nothing more I hate about tutorials than how people like to break them all into itty bitty little chunks and you have to copy and paste, copy and paste, copy and paste. It's aggravating....and yet it's somewhat contrary to the theme of this tutorial. Isn't it ironic?

<?php
// database connection info
$conn = mysql_connect('localhost','dbusername','dbpass') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('dbname',$conn) or trigger_error("SQL", E_USER_ERROR);

// find out how many rows are in the table 
$sql = "SELECT COUNT(*) FROM numbers";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 10;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;

// get the info from the db 
$sql = "SELECT id, number FROM numbers LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

// while there are rows to be fetched...
while ($list = mysql_fetch_assoc($result)) {
   // echo data
   echo $list['id'] . " : " . $list['number'] . "<br />";
} // end while

/******  build the pagination links ******/
// range of num links to show
$range = 3;

// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end if 

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
	 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      } // end else
   } // end if 
} // end for
		 
// if not on last page, show forward and last page links	
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/
?>

Okay so there's the code. Basically the idea is to:

- Find out how many rows you have in your table
- Find out how many pages you want to make, based on how many rows per page you want to show
- Find out what page we're on
- Pull out just the rows of the current page
- Display the info
- Make some links to go to the first page, previous page, some pages around the current page, the next page, and the last page.

There's comments aplenty in the code, so you may be able to figure out what's going on based on that, but let's go ahead and break it down for some further commentary. Moving on...

// database connection info
$conn = mysql_connect('localhost','dbusername','dbpass') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('dbname',$conn) or trigger_error("SQL", E_USER_ERROR);

Just a couple of lines of code to connect to your database, obviously.

// find out how many rows are in the table 
$sql = "SELECT  COUNT(*) FROM numbers";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];

We need to find out how many rows are in the table so we can figure out how many pages we are going to have. So we do a basic count(*) query and assign that number to $numrows.

// number of rows to show per page




$rowsperpage = 10;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

$rowsperpage is where we decide how many rows per page we want to have. To find out the total pages we will have, we take the number of rows ($numbrows) and divide that by the rows per page ($rowsperpage). Since there's no such thing as a half a page (unless your dog chews it up), we will round up, using ceil().

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if

When you click on one of the page navigation links, the target page is passed through the URL via the GET method. So here we want to grab the target page number. We want to first check to see if it's even there and if it is number, because if this is the first time someone loads the page, it won't be set. Or someone could manually change the value in the URL.

If it is there and it is numeric, we are going to force it to be type int. What this means is if someone were to change the page number in the URL to like 9.75, it will truncate it to 9, because there's no such thing as page 9.75 unless you're Harry Potter.

If the value fails to be set or is not numeric, we default to page 1.

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

The next thing we want to do is check to make sure it's a valid page number. We don't want users trying manually enter in page 401 in our 400 page book, just because they hate the ending and refuse to believe it ended that way omg what were we thinking nono there's GOT to be another page stuck together or something right??? So yeah... if they enter a negative number or some number higher than our total pages, it defaults to 1 or $totalpages, respectively.

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;

Since we have our list of 106 rows, and we want to only grab 10 specific rows of our target page, we need to find the "offset." That is, for example, page 8 is not going to start at the top of the list. It's going to start on row 80. Actually, it's going to start on row 70, since computers like to start counting at zero, not one. So we take our current page (let's say page 8), subtract 1 from it, and then multiply by our rows per page (10). Again, computers start at zero, so:

page 1 of our list will be rows 0-9.
page 2 of our list will be rows 10-19.
etc...

// get the info from the db 
$sql = "SELECT id, number FROM numbers LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

Now we run our query. It's a basic query telling sql to look at our numbers table, start at our offset, and give us 10 rows, starting at that offset. Since our table has 106 rows, if we are on the last page, it will give us the last 6 rows.

// while there are rows to be fetched...
while ($list = mysql_fetch_assoc($result)) {
   // echo data
   echo $list['id'] . " : " . $list['number'] . "<br />";
} // end while

Next we have a basic loop to display the rows of the current page. Nothing fancy like tables or divs or anything like that, as that is not the point of this tutorial. Next we look at building the navigation bar...

/******  build the pagination links ******/
// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end if

The first thing we want to do is make some "<< <" links. "<<" brings us back to page 1. "<" takes us to the previous page. If we are on page 1, then there's no reason to show the links, so our condition checks to see if we are on some other page besides page 1.

The first echo makes the "<<", passing page 1 as the target page. Since the "<" link targets the previous page, we make a variable that simply subtracts 1 from the current page, and make the link.

// range of num links to show
$range = 3;

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range)  + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
	 echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
      } // end else
   } // end if 
} // end for

In this chunk of code, we make the range of page links display. The idea is that if we're on page 8, it will show:

<< < 5 6 7 [8] 9 10 11 > >>

We start with a range ($range). The range covers how many numbers to the left and to the right of the current page (not total numbers to be displayed). The range in the code is 3, so if we are on page 8, it shows 5 6 7 on the left, 8 in the middle, 9 10 11 on the right. The loop starts at the current page minus 3, and iterates to the current page plus 3.

Inside the loop, we first check to make sure that the current iteration is a valid page number, because for instance, if we are on page 1, we do not want to show links to page -2 -1 0. The next thing we do is check to see if the current iteration of the loop is on the current page. If it is, we want to 'highlight' it by making it bold with brackets around it to make it stand out a little. We also do not want to make it a link, because it's just silly to make a link to the same page you are on. I can just imagine poorly written page raker or spider bots getting stuck in an infinite loop from that, which is kinda funny, except it's your bandwidth they are eating up.

Anyways, if it's a valid page number, and it's not the current page, then make a link of that page number. The end.

// if not on last page, show forward and last page links	
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page 
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
   // echo forward link for lastpage
   echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/
?>

This chunk of code works exactly like the first chunk of code that made the "<< <" links, except the opposite. If we're not on the last page, make the "> >>" links. Take the current page and add 1 to it to make the ">" link. Target page for the ">>" link is simply the $totalpages var.

Well, there you have it. That's really all there is to it. There are plenty of extra bells and whistles you can add on to this, like having links to sort your data by each column, etc.. the sky's the limit! Perhaps I will eventually publish some sort of "Expanded Pagination" tutorial.

Until then, Happy coding!

Crayon Violent

Comments

Ben Smithers Jun 19, 2008 8:47:14 AM

Thank god for that. I'm tired of having to tell people to google for pagination tutorials rather than being able to point them to something specific!

Reads well CV.

Rezert Jun 20, 2008 8:48:44 AM

You said there's nothing more annoying then having to copy and paste, copy and paste, copy and paste. But the point is you're not learning anything when you do it that way so they make it hard to just cheat and not learn.

Crayon Violent Jun 20, 2008 10:35:51 AM

If someone is looking to cheat, they would just disregard the commentary and copy and paste it anyways. I'm not in the business of "forcing" people to learn. I'm in the business of giving people the material to learn, the best way I know how.

If someone feels they need to be walked through each piece of code, it's there for them. If someone feels they can learn it by just looking at the code, it's there for them. If someone feels they can just c/p it into their code and hope some magic wand is waved...again, that's their prerogative.

It is certainly my choice to make tutorials, but if I do endeavor to do so, it is no longer a choice, but a responsibility to make the resource as available as possible. It is at no point in time my place to police how people use that information. In fact, I refuse to accept responsibility for that, at all. How do I know that someone will not use it for malicious purposes? Should I be held responsible for that? I think not!

Rodney Moore Jun 20, 2008 3:45:30 PM

I love the commentary on the tutorials. If I get stuck I always refer back to it. That being said I like the idea of having all the code up front so I can tinker and screw it up on my own. It's how I learn best.

Thanks for the great tutorial btw.

Rezert Jun 20, 2008 7:38:02 PM

Point well stated.

norwid Jun 22, 2008 1:50:14 PM

I cannot get this code to work. Has anyone encountered the same problem?

Crayon Violent Jun 22, 2008 4:48:14 PM

can you be more specific?

Christopher Burnside Jun 24, 2008 7:07:35 AM

Thank you CV great code

webent Jul 3, 2008 4:08:16 PM

Thank you Crayon Violent for the most excellent tutorial...
Very simple to follow, the only real changes that I had to make were, instead of using "{$_SERVER['PHP_SELF']}?", using "{$_SERVER['REQUEST_URI']}&" so it would tack on the pagination to my already existing stream of $_GET variables...

petitchevalroux Aug 1, 2008 8:02:26 AM

SQL_CALC_FOUND_ROWS can be used under MySQL instead of querying the first SELECT COUNT(*) FROM NUMBERS

vherhonne Aug 2, 2008 12:40:41 PM

thank you so much!! just what i needed... been searching the web for 3 hrs... i really appreciate it. now i can finally finish my project... thanks a bunch CV!!!!

GreenUser Aug 15, 2008 3:19:29 PM

This tutorial was great in helping solidify my pagination technique! Easily explained for dummies as well, always helps.

tmallen Sep 1, 2008 8:50:48 PM

I think the lines that find out the current page are both more readable and better written as:

$currentpage = is_numeric($_GET['currentpage']) ? (int) $_GET['currentpage'] : 1;

At minimum, isn't it redundant to check both isset() and is_numeric()?

Fluoresce Sep 16, 2008 3:36:32 PM

Thank you very much for this script, Crayon!

I must say, however, that I do have a small problem with it . . .

The problem is, the pagination links appear on the page whether results are returned or not. In other words, if someone conducts a query on my site which returns no results, the links to pages 1, 2 and 3, along with ">" and ">>", will appear nonetheless, thus:

1 2 3 > >>

And when clicked, these links will go to blank pages.

How can I fix this? I don't want links to blank pages if no results are returned.

I thank you for your advice.

Crayon Violent Sep 17, 2008 5:34:48 PM

Just wrap a condition around the whole pagination section.

if ($result > 0) {
// pagination section here
}

biciodark Oct 14, 2008 8:20:25 PM

i have a problem ,when i send with post method a query to my pagination page
the firt time i see the page ok but when i click on second page link i don't see nothing...

the pagination work if i write the sql query into my pagination page but not work if i send whit post method the sql query

i think that is the PHP_SELF that when reload page cancel the data into variable.

code :

$data1 = mysql_real_escape_string(@$_POST['data1']); //data scatto
$data2 = mysql_real_escape_string(@$_POST['data2']); //data scatto

$dida = mysql_real_escape_string(@$_POST['dida']);
$dida1 = mysql_real_escape_string(@$_POST['dida1']);
$nome = mysql_real_escape_string(@$_POST['nome']);
$stagione = mysql_real_escape_string(@$_POST['stagione']);
$tutto = mysql_real_escape_string(@$_POST['tutto']);

if ( $data1 == "" && $data2 == "" && $dida == "" && $dida1 == "" && ($nome != ""))
{

$sql = "SELECT id,nome,path_basse,path_alte,path_medie,descr_usata,larghezzab,altezzab,categoria,didascalia,date_format(data_evento,'%d-%m-%Y')as datetrans,note FROM foto where nome like '%$nome1%' order by data_evento asc LIMIT $offset, $rowsperpage";


}
any idea ?

thanks Biciodark

hakmir Oct 16, 2008 5:52:14 PM

I have exactly the same problem. First page comes ok. but other pages are empty. If you find an answer please let me know.

Crayon Violent Oct 16, 2008 6:28:17 PM

The problem is that your posted vars do not carry over from page to page. They are only passed to the page from the initial form submission. To keep them persisting, you need to either make them session variables or else add them to the links to be passed through the url and retrieved via the GET method (like the other vars).

If you are using a lot of posted info I would suggest using sessions, as there is a limit on how long a url string can be. Also, sessions are more secure.

Kayosz Oct 27, 2008 10:19:05 AM

Hi CV, cool script.

I don't know if anyone noticed this but in line 65 of the script it says:

for ($x = (($currentpage - $range) - 1); $x < (($currentpage + $range) + 1); $x++)

Now, if the page range is 3 the above line will actually create 4 page ranges instead of 3 left of the current page.

If one change the above line to read:

for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++)

it will correctly display the page ranges left of the current page.

Am I correct?

Kayosz

Crayon Violent Oct 27, 2008 11:02:34 AM

Ah, you are right. Don't know why I did that. Perhaps I was thinking ahead to +1 or something, I dunno, lol. Changed the tut code. Thanks for pointing that out :)

yepwingtim Oct 31, 2008 7:35:49 PM

Hi Crayon, nice tut. =D

I'm trying to put a delete query in the pagination. It works....
but it doesn't refresh properly. Once i hit DELETE, it's highlighted purple as purple, but doesn't go away till i refresh the page.

while ($list = mysql_fetch_assoc($result)) {
// echo data
echo $list['id'] . " : " . $list['number'] . <a href='{$_SERVER['PHP_SELF']}?currentpage=$currentpage&amp;var=$list[id]'>DELETE</a>" . "<br />";
} // end while

$a=$_GET['var'];

if(isset($a)){
$sql1="DELETE FROM numbers WHERE id='$a';";
$query1=mysql_query($sql1, $conn);
}

Is there anyway around this? thanks~

Thanks,

Daniel (new to php)

yepwingtim Nov 1, 2008 3:15:23 AM

if you guys want to quickly go to a specific page... =)

echo "<form name='page' id='page' action='{$_SERVER['PHP_SELF']}?currentpage={$_GET['currentpage']}' method='get'>";
echo "<input type='submit' value='Submit' /> <br>";
echo "<input type='text' name='currentpage' id='currentpage' /> <br>";
echo "</form>";

Crayon Violent Nov 2, 2008 1:37:43 AM

Daniel,

Simply move the part of your code deletes stuff to before the part that displays stuff, and you should be good to go. Although I have to tell you that what you are doing with that delete code block is not safe. All you are doing is checking to see if the var is set and then using it in your query, without making sure that it holds what it's supposed to be holding. Read up on sql injection to see what I mean.

mahender Nov 25, 2008 2:30:14 AM

Great Job!

I never know it was that easy.
You made it easy!

Thank You!

sw45acp Dec 17, 2008 7:47:27 PM

Hi,
I have found this tutorial very useful and easy to understand. However, I would like to apply the same code to a php mysql photo gallery. It works fine with this, but I only want the output section to do 4 thumbnails per row (as in after it echo's out 4 pictures there needs to be a <br /> at the end) and then start the next row. So I think I have to change the while() statement to a for loop. If I could have any help in modifying this, it would be greatly appreciated.

Crayon Violent Dec 17, 2008 9:02:09 PM

before the while loop, do something like this:

$x = 1;

and inside the while loop, do something like this:

echo ($x % 4 == 0)? "<br/>" : "";
$x++;

sw45acp Dec 17, 2008 9:12:24 PM

Yes, it does work, only when I added $x++ after the new statement, inside the while loop. Thank you for your help. What does the ? and the : mean in this syntax?

Crayon Violent Dec 17, 2008 10:04:13 PM

It's a ternary operator.

(condition)? do this if true : do this if false;

Shorthand for an if..else statement. Same as writing this:

if ($x % 4 == 0) {
   echo "<br/>";
} else {
   echo "";
}
sw45acp Dec 17, 2008 10:15:02 PM

ok thank you so much (you really know your stuff)

bateman Dec 22, 2008 11:22:12 AM

I was wondering if anyone else has hit the 60 rows max issue I'm having
It doesn't seem to want to go past that (goes to page 4)

OHMichelle Dec 28, 2008 4:54:07 PM

Great script.

I would like to display the results in a table format rather than a colon delimited list.
I've tried to modify this echo statement:

echo $list['regnum'] . " : " . $list['name'] . " : " . $list['color'] . "<br />";

With one to echo out <tr><td> of the 3 fields I have with no luck.

Help would be greatly appreciated.

Crayon Violent Dec 28, 2008 5:56:55 PM

Hey OHMichelle,

Showing your data in tabular format is pretty easy. I'm assuming that you want each row of data in its own table row (tr), and each of those variables in its own column (td). The code will look something like this:

echo "<table>";
while ($list = mysql_fetch_assoc($result)) {
   echo "<tr>";
   echo "<td>" . $list['regnum'] . "</td>";
   echo "<td>" . $list['name'] . "</td>";
   echo "<td>" . $list['color'] . "</td>";
   echo "</tr>";
} // end while
echo "</table>";
Flames Dec 30, 2008 3:55:27 PM

The code is great, i'd prefer something smaller but after testing i couldnt really find a way to do that, but the big thing you helped me with was the ternary operator, i've seen it all over the place never known what it was and its a nightmare to google about. Its made me curious if you can have an elseif ternary statement. e.g.
$cheese = ($hello == "1") ? "1" : ($hello == "2") ? "2" : "3";
If you do answer my question i'd rather you pm me it, cause ill forget to check this tutorial.
Thanks alot CV

Crayon Violent Dec 30, 2008 7:52:50 PM

You can nest a ternary inside another ternary but it's not the same as if..elseif...else. It's more like this (2 nested ternaries, 1 inside true, 1 inside false)::

if (...)  // ternary1 condition
   // ternary1 true value/exp
   if (...) // ternary2 condition
      // ternary2 true value/exp
   else // ternary2 false
      // ternary2 false value/exp
else // ternary1 false
   // terary1 false value/exp
   if (...)  // ternary3
      // ternary3 true value/exp
   else // ternary3 false
      // ternary3 false value/exp

Just google 'ternary operator' I'm sure you can find much better examples of what it can and can't do. In-depth look at conditions isn't really the scope of this tutorial...

katendarcy Jan 19, 2009 11:24:58 AM

First of all, thanks for the tutorial. Just what I was looking for. It's simple, straight-forward, and well explained. (I'm going to recommend it on my blog.)

Also, when implementing it on a project I'm working on, I changed:
$r = mysql_fetch_row($results);
$numrows = $r[0];
/*To:*/
$numrows = mysql_num_rows($results);

Any thoughts? (Performance wise, etc.) It seemed to work just as well. It actually didn't work quite right the first way, and I'm thinking that's because my table started with a high index because I had been testing. I tried this and it worked. Anyway...

Thanks again!

lordshoa Jan 28, 2009 6:17:48 AM

I am so close to get this working but its not for some reason, I had it working with the basic queries but once i try and add in my php code it all falls over.

I can do the pages manualy but the links at the bottom are missing it is for a xml output file so its kind of tricky to get the links working as its a rss feed.

Great bit of code thank you for the trouble of writing it.

Will ask on the forum for a bit of help see if anyone will be kind enough to help me.

Sherry Tingley Mar 28, 2009 5:14:23 PM

Could you go over how to add links and images to something like this?

echo "<table>";
while ($list = mysql_fetch_assoc($result)) {
echo "<tr>";
echo "<td>" . $list['regnum'] . "</td>";
echo "<td>" . $list['name'] . "</td>";
echo "<td>" . $list['color'] . "</td>";
echo "</tr>";
} // end while
echo "</table>";

Crayon Violent Mar 28, 2009 6:02:43 PM

The while loop is where each row from the returned query is pulled. You can format the results any way you like. The table I have is just an example. You can use echo to echo out whatever you want. Or print. Or close the php tag out ?> and just write out what you want and open it back up <?php when it gets back to the code.

As far as what you actually output...you can wrap stuff in html tags and your browser will mark it up just like it normally does. So for instance, if I wanted to make all of the data a certain color, I could just do this:

echo "<table>";
while ($list = mysql_fetch_assoc($result)) {
echo "<tr>";
echo "<td><font color='red'>" . $list['regnum'] . "</font></td>";
echo "<td><font color='red'>" . $list['name'] . "</font></td>";
echo "<td><font color='red'>" . $list['color'] . "</font></td>";
echo "</tr>";
} // end while
echo "</table>";

Or I could add an id or class attribute to something and mark it up with css. How you want to format it isn't really a php issue...

robm2002 Apr 3, 2009 5:50:52 AM

Thanks for spending the time to do this, you're officially a legend.

Rob.

bytesize Apr 12, 2009 8:06:22 PM

The column ‘number’ will contain urls. I would like all the data from ‘number’ to have links. Can you help me with this?

No need to reply, I worked it out. Thank you for writing this script, it works great!

man12_patil3 May 14, 2009 7:21:37 AM

head of u..............................
nice sir .......
can u explain curl and cron job also for me sir.............................

JPark Jun 13, 2009 10:52:02 PM

Crayon Violet, this tutorial rocks. I am definitely not the brightest bulb in the box and even I could understand it (everyone's comments and your answers to them helped also). Excellent stuff!

realcostdomains Aug 7, 2009 12:17:03 PM

Hello,

I implemented the code but I somehow keep having the 1st and last record from the database not showing up at all in my output - anyone have an idea why this may happen?

Thanks

guyfromfl Aug 17, 2009 2:15:37 AM

wow very great tutorial...VERY easy to incorporate into my code!!!

Thanks!!!!!

Rorschach Aug 24, 2009 3:01:03 AM

Thanks for the tutorial!

It is really good. :)

rk Aug 27, 2009 5:54:03 PM

Thank You very much for the tutorial.
Could you please give any example as to how i can pass session variables via the url with the use of GET method. I seem to still be getting a blank page.

skeep5 Sep 18, 2009 6:16:30 PM

thanks so much, this is the best.

Cliftron Sep 24, 2009 5:22:04 PM

Great Tutorial!

How would i change the order in witch the data was displayed?

iQue Oct 6, 2009 9:39:58 AM

Cliftron, hopefully this should help.

Example

// get the info from the db
$sql = "SELECT id, number
FROM numbers
ORDER BY id
DESC
LIMIT $offset, $rowsperpage";

sp@rky13 Oct 17, 2009 4:03:39 AM

Ok I'm totally confused. Where do I put the actual query in. I tried here:

// get the info from the db
$sql = "mysql_query("SELECT * FROM village28 WHERE player = '0' AND $BetweenClause, LIMIT $offset"";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

but that's not right lol. how do you put it in? I see the echo and understand that but not sure about the query :(

echoofali Nov 5, 2009 10:42:45 AM

Thankyou CV for a great tutorial, this is definitely a keeper that I can see myself using time and time again!

DMAN Nov 12, 2009 5:15:52 PM

I too am new to php and really appreciate your tutorial. I am trying to do a page that is ordered by user experience instead of an id and each new page starts with 1 where as i would like it to continue from the page before so if the first page ends with 10 page 2 will start with 11 any help would be great. Thanks in advance you guys are great.

c-o-d-e Dec 6, 2009 10:46:50 PM

Oh! This is brilliant! Just what I've always needed!
DMAN, post in the PHP Help sction.. I'll catch you out ;) I've got the fix for you.

CodeV Dec 10, 2009 12:47:07 AM

Thanks for the code. It functional and beautiful - easy to read & learn. You're an artist & a teacher! Rock on!!!

StarvingArtist Dec 12, 2009 7:23:21 AM

Hi CV --
As I've said elsewhere on this site, thank you very much for this code.

Now I have a question.

I eliminated the first and last links because I only want the numbers of the pages. And that is fine. BUT, there is a difference in the way IE and FF (latest versions of both) display them. FF works perfectly (of course).

IE insists on putting the [1] on a line by itself and the rest of the page numbers on a line below it. It's obviously not a big deal, but being that I'm a designer, it bugs the hell out of me. Here's the portion of your pagination code that I'm using below this post: (any ideas appreciated!)

And you can see it operation by going to
http://www.artbuttonworks.com/blue-pgs.php
and viewing it with both IE and FF.
Clair

/****** build the pagination links ******/
// range of num links to show
$range = 6;
// require("org-footer.php");
if ($result > 0) {

// if not on page 1, don't show back links
if ($currentpage > 1)
{
$prevpage = $currentpage - 1;
}
// loop to show links to range of pages around current page
for ($xy = ($currentpage - $range); $xy < (($currentpage + $range) + 1); $xy++)
{
// if it's a valid page number...
if (($xy > 0) && ($xy <= $totalpages))
{
// if we're on current page...
if ($xy == $currentpage) {
// 'highlight' it but don't make a link
echo " <b><span style=\"font-size: 200%; color: #FFCC33;\">[$xy]</b></span> ";
// if not current page...
}
else
{
// make it a link
echo "<a class=\"red\" href='{$_SERVER['PHP_SELF']}?currentpage=$xy'><span style=\"font-size: 200%; color: #FF3300;\">$xy</span></a></span> ";
} // end else
} // end if
} // end for
// if not on last page, show forward and last page links
if ($currentpage != $totalpages)
{

// get next page
$nextpage = $currentpage + 1;
// echo forward link for next page

// line below is mine and always appears at the end of the table.
echo "<span style=\"font-size: 140%; color: #FFFF66;\"> &#151; A small 'x' after the button name indicates laundering caution.</span> ";
} // end if
/****** end build pagination links ******/

gmcdaniel Jan 21, 2010 11:19:35 AM

Trying the code but get error saying that Parse error expect T_STRING

I read documentation for error but can not find problem with { or "

I verfied the code was good to the point where I put in a test echo. see the comments I placed in the code. I can not find anything wrong. server is running PHP 5.x

$totalpages = ceil($numrows / $rowsperpage);
// tested code was good to this point of test echo the next line and commented
// commented out every thing below this echo line.
echo "total rows $numrows total pages $totalpages";

if (isset($_GET["currentpage"]) && (is_numeric($_GET["currentpage"]))) {
    // the line below is where it says is an error
$currentpage = (int)$_GET["currentpage"];
} else {
   // default page num
   $currentpage = 1;
} // end if
/* I put in block comment to comment out rest of code and still get the error.

ZombieBento Mar 12, 2010 12:44:29 PM

Easy to understand, great content. This was just what I needed for a project I've been working on. Thanks so much for this helpful tutorial!

boiler97 Mar 22, 2010 12:15:03 PM

Great tutorial! Thanks very much. For those who are forced to use MSSQL here's a bit of code change to get it to work since MSSQL doesn't have a LIMIT function.

// the offset of the list, based on current page
$offset = ($currentpage - 1) * $rowsperpage;
//ADDED FOR MS SQL
$limit=$offset + $rowsperpage;

// CREATE A LIMIT QUERY FOR MS SQL DATABASES
$query = "WITH LIMIT AS(
SELECT FIELD1, FIELD2,
ROW_NUMBER() OVER (ORDER BY time_posted) AS 'RowNumber'
FROM some_table)
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit";
$result = mssql_query($query);
// while there are rows to be fetched...
while ($list = mssql_fetch_array($result)) {
echo $list['field1'] . " : " . $list['field2'] . "<br />";
}

boiler97 Mar 22, 2010 12:17:51 PM

One fix from my MS SQL post. ORDER BY "time_posted" should just be a field in you original query. Sorry about that.

FIXED CODE:
// the offset of the list, based on current page
$offset = ($currentpage - 1) * $rowsperpage;
//ADDED FOR MS SQL
$limit=$offset + $rowsperpage;

// CREATE A LIMIT QUERY FOR MS SQL DATABASES
$query = "WITH LIMIT AS(
SELECT FIELD1, FIELD2,
ROW_NUMBER() OVER (ORDER BY FIELD1) AS 'RowNumber'
FROM some_table)
select * from LIMIT WHERE RowNumber BETWEEN $offset AND $limit";
$result = mssql_query($query);
// while there are rows to be fetched...
while ($list = mssql_fetch_array($result)) {
echo $list['field1'] . " : " . $list['field2'] . "<br />";
}

korbenmxli Mar 23, 2010 2:54:30 AM

great code and great help... i try to put in the form of table but how can i put grid lines?? i try with
<table id="" border="1" cellpadding="0" cellspacing="0">; but no use... how can i do grid lines?? tnx in dvnc.

korbenmxli Mar 23, 2010 3:22:52 AM

SOLVED!! i replace de "" with \ so the code get like this
echo "<table width=\50%\ border=\2\">" ;

AniM Mar 24, 2010 11:04:21 AM

hi,
if we have more than 3 pages can you please show an example on how to add '...' after the 3rd page. Also if current page is 10 out of 20 pages and we show only +3-3 page range can we always keep the first and last 3 pages add then '...' between the current range and the first and last pages ?
should be something like

1 2 3 ... 7 8 9 [10] 11 12 13 ... 17 18 19 20

thanks in advance
AniM

pages Apr 11, 2010 9:16:56 AM

Thank you so much for this , You wouldn't believe the amount of over complicated "simple" tutorials out there

But yeah if you could add an option to show only a few of the pages like AniM above me suggested that'd be great
But thank you once again.

wordman Apr 12, 2010 7:35:25 PM

CV,

I found phpfreaks a few weeks ago and I am so glad i did. This tutorial is just what I am looking for. I have implemented it in a test page and have made necessary adjustments to the code because I am using MySQLi. The page doesn't bomb, but it isn't displaying any of the info, just the pagination links. I know it's calculated things correctly because the number of pagination links that show up are the correct amount.

Is there a good place to post the code or could it be sent to you via PM or email? I'm still testing locally.

Any info would be greatly, savagely appreciated. This site rocks!

Sincerely,

wordman

dev-amany Apr 25, 2010 5:53:59 PM

Thank you Crayon, that was of a great help

beaudierman Jun 3, 2010 2:35:30 PM

AniM, that is a very easy change and you can do it just by putting ... after the page 1 echo and before the last page echo.
IE:
echo " ...<a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages&sort=$sort#page=page-2'>$totalpages</a> ";

coreyavis Jun 4, 2010 3:55:03 PM

I was creating a script that doesn't use a database, but arrays. I had to modify the script accordingly. If anyone is interested in seeing what I did, just let me know.

php_girl101 Jun 6, 2010 6:54:43 AM

am going to try this n c what happens....so tired! hope it works n thnx for the post!

php_girl101 Jun 12, 2010 8:19:32 AM

it works! thnx

Zomxilla Sep 7, 2010 2:39:21 AM

This is exactly what I was looking for. The code itself is easy to understand, thanks very much!

adityamenon90 Sep 18, 2010 8:09:49 AM

Thanks a million! I didn't even have to read beyond the point you wrote the code, except for the "offset" part. I'm using this straight away, and it works fine! You are great, my lord, I bow in reverence! =)

Digma Oct 18, 2010 6:21:21 PM

Excellent. Thank you for this tutorial. Lets see if I can make this work with the way we transformed the url.

3k2k1 Oct 25, 2010 11:55:33 PM

VERY AWESOME TUTORIAL :)

cjohnweb Oct 26, 2010 6:18:17 PM

I have a very simple function I made in PHP specifically for easy and clean pagination results. Pass this function your SQL before the limit command, pass it what page you are on, and the rest of the params are optional. The function returns an array containing your page links Page: 1 2 3, etc, it returns your SQL with appended LIMIT function, and it returns some test like "Displaying 1-10 of 24 results", etc.

Check it out here:

<a href="http://iluvjohn.com/knowledge-database/computers/general-cross-platform/web-internet/php-mysql-curl/php-pagination-function-838/">http://iluvjohn.com/knowledge-database/computers/general-cross-platform/web-internet/php-mysql-curl/php-pagination-function-838/</a>

cjohnweb Oct 26, 2010 6:19:06 PM

I guess that link is just:

http://iluvjohn.com/knowledge-database/computers/general-cross-platform/web-internet/php-mysql-curl/php-pagination-function-838/

or search iluvjohn.com for "PHP Pagination"

sstavrid Nov 3, 2010 4:00:26 PM

Excellent post, I have a problem though and note that I'm really newbie with php.
I have made a small addition in the script since I don't want to show all info of my table but only those records satisfying a criterion (I have a form with a drop down list so that the user can select the region of his interest).
The form uses the GET method and the name of the drop down list is "Itm_8_00_1".
So I have changed the script to the following:
// database connection info
include("connect.php");
$prefecture2 = trim($_GET['Itm_8_00_1']);
// find out how many rows are in the table
if ("$prefecture2"=="All regions") {
$sql = "SELECT COUNT(*) FROM list";
}
else {
$sql = "SELECT COUNT(*) FROM list where (prefecture='$prefecture2')";
}
$result = mysql_query($sql) or trigger_error("SQL", E_USER_ERROR);

And again the relevant change on the second usage of &sql:
// get the info from the db
if ("$prefecture2"=="All regions") {
$sql = "SELECT area,prefecture FROM list LIMIT $offset, $rowsperpage";
}
else {
$sql = "SELECT area,prefecture FROM list where (prefecture='$prefecture2') LIMIT $offset, $rowsperpage";
}

$result2 = mysql_query($sql) or trigger_error("SQL", E_USER_ERROR);
// while there are rows to be fetched...
while ($list = mysql_fetch_assoc($result2)) {
// echo data
echo $list['area'] . " : " . $list['prefecture'] . "<br />";}
// end while

Here comes my problem, on submit, the first page loads perfectly but when I click any other following page I get the message :
Notice: Undefined index: Itm_8_00_1 in C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\eWeekend\files\mail_home_8_00.php on line 4

In line 4 I have the $prefecture2 = trim($_GET['Itm_8_00_1']);

Apologies for the long post,
Thanks,
Stelios

Jeffrey Fisher Nov 8, 2010 2:09:43 PM

You are very funny! I enjoyed this more than most programming articles. And it was very helpful too. You should consider a career as a writer, unless, of course, you already are a career writer. That would be one of those infinite loops you talk about.

The Schaef Jan 21, 2011 10:39:53 PM

The links don't display inline for me. I don't quite understand why this is but I've seen this happen a couple times with php. Help?

nadila Mar 20, 2011 9:15:25 PM

Hye CV, many thanks for this tutorial.

I having problem when I add column for record counter. How must I do to put the record 1-10 for 1st page, 11-20 for 2nd page and so on?

ziadhashim Apr 21, 2011 4:46:58 PM

thank you! thank you! thank you! thank you! you are a master, cut/ paste, read, learn ... everything is easy about your tutorial. I did everything all at once! Thank you and give us more!

ploppy May 31, 2011 11:20:32 AM

cv. when i echo out $numrows it shows 2063 which equals 207 pages at 10 to a page. however, when i run script i am seeing all records in one page but the links are still appearing up to 207 if i hit the last page. each link i press shows the same 2063 records on 1 page. i have copied your code exactly and no-one else seems to have this problem. any idea how i can start troubleshooting this? many thanks for all your efforts.

lightfuze Jun 3, 2011 9:32:34 AM

Thank you for this post. Still very relevant even after three years!

Divante Jun 20, 2011 4:55:26 PM

Thanks for this post truly helped me alot.

worff Jul 1, 2011 4:18:18 PM

Dude you have no idea how you helped me by posting this tutorial. I just found it and implemented it on a project I've been working for. I've been working with php for a few months and I'm starting to get things done but this is something I couldn't have done on my own yet.

Thank you a lot. Next beer I'll have will be on your health.

Nik2015 Jul 9, 2011 7:36:38 PM

Thought I would register having browsed the web for 2 days straight trying to find a pagination tutorial to learn from and getting nothing but errors. You had the one that took minutes to re-write and I intend to learn it fully. Thanks for providing such a simple tutorial and most importantly one that works. If only I had found this earlier.

Thanks again.

gentlebreeze Aug 2, 2011 3:08:43 PM

Hi CV..actually, this is my first time to post a comment for a certain great tutorial. I am a newbie in PHP and is currently working for a PHP project. I merged your code in my program and it WORKS!!!! Thank you so much...
You have done very well. Keep it up...

zuzuleinen Aug 3, 2011 11:29:25 AM

Thanks a lot. You explanations are very logic. I searched a lot for an good example. Glad I found it.

Al - Aug 29, 2011 2:01:14 PM

Thanks for this AWESOME tutorial. Saved my skin.

sgtlopez Sep 3, 2011 1:24:06 PM

Im a php newbie.....how would i add a search box to this code?

iansane Sep 16, 2011 2:36:06 PM

Thanks Crayon! I tried this from about.com and it was almost the same but had something wrong and was old coding. your's works great and in 5 to 10 minutes I changed the numbers, prev, and next to css styled with no problem.

sgtlopez,
I added a search box like this.
1. add html form to top of page and use get to add the search term to the url when submitted.
2.use if() statement to change the sql statements if there is a search term
3. use if()statements to change all the links to include the search term.
4. add a link "Clear Search" that just refreshes with no get parameters.

Here's the code I came up with. It's sloppy but works and should give you some ideas about what you can do to extend this.

The script now changed to have html and css:
<html>
<head><title></title>
<link rel="stylesheet" href="default.css" type="text/css"/>

<body>

<?php
//get the search term if it exists
if(isset($_GET['searchTerm'])){
$searchTerm = $_GET['searchTerm'];
}
?>

<div id="search_box">
<form id="searchForm" name="searchForm" action="index.php" method="GET">
<input type="text" id="searchTerm" name="searchTerm"></input>
<input type="button" id="submit" name="submit" value="SEARCH"></input>
</form>
</div>

<?php

// database connection info
$conn = mysql_connect('localhost','root','dbpassword') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('books',$conn) or trigger_error("SQL", E_USER_ERROR);

// find out how many rows are in the table
//use search term if isset
if(!isset($searchTerm)){
$sql = "SELECT COUNT(*) FROM titles";
}else{
$sql = "SELECT COUNT(*) FROM titles WHERE title LIKE '%$searchTerm%'";
}
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];

// number of rows to show per page
$rowsperpage = 10;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
// cast var as int
$currentpage = (int) $_GET['currentpage'];
} else {
// default page num
$currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
// set current page to last page
$currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
// set current page to first page
$currentpage = 1;
} // end if

// the offset of the list, based on current page
$offset = ($currentpage - 1) * $rowsperpage;

// get the info from the db
//use searchTerm is isset
if(!isset($searchTerm)){
$sql = "SELECT title FROM titles LIMIT $offset, $rowsperpage";
}else{
$sql = "SELECT title FROM titles WHERE title LIKE '%$searchTerm%' LIMIT $offset, $rowsperpage";
}
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);

// while there are rows to be fetched...
while ($list = mysql_fetch_assoc($result)) {
// echo data
echo $list['title'] . "<br />";
} // end while

//add link to clear search by go home
echo"<br/><br/><a href='index.php'>Clear Search</a>";

/****** build the pagination links ******/
// range of num links to show
$range = 3;

//two different styles to keep from moving back and forth
if($currentpage == 1){
echo "<div id='plinks1'>";
}
else{
echo "<div id='plinks2'>";
}
// if not on page 1, don't show back links
if ($currentpage > 1) {
// show << link to go back to page 1
if(isset($searchTerm)){
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1&searchTerm=$searchTerm'><<-FIRST</a> ";
}else{
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<-FIRST</a> ";
}
// get previous page num
$prevpage = $currentpage - 1;
// show < link to go back to 1 page
if(isset($searchTerm)){
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage&searchTerm=$searchTerm'><-PREV</a> ";
}else{
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><-PREV</a> ";
}
} // end if

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
// if it's a valid page number...
if (($x > 0) && ($x <= $totalpages)) {
// if we're on current page...
if ($x == $currentpage) {
// 'highlight' it but don't make a link
echo " <span id='pnum_current'>$x</span> ";
// if not current page...
} else {
// make it a link
if(isset($searchTerm)){
echo " <span class = 'pnum'><a href='{$_SERVER['PHP_SELF']}?currentpage=$x&searchTerm=$searchTerm'>$x</a></span> ";
}else{
echo " <span class = 'pnum'><a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a></span> ";
}
} // end else
} // end if
} // end for

// if not on last page, show forward and last page links
if ($currentpage != $totalpages) {
// get next page
$nextpage = $currentpage + 1;
// echo forward link for next page
if(isset($searchTerm)){
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage&searchTerm=$searchTerm'>NEXT-></a> ";
}else{
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>NEXT-></a> ";
}

// echo forward link for lastpage
if(isset($searchTerm)){
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages&searchTerm=$searchTerm'>LAST->></a> ";
}else{
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>LAST->></a> ";
}
} // end if
/****** end build pagination links ******/
echo "</div>";
?>
</body>
</html>

Here's my css:
.pnum{
padding-left:5px;
padding-right:5px;
background-color:#e3e3e3;
border:1px solid gray;
}

.pnum a{
text-decoration:none;
font-family: Arial, 'Times new Roman', sans-serif;
}

#pnum_current{
/*current page link slightly larger and different color*/
padding-left:7px;
padding-right:7px;
padding-top:2px;
padding-bottom:2px;
background-color:#ffffff;
border:1px solid gray;
font-weight:bold;
}

#plinks1{
position:absolute;
top:500px;
left:150px; /*allow room for first and prev links*/
}

#plinks2{
position:absolute;
top:500px;
left:23px; /*tweak number till page links don't move*/
}

That was just some quick and dirty throwing together of code. I positioned the links in a div and made it so it doesn't move when the "first" and "prev" links disappear. Also you should never have a live form for user input without sending the data through sanitizing like mysql_real_escape and strip_slashes and some filtering for unauthorized search terms. I didn't do any of that with this code.

iansane Sep 16, 2011 2:39:09 PM

In the code above,
I used a sample books.sql database I found somewhere on line instead of the numbers so I was able to confirm that the search terms worked. With "Java" it only listed books with "Java" in the title and so on for other terms I tried.

iansane Sep 16, 2011 10:40:08 PM

oops one more comment. I made a mistake with the submit button. I was using the enter key and didn't notice the button doesn't even work.

Should be:
type="submit" instead of type="button"

tenzijth Oct 28, 2011 12:29:33 PM

Hi, thanks for the tutorial. THe code looks really straight forward, but I'm new to PHP and I can't wrap my head around something. I have an SQL query retrieving data from various tables. How do I adjust this code for that and at what point do I insert the "find out how many rows" part?. Here's the code I'm working with. Thanks in advance to anyone who can help.

<?php

// db connection etc..

$select = 'SELECT DISTINCT joke.id, joke.joketext, joke.jokedate,
author.id AS author_id, author.name AS author_name,
jokecategory.jokeid AS cat_jokeid, jokecategory.categoryid AS joke_catid, category.id AS cat_id, category.name as cat_name,
joketheme.jokeid AS theme_jokeid, joketheme.themeid AS joke_themeid, theme.id AS theme_id, theme.name AS theme_name,
jokegeofocus.jokeid AS geofocus_jokeid, jokegeofocus.geofocusid AS joke_geofocusid, geofocus.id AS geofocus_id, geofocus.name AS geofocus_name';
$from = ' FROM joke, author, jokecategory, category, joketheme, theme, jokegeofocus, geofocus';
$where = ' WHERE joke.authorid = author.id AND joke.id = jokecategory.jokeid AND jokecategory.categoryid = category.id AND joke.id = joketheme.jokeid AND joketheme.themeid = theme.id AND joke.id = jokegeofocus.jokeid AND jokegeofocus.geofocusid = geofocus.id';
$in = ' ORDER BY jokedate DESC';

$jokes = @mysql_query($select . $from . $where . $in);
if (!$jokes) {
echo '</table>'; exit('<p>Error retrieving jokes from database!<br />'.
'Error: ' . mysql_error() . '</p>');
}

while ($joke = mysql_fetch_array($jokes)) {
$id = $joke['id'];
$joketext = htmlspecialchars($joke['joketext']);
$jokedate = htmlspecialchars($joke['jokedate']);
$aname = htmlspecialchars($joke['author_name']);
$category = htmlspecialchars($joke['cat_name']);
$theme = htmlspecialchars($joke['theme_name']);
$geofocus = htmlspecialchars($joke['geofocus_name']);
echo "<li id=\"jump\">
<article class=\"entry\">
<header>
<h3 class=\"entry-title\"><a href=''>$aname</a></h3>
</header>
<div class=\"entry-content\">
<p>$joketext</p>
<p>$category</p>
<p>$theme</p>
<p>$geofocus</p>
</div>
<footer class=\"entry-info\">
<abbr class=\"published\">$jokedate</abbr>
</footer>
</article>
</li>";
}

?>

badger_fruit Nov 4, 2011 9:53:34 AM

Thank you for a great article; well presented and amusing - helped a LOT!
Regards

melting_dog Dec 30, 2011 6:54:34 PM

Great Tute! Glad how you broke it up into chunks and explained each section. Only thing I did not get working was {$_SERVER['PHP_SELF']} but I was able to hard code it with my URL.

Thanks

melting_dog Jan 7, 2012 7:55:08 PM

I also added this if($numrows > $rowsperpage ){ above where my pagination links are created so that they would not show if there was only one page.

OldWest Jan 13, 2012 3:48:58 PM

Hey. This is a great tutorial. It's always nice to see programming that works, efficient and makes sense.

pdw Feb 5, 2012 4:48:48 AM

Great tutorial! Works like a charm!

stat30fbliss Mar 7, 2012 5:44:48 PM

Great, great, great tutorial! Thanks for the great tut styling, and functional script!

bernte May 23, 2012 7:14:10 PM

your script is awesome!!
but i have a little question.. is it possible to give page 1 another link via php than
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";

because page 1 creates duplicate content.
www.site.com/news.html and www.site.com/news.html?currentpage=1 is the the same

Add Comment

Login or register to post a comment.