Creating simple Guestbook with PHP and MySql

Posted on by web help

Most of the website having guestbook in different way to track guest feedback and make their website more user friendly for their visitor. So next time user came to their  website they can feel the change what they have suggested in the guestbook.

What is the Guestbook ?

Guestbook is the form which will collect list of data from the user and store into database or any file-system. So it will helpful for the website owner to go through the suggestion of the guestbook.

Why Guestbook need to store in database instead of mail ?

Yes, you can have both in Guestbook to store data into database and mail alert for post information. When user data stored into database it will help the admin to check the data by date-wise, region-wise and repeated user.

Based on the report admin can optimizer their website, if Guestbook only enabled for mail.
Its really hard to filter the data by the above report. So it’s important to have both mail and database.

Now let’s create guestbook table with 3 pages for our Guestbook project.

  • config.php – database connection
  • post.html – html which will have list of input
  • post.php – server-side page to store the data
  •  view.php – display guestbook feedback list of data

Guestbook table

CREATE TABLE IF NOT EXISTS `guestbook` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(20) NOT NULL,
  `Email` varchar(255) NOT NULL,
  `Url` varchar(350) DEFAULT NULL,
  `Comment` varchar(450) NOT NULL,
  `IP_Address` int(11) NOT NULL,
  `Date_Time` int(11) NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `idx_Date_Time` (`Date_Time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

config.php

<?php 
//Database connection with mysqli
$mySqli = new mysqli("localhost","root","","test");

post.html

<html>
<head>
<title>Codeasearch.com - Creating simple Guestbook with PHP and MySql</title>
</head>
<body>
<table>
<form action="post.php" method="post">
<tr>
<td>Name</td>
<td><input type="text" name="txtName" maxlength="20" /></td>
</tr>
<tr>
<td>Email</td>
<td><input type="text" name="txtEmail" maxlength="255" /></td>
</tr>
<tr>
<td>Website</td>
<td><input type="text" name="txtWebsite" maxlength="350" /></td>
</tr>
<tr>
<td>Comments</td>
<td><textarea rows="6" cols="30" name="txtComment" maxlength="450"></textarea></td>
</tr>
<tr>
<td></td>
<td><input type="submit" name="btn" value="Submit" /></td>
</tr>
<tr>
<td></td>
<td><a href="view.php">View Guestbook</a></td>
</form>
</table>
</body>
</html>

post.php

<?php 
// including database connection
require_once("config.php");

// assigning post.html to $strUrl varaiable
$strUrl = 'post.html';
$strError = '';

// checks if the btn is valid or not
if(isset($_POST["btn"])) {

	// assigning the $_POST values to varaible
	$name = trim($mySqli->real_escape_string($_POST["txtName"]));
	$email = trim($mySqli->real_escape_string($_POST["txtEmail"]));
	$url = (isset($_POST["txtWebsite"])) ? trim($mySqli->real_escape_string($_POST["txtWebsite"])) : '';
	$comment = trim($mySqli->real_escape_string($_POST["txtComment"]));
	$ip = ip2long($_SERVER["REMOTE_ADDR"]);
	$datetime = strtotime("now");
	
	// if the validate false, it will concatinate the error message
	if($name=='') { $strError = '<br />Please enter the Name';}
	if(filter_var($email, FILTER_VALIDATE_EMAIL)==false) { $strError .= '<br />Please enter the valid Email';}
	if(($url!='' && filter_var($url, FILTER_VALIDATE_URL)==false)) { $strError .= '<br />Please enter the valid Url';}
	if($comment=='') { $strError .= '<br />Please enter the Comment';}
	
	// display the error messsage
	if($strError!='') {	
		echo $strError.'<br /><a href="javascript:history.back();">Back</a>';die;
	}
	
	// if there is no error message,  inserts into database
	if($strError=='') {	
		$strInsert = "insert into guestbook (Name,Email,Url,Comment,IP_Address,Date_Time) values ('".$name."','".$email."','".$url."','".$comment."',$ip,$datetime)";
		$mySqli->query($strInsert);
		
		// sending mail to admin for entry
		$strContent = "name : $name,email : $email,url : $url,comment : $comment, ip : $ip, datetime : $datetime";
		mail("admin@yourdomain.com","New entry",$strContent,"From:".$email);
	}
	
	$strUrl = 'post.html?a=y';
} 
// finally redirecting to the page which mentioned in the $strUrl varaible
header("Location: $strUrl");

view.php

<?php 
// including database connection
require_once("config.php");

// assigning the array to the $data varaible
$data= array();
$strSql = "select Name,Email,Url,Comment,IP_Address,Date_Time from guestbook order by ID desc";
$result = $mySqli->query($strSql);

// assigning records to $data varaible, after executing the query
while($rs = $result->fetch_array()) { $data[] = $rs; }

// assigning $data count to $intCnt varaible
$intCnt = count($data);

// finally printing the data in loop
?>
<table width="100%">
<tr>
<td><a href="post.php">Post</a></td>
</tr>
<?php for($iRow=0;$iRow<$intCnt;$iRow++) { ?>
<tr>
<td>
<table width="100%">
<tr>
<td><?php echo $data[$iRow]["Name"];?></td>
</tr>
<tr>
<td><?php echo $data[$iRow]["Email"];?></td>
</tr>
<tr>
<td><?php echo $data[$iRow]["Url"];?></td>
</tr>
<tr>
<td><?php echo $data[$iRow]["Comment"];?></td>
</tr>
<tr>
<td><?php echo long2ip($data[$iRow]["IP_Address"]).'        '.date("Y-m-d H:i:s",$data[$iRow]["Date_Time"]);?></td>
</tr>
<tr>
<td colspan="2" height="20"><hr size="1" /></td>
</tr>
</table>
</tr>
<?php } ?>
</table>