Ultimate Guestbook Tutorial: How to build a Guestbook with a honeypot, error checking, IP banning, pagination, e-mail notification and smilies with PHP and mySQL

We’re now at the final stage of our tutorial, pagination. Pagination are those things at the top/bottom of page which allows you to jump to segments of pages. On google you have pagination, page 1, 2, 3, 4, 5 etc. As do you on this website. We will have the same thing for our guestbook; but instead of 1,2,3,4,5 we will have 1-10, 11-20, 21-30 etc. To show blocks of 10 entries. Trust me, you don’t want 2000 guestbook entries on a single page.

Lets being! Open up skin.php and:

Find:

<tr>
		<td valign="top"><?php include('entries.php'); ?></td>
	</tr>

Above it add:

<tr>
		<td valign="top" align="center"><?php pagination($_GET['page'], $items); ?></td>
	</tr>

Correct. We will be using a function for this! But before we make the function, we need to do some setting up. Open up entries and:

Find:

$query = mysql_query("SELECT *, UNIX_TIMESTAMP(`date`) as date FROM `entries` ORDER BY `date` DESC");

Replace it with:

$query = mysql_query("SELECT *, UNIX_TIMESTAMP(`date`) as date FROM `entries` ORDER BY `date` DESC LIMIT ".$page.",".$items."");

This adds in a limit, where we will be able to get a certain number of items, with a certain starting point. For example, we only want to select 10 items after the first 5 items. This lets us do it. We have inserted some variables here, which will automatically calculate what needs to be fetched. So to do so add above it:

if(!isset($_GET['page'])){
		$page = 0;
	} else {
		$page = $_GET['page'] * $items;
	}

This looks at the address bar, and gets the value of ‘page’. We will be setting a numerical value for page. But what about the $items variable. Well this will be how many items we want to display on the page. I stored this value in the config.php file. So open up your config.php file which we put in the includes directory and add:

Add:

$items = 10;

Underneath:

$connect = mysql_connect($host, $username, $password);
	$dbselect = mysql_select_db($dbname);

Excellent! You can try this out straight away. Simply by going to the url and typing in page=1 or what not in the get component of the address bar:

http://www.yourdomain.com/index.php?page=1

Next we want to show some pagination, so that our users can simply click on some page numbers and get taken to the right spot, instead of typing a number up in the address bar. For my pagination i want it to have a link to go to the very first page, and to the very last page. I also want the page numbers to display a range of posts, and not simply a page number. Also i want to know what page i am on, and in turn it should show a different style for the current page. For this we will use another function, so open up your functions.php file and add:

function pagination($page, $items){
		$query = mysql_query("SELECT * FROM `entries`");
		$rows = mysql_num_rows($query);
		$pages = ceil($rows / $items);
		echo '<a href="'.$_SERVER['PHP_SELF'].'?page=0"><<</a> ';
		$tabs = 2;
		for($i=$page-$tabs; $i <= $page+$tabs; $i++){

			if(($rows - ($i * $items)) <= $rows && ($rows - ($i * $items)) > 0){
				if(($rows - (($i+1) * $items)+1) < 0){
					$top = 1;
				} else {
					$top = ($rows - (($i+1) * $items)+1);
				}

				if($page == $i){
					echo '<span>'.($rows - ($i * $items)).'-'.$top.'</span> ';
				} else {
					echo '<a href="'.$_SERVER['PHP_SELF'].'?page='.$i.'">'.($rows - ($i * $items)).'-'.$top.'</a> ';
				}
			}
		}
		echo '<a href="'.$_SERVER['PHP_SELF'].'?page='.($pages-1).'">>></a> ';
	}

Taking a deep breath? Me too. Let me explain line for line what is going on here. Using the line numbers above:

1: We have touched on functions before. This is no exception. This simply sets up the pagination function, with 2 parameters. This allows us to get the current page and how many items was set in the config.php file. Simple.

2-3: These lines simply select everything in the database and then count up how many rows were returned. This is done so that we can track the page titles, i.e. 51-42, 41, 32 etc.

4: The ceil function and the equation inside is used to calculate how many pages there are. This component would usually get used alot, however for this style of pagination we are only using it once, and that is on line 23.

5: This line sets up the first link, and that is the link to be taken to the newest posts. Notice how we simply create a hyperlink to page 0. That tells the SQL statement back on the entries.php page to select 10 items (set in the config.php file) starting from 0*10, which of course equals 0, so starting from the start.

6: This is a variable that i set up to determine how many pages will be infront and behind of the current page in our pagination. What if we had 1million posts, and we would only limit 10 posts per page, we would have 100,000 links in our pagination navigation bar. Not good. So this way we have a max of 5 at a time, 2 in front, and 2 behind. You can change this to be lower or greater.

7: Now the fun begins. It sets up a loop. But pay attention to

$i=$page-$tabs; $i <= $page+$tabs;

This starts $i off at -2 (2 behind) and stops the loop at +2 the current page. Now you’re thinking what will happen when we get to the first and last posts, won’t extra page titles be displayed? Even negative pages? Well we have taken care of them inside the loop.

9: Does exactly this. It tests if the page is within the max and min pages possible. That way you will never see an option to go higher than the max page, or lower than the first post! This is done with an IF statement which can escape all the display code.

10-14: This section sets up the 2nd number in the pagination. I.e 1 – 10. It sets up the 10. This is for the pages that don’t match up to our defined $items number. For example the last page may only have 1 post on it. So we calculate this, see if it is 0 and if it is under 0 then it knows that this is the last page possible, and in turn will simply display a 1.

The other case simply gets the total number by seeing what value $i is, increasing it by one to match the page under the current page (we dropped its value by 2 remember) multiplying it by the $items, to show the post count, adding 1 to it, otherwise we get an overlap of posts, and then subtracting it from the amount of rows returned. If you didnt follow that, don’t worry. Just think of it as we had some lag, we need always a page lower than the current, but we need to add an extra post otherwise the posts will overlap!

16: Is our style tester. It tests to see if the page display is the one we are looking at in the browser. If it is, it removes the link and makes the text grey. Easy. If it isnt the one on the page, i.e. the two before or behind it, then it make it a link. That should be easy enough to follow

23: Does like on line 5. It shows the very last (oldest) post page!

Also the css for the pagination class is:

.pagination {
	font-size: 14px;
}

Nothing really but smaller text! But you can style it to how you want it.

Now i noticed a potential bug! What happens when you delete an item, say some idiot posted something you don’t like. Then you’re page numbers and post numbers would be totally messed up. Because if you remember, our post numbers were displayed with:

<td valign="top" align="right">#<?php echo $row['id']; ?> Posted on: <?php echo date("d/m/y g:i a", $row['date']); ?></td>

Correct, with the id of the table cell in our database. So lets go back and fix this. Open up your entries.php file and:

Under:

if(!isset($_GET['page'])){
		$page = 0;
	} else {
		$page = $_GET['page'] * $items;
	}

Add:

$query = mysql_query("SELECT * FROM `entries`");
		$rows = mysql_num_rows($query);

It has to be like above, above the other SQL statement.

Next find:

$i++;

Move it to the bottom of the page where you see:

</tr>
	<?php } ?>
</table>

So it looks like:

</tr>
	<?php $i++;} ?>
</table>

Next find:

<td valign="top" align="right">#<?php echo $row['id']; ?> Posted on: <?php echo date("d/m/y g:i a", $row['date']); ?></td>

Change it to:

<td valign="top" align="right">#<?php  echo $rows - ($page)-$i; ?> Posted on: <?php echo date("d/m/y g:i a", $row['date']); ?></td>

Excellent! Test it out if you like. Delete a record and see how it affects the way the items are numbered. That way you should never see a hole in your posts, i.e so it won’t look like 4,5,6,8,12 instead it will always be, 4,5,6,7,8,9,10,11,12. Get me?

Finished! Phew! Oh wait, one more thing? E-mail Notifications!