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

Meta: April 26th 2008 // PHP // 44,664 views

Only 3 things left to do. Show our guestbook entries, show smilies and install our pagination. How about we finish off this tutorial in that order then!

Make a new file in the templates folder called entries.php. Then open up skin.php and:

Under:

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

Add:

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

All this does is link the entries.php file into the skin.php file. Now lets open up our entries.php file:

<?php
	$query = mysql_query("SELECT *, UNIX_TIMESTAMP(`date`) as date FROM `entries` ORDER BY `date` DESC");
?>
<table width="100%" cellpadding="0" cellspacing="0" border="0">
	<?php	
		while($row = mysql_fetch_array($query)){ 
		$i++;
		if(($i % 2) == 0){
			$class = 'class="entry1"';
			$classbg = 'class="entriesbg1"';
		} else {
			$class = 'class="entry2"';
			$classbg = 'class="entriesbg2"';
		}
	?>
	<tr>
		<td valign="top" <?php echo $classbg; ?>>
		<table width="100%" cellpadding="0" cellspacing="0" border="0">
			<tr>
				<td valign="top" align="right">#<?php echo $row['id']; ?> Posted on: <?php echo date("d/m/y g:i a", $row['date']); ?></td>
			</tr>
			<tr>
				<td valign="top">
				<table width="100%" cellpadding="0" cellspacing="0" border="0" <?php echo $class; ?>>
					<tr>
						<td valign="top"><span>Name:</span></td>
						<td valign="bottom" width="100%"><?php echo stripslashes($row['name']); ?></td>
					</tr>
					<tr>
						<td valign="top" nowrap="nowrap"><span>E-Mail:</span></td>
						<td valign="bottom" width="100%"><?php echo stripslashes($row['email']); ?></td>
					</tr>
					<?php
						if(strlen($row['website']) > 0){
							if(substr(strtolower($row['website']), 0, 7) != 'http://'){
								$website = 'http://'.$row['website'];
							} else {
								$website = $row['website'];
							}
					?>
					<tr>
						<td valign="top" nowrap="nowrap"><span>Website:</span></td>
						<td valign="bottom" width="100%"><a href="<?php echo stripslashes($website); ?>" target="_blank"><?php echo substr($website,0,30); ?></a></td>
					</tr>
					<?php
						}
					?>
					<tr>
						<td valign="top" colspan="2"><span>Message:</span></td>
					</tr>
					<tr>
						<td valign="top" colspan="2"><?php echo stripslashes($row['message']); ?></td>
					</tr>
				</table>
				</td>
			</tr>
		</table>
		</td>
	</tr>
	<tr>
		<td valign="top" height="10"></td>
	</tr>
	<?php } ?>
</table>

There is alot of CSS going on here, so here is the new CSS i added, add it to your stylesheet, or add your own:

.entries {
	padding: 10px 10px 0px 10px;
	border: #999999 solid 1px;
	font-size: 14px;
	background: #333333;
}
 
.entriesbg2 {
	padding: 10px;
	border: #999999 solid 1px;
	font-size: 14px;
	background: #111111;
}
 
.entriesbg1 {
	padding: 10px;
	border: #999999 solid 1px;
	font-size: 14px;
	background: #555555;
}
 
.entrytitle {
	font-family: Helvetica, Arial, Verdana, Courier;
}
 
.entrysmall {
	font-size: 12px;
}
 
.entry1 {
	padding: 5px;
	margin: 2px;
	border: #222222 solid 1px;
	background: url('../images/entry2.jpg') repeat-x #000000;
}
 
.entry2 {
	padding: 5px;
	margin: 2px;
	border: #555555 solid 1px;
	background: url('../images/entry1.jpg') repeat-x #333333;
}
 
a {
	color: #FFFFFF;
	text-decoration: none;
}
 
a:hover {
	text-decoration: underline;
}

Correct, there are some images i used. You can get them from the source pack, or make your own background images. Just make a folder in the templates folder called images, and drop the entry1.jpg and entry2.jpg files in there.

Now time for some explanation, as usual i will be using the line number to explain what is going on; but for the most part it does not require any explanation.

2: This is our SQL statement, which gets all of the entries, orders them by the date (newest first) and converts the mysql timestamp to a unix_timestamp, something php can understand.

6: This sets up a while statement, which runs through each logical row in the array, or the output that the SQL query generated.

7: Sets up a variable called $i which is self incrementing, meaning each time the while loop iterates (runs through) it increases its value by 1. Which is helpful for lines 8-14

8-14: This section helps us get alternating row colours. Well not really colours, in our case its styles. It uses the modulus operator, %. Which basically shows the remainder of a division. For example: What is the remainder or 3 going into 15. It would be 0, because 3×5 = 15, so it fits perfectly. But 3 % 5 would return 2. Because 3 only goes in once, leaving a remainder of 2. You get it?

Then the relevant classes are stored into variables, which will be overwritten each iteration.

17: Shows how we use this alternating rows. It simply outputs the variable and in turn chooses the right style.

20: This is the first line that shows us how to access our new array. We stored the SQL array into the variable $row. So now it can be accessed via: $row['id']; Each time the loop runs through the array steps through, so its like reading the table row by row, the variables don’t change, just the values.

34: This checks if there is a value for the website, if not it won’t display the title/section.

35: This checks to see if there is ‘http://’ at the front of the website address. If not then it will add it.

43: This line just shows a partial segment of the website URL, as we don’t want a 200 character long website address destroying our theme style!

That’s pretty much it! Also note the stripslashes. We put the text in with slashes, so we should remove them in order to show the information. Play around with the style, the style is pretty dark and evil at the moment, so if you like rainbow colours go style it up! Next we will get some smilies into the messages!

Tags: guestbook, honey, honeypot, mysql, pagination, PHP, pot, smilies, sql

Pages: 1 2 3 4 5 6 7 8 9 10 11 12 13

Postscript: Leave A Comment // Subscribe (RSS Feed)

Twitter

@melindabuttle was too funny! If you haven't seen her at @briscomfest yet then book those tickets now! 8 hours ago

About to see @melindabuttle at the @briscomfest look forward to seeing her after seeing her damn funny tweets 9 hours ago

@predominant Ooo why thank you :) Where can i see those stats that you're indulging in? 2010/03/12

Woops http://yfrog.com/0k86cj 2010/03/10

The Twitter screen we built at @bigfishtv for @briscomfest. Updating awesomely! Now time to see Claudia O'Doherty! 2010/03/10

Comments About 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

// 57 comments so far.

VLADIS // June 02nd 2008

I make this guestbook.
It go not me. It always write this errors:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /3w/wz.cz/m/medvede/5/includes/actions.php on line 3

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /3w/wz.cz/m/medvede/5/includes/functions.php on line 12

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /3w/wz.cz/m/medvede/5/templates/entries.php on line 15

i have it on http://medvede.wz.cz/5/index.php

Help me, please.
thank you
VoiDeT // June 02nd 2008

Hey Vladis,

Did you make sure you have created the database correctly?

Please make sure you have done this, otherwise this error would definitely show up.
VLADIS // June 03rd 2008

I make this TABLE :

CREATE TABLE `entries` (
`id` int(8) NOT NULL auto_increment,
`name` varchar(255) collate latin1_general_ci NOT NULL,
`email` varchar(255) collate latin1_general_ci NOT NULL,
`website` varchar(255) collate latin1_general_ci NOT NULL,
`message` text collate latin1_general_ci NOT NULL,
`date` timestamp NOT NULL default CURRENT_TIMESTAMP,
`ip` varchar(15) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

and this:

CREATE TABLE `spam` (
`id` int(8) NOT NULL auto_increment,
`ip` varchar(15) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `ip` (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

I have in config.php this code:

And it doesn’t go.
VLADIS // June 03rd 2008

I have in config.php this code:

$host = ‘mysql.webzdarma.cz’;
$username = ‘medvede48′;
$password = ‘xxx’;
$dbname = ‘guestbook’; – - I try also entries
$email = ‘your@email.com’;
$connect = mysql_connect($host, $username, $password);
$dbselect = mysql_select_db($dbname);
$items = 10;
VoiDeT // June 03rd 2008

Can you please provide me with your ftp details?
It could be my end, or it could be your end. But i thought i tested this script without any rows in the database. Let me know
VLADIS // June 03rd 2008

I have it on: photoshopsk.wz.cz
password: 7754705

I have files from this tutorial.
VoiDeT // June 04th 2008

And your username for me to log in please?
VLADIS // June 05th 2008

(https://www.webzdarma.cz/)
My username on FTP is : photoshopsk.wz.cz
and password: 7754705

(https://www.webzdarma.cz/mysql/index.php)
And username on mysql server is: photoshopsk
password:ragp3s
VoiDeT // June 07th 2008

Those settings do not work.

I need username, password, and address.

Otherwise i cannot look for you.
VLADIS // June 07th 2008

Look you:
1) http://photoshopsk.wz.cz/1/1.JPG
2) http://photoshopsk.wz.cz/1/2.JPG
3) http://photoshopsk.wz.cz/1/3.JPG

Do you thing this or no?
If no this, then what you think? What of address?
Linnea // June 09th 2008

Hi! I just want to say thank you for a wonderful tutorial. I will probably use this at my website when I have finished it, so I can send the link later. Thank you!
VoiDeT // June 09th 2008

@ Linnea – Thanks alot for your comment. I would love to see your website when you have finished with it!

@Vladis – Doesn’t work dude. Maybe you have limited the IP range of access?
VLADIS // June 11th 2008

My action what I make.

1.) I am download this tutorial: http://www.jotlab.com/wp-content/uploads/2008/04/guestbook.zip

2.) I give it on a web all. (http://photoshopsk.wz.cz/)

3.) I am create table :

CREATE TABLE `entries` (
`id` int(8) NOT NULL auto_increment,
`name` varchar(255) collate latin1_general_ci NOT NULL,
`email` varchar(255) collate latin1_general_ci NOT NULL,
`website` varchar(255) collate latin1_general_ci NOT NULL,
`message` text collate latin1_general_ci NOT NULL,
`date` timestamp NOT NULL default CURRENT_TIMESTAMP,
`ip` varchar(15) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

and this:

CREATE TABLE `spam` (
`id` int(8) NOT NULL auto_increment,
`ip` varchar(15) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `ip` (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

4.) I chanqe in config.php on it :

AND IT NO GO.
You know where is mistake???
Amanda // June 11th 2008

Hello. I am trying to make a wedding website and want to add a guestbook feature. Everything seemed to be working okay but now I get two big errors.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/.magdalen/danfrancis/www/attempt/includes/actions.php on line 3

and

Fatal error: Call to undefined function: pagination() in /home/.magdalen/danfrancis/www/attempt/templates/skin.php on line 48

Any clue what is going on? Any help would be great. The site it’s at right now (just testing it out) is: http://www.danfrancisphotography.com/attempt/index.php

THANKS AGAIN!
Lily // June 11th 2008

Hello there.

It doesn’t work at myhomepage.. Can you please tell me, what I’ve done wrong?

- Lily.
VoiDeT // June 11th 2008

Hey people,

I don’t know why you are having these problems. It sounds like an error in the SQL. I am happy to look on your server if you provide me with the correct FTP details or cpanel details.

I have installed this script from the zip file and it works fine.

Thank you
Lily // June 12th 2008

What do you mean with the correct FTP details or cpanel details?
VoiDeT // June 12th 2008

However you upload the files to your server,
so i can see what the problem is. Because i cant replicate it
Lily // June 12th 2008

The only thing I’ve changed is the MySQL otherwise I haven’t touched anything. The same text as Amanda got I have at my page.
VoiDeT // June 12th 2008

Yep,
what sql did you change?
the connection settings?
eHobayyeb // June 22nd 2008

Amazing!

Everything works fine.
I am new PHPier and found many useful tips & tricks!

Keep it up VoiDeT, I will do all PHP tuts here.

Thanks
Mohammad
hattoon.com
HCF // July 05th 2008

Hi, awesome tutorial, shows exactly how to use the basics. 2 questions regarding your techniques:
1. What about using mySQLi instead of the usual mySQL (only PHP5, but way better), since it is faster and more secure.
2. I guess this was designed for beginner and advanced user, so it would be useful to show a lil bit of object oriented programming, since it makes the source code more accessible and php more flexible.

Awesome work, greetings from Germany.
VoiDeT // July 05th 2008

HCF!

Vielen dank für ihre nette antwort. Du hast auf Englisch geschreiben, so ich werde auf Deutsch antworten. Es freut mich so viel das du die tutorial magst. So danke noch mal. Ich hab noch nicht viele mysqli probiert. Aber ich weiß das es OOP ist. Für dieser tutorial will ich sehr einfach machen, aber vielleicht lern ich MySQLi für meine selber projekte.

Vielen dank noch mal,
Fredrik // July 07th 2008

Hi voidet,

I like the icons you use to identify the country, OS and browser. How did you do that?
VoiDeT // July 07th 2008

Firestats plugin mate
Akira // July 30th 2008

Nice guestbook. I like it.
Dan Bunyard // August 09th 2008

Awesome work! I have a guestbook on my existing site but….to put it mildly….it was hacked together to make it work. You have sure a great example here of the things you can do with PHP/MySQL. Keep up the great work, and happy coding!!
Franklyn // August 11th 2008

I havent read the code in detail but wouldnt it be better to do the error handling client side ?.
VoiDeT // August 12th 2008

Doing just client side opens you up to attacks.
This is a server side tutorial anyhow. By just doing client side error checking would be insecure and negligent.
mm // August 24th 2008

hi – this is really a great tutorial. Im very glad you did this as I was confused about how to add a honeypot.

listen, do you know how to add an ADMIN page/function to this GB, or is it already there? I was testing mine and managed to ban my own IP! I’m not sure how to manage the database at this point. Any help is appreciated.
Thanks!
VoiDeT // August 24th 2008

Hey,

Simply go into phpmyadmin and delete the entry in the banlist that is associated with your IP address

Thanks for the comments!
Yousha // September 11th 2008

This form needs CAPTCHA security image.
GL.
Pick Nick // January 30th 2009

Just want to see what kind of icon there is for Linux…
VoiDeT // January 30th 2009

google

plenty there!
testinr666 // February 16th 2009

you can delete my posts now, i needed to know if this supported line breaks, which it does.

thanks
John // February 26th 2009

Hi:
Where is the tutorial? Do I have to login to see it or I’m just blind. Thank you.
Martin Murphy // March 05th 2009

Hi m8 brilliant tutorial. Just wondering any way of putting the guestbook into the layout of your website? I cant quite get it right always goes into the wrong place :S
VoiDeT // March 05th 2009

Hey Martin!
Thanks alot for the reply!
After seeing some bad comments here from some brainless morons, you make me want to write more tutorials!
I would need to see your page however, then i can give you some feedback on why it isn’t sitting correctly on your page Thanks,
VoiDeT
Norway // March 22nd 2009

the same problem i got the error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\AppServ\www\gb\includes\actions.php on line 3
VoiDeT // March 28th 2009

Norway,
Try putting some content into your database.
I really should of built in more tests for this tutorial. However it was a tutorial, not a script give away. I will see if i get some free time soon to patch it up a bit more.
weeman // March 29th 2009

Hi
Thanks for the great tutorial!

I’m just wondering: How do I make a “new line”-character from the form show up as a new line in entries? Just like all entries in this guestbook we’re writing in now show up nicely formatted with new lines.

new line
new line
new line

instead of
new line new line new line
VoiDeT // March 30th 2009

Doesn’t the guestbook show newline characters? I mean doesn’t the text drop down a new line? New line characters are written like “\n” or a return is “\r”. Thanks for the comments Weeman!
Robi Santoso // May 12th 2009

Great tutorial, thanks a lot for your contributions. I’m newbie in php subject…
so it’s very useful for me…
meg83 // May 20th 2009

i have the same problem:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/a9819450/public_html/includes/actions.php on line 3
Thomas Cherry // May 25th 2009

I am also having a bit of trouble with setting this guestbook up. I think i’ve tracked the issue to ‘$query’, as this is the one thing that all the errors have in common. Please, i’m new to this. thanks.

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/blazesa/public_html/chamvil/guestbook/includes/actions.php on line 3

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/blazesa/public_html/chamvil/guestbook/includes/functions.php on line 12

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/blazesa/public_html/chamvil/guestbook/templates/entries.php on line 10

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/blazesa/public_html/chamvil/guestbook/templates/entries.php on line 15

the actual code, in order…..

$spamip = mysql_num_rows($query);

$rows = mysql_num_rows($query);

$rows = mysql_num_rows($query);

while($row = mysql_fetch_array($query)){
Thomas cherry // May 25th 2009

ok cool, it has to do with the tables??? when i go into myphpadmin i see that there are no tables. could this be the problem….

guestbook.sql

CREATE TABLE `entries` (
`id` int(8) NOT NULL auto_increment,
`name` varchar(255) collate latin1_general_ci NOT NULL,
`email` varchar(255) collate latin1_general_ci NOT NULL,
`website` varchar(255) collate latin1_general_ci NOT NULL,
`message` text collate latin1_general_ci NOT NULL,
`date` timestamp NOT NULL default CURRENT_TIMESTAMP,
`ip` varchar(15) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

CREATE TABLE `spam` (
`id` int(8) NOT NULL auto_increment,
`ip` varchar(15) collate latin1_general_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `ip` (`ip`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;
Thomas cherry // May 25th 2009

well what do you know. i imported the ‘guestbook.sql’ file into the database using phpmyadmin and bingo, alls well that ends well…. thanks for the tutorial. it really helped me a shit load
Ren // June 15th 2009

Well, at first I have the problem:-
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/blazesa/public_html/chamvil/guestbook/includes/actions.php on line 3

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/blazesa/public_html/chamvil/guestbook/includes/functions.php on line 12

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/blazesa/public_html/chamvil/guestbook/templates/entries.php on line 10

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/blazesa/public_html/chamvil/guestbook/templates/entries.php on line 15

But that I change my $dbname to username_guestbook and it works fine. Thanks for the great tutorial. I successfully have created mine.. =)
VoiDeT // June 15th 2009

I am getting alot of comments about mysql_num_rows errors!
People, please check that you have the correct database connection settings in order and that your tables have been set up correctly!
Tilak // June 16th 2009

Hello Friend,….
Thanx for the nice tutorial its working……
add more matter like this ….
we all are very thankful to you..
Bye
CGar // August 03rd 2009

Hello VOIDET

I spent last night going through this tutorial and it was great and informative.

One question I have is that I see your guestbook example has had some spambot action. Is this because there are new techniques that your tutorial doesn’t cover? I’d like to keep this kind of crap off my guest book if possible.

Thanks for your great tutorial and your feedback.

Best,
CGar
VoiDeT // August 03rd 2009

Hey Cgar,

This is both true and unfortunate.
I only taught one spam catching technique.
However more can be applied if need be. Generating a captcha form, or having an ip-ban with 30 day cool off period. Running known ip blocking from black lists.
The honey pot technique is just one! Surprisingly, it rejects quite alot!

Let me know if i can help you out further!
VoiDeT
Dennis van Duinen // November 12th 2009

Hi there,

A great tut! Im trying to put it on my site.

But there is one little problem. The honeypot.. when is add this link:

a new text field appears on my guestbook, while you where saying that it was hidden?

How is that possible?

Dennis
Iceaangel // December 06th 2009

Hi there. Thanks for the great tut. Sorry…forgot to read the last side, as I didn’t used all of the tut for my guestbook at the moment. So I implemented the guestbook in my website without asking you first. And…I’m not completly ready, still working on some things as the honeypot and the pagination.
Zime // December 20th 2009

Hello VOIDET,

This is the best tutorial on the internet so far!

I’m stuck at stage 7 – 10,
it looked fine until stage 7 then the succes-message never showed up.

The IP thing didin’t work for me :/ so I jumped that part and now I’m trying to get the entries to work out, but it show me this message:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\xampp\htdocs\guestbook\templates\entries.php on line 6

do you have a idea of what’s wrong?
I would be happy for any help, just contact my email!

Regards Zime
VoiDeT // December 21st 2009

@Zime:
Thanks a lot for the kind words.
That’s a shame that you can’t get the guestbook working. It looks as though your data isn’t being insert correctly. What you can do however is check your database for any records. If they aren’t in there then check what’s going on with data you’re inserting, and the insert commands.
If you do see the data in there, then check what’s happening when you try and retrieve the records.

I’m thinking i might rewrite this tutorial to use OOP with PHP5.
Or maybe save that for a whole new tutorial.
PHP Beginner // January 29th 2010

Hi
First great thanks to the author of this tutorial/workshop

Its working great. But there is only a single problem with the website links in the db entrys they re not working.

The link includes the hole file path i.e.(http://htdocs/mywebsite/www.pcsh.it) whats wrong?

thx in advance and best regards
Oli

You can follow any responses to this entry via its RSS comments feed. You may also leave a trackback by clicking this link.

Jotlab