MySQL Guestbook
Shobhan Challa on 2002 May 20
Shobhan Challa on 2002 May 20
Here again, today we'll discuss how to connect to Mysql database using PHP and how to store and retrieve data from MySQL database. I've choosen a simple Guestbook application as the example
.Overview
1. Creating tables in MySQL
2. Connecting to MySQL database
3. Creating the script page which stores and retrieves the data.
Creating the Database
First I'll look at setting up a simple database table. We need this to keep a record of the messages like names, email ids, and messages which the users enter.
To create a database type, this at the MySQL prompt:
Create database guestbook;
NOTE: this is optional, you might as well use another database for this example.
Now, let's create a table for the guestbook which stores the data
CREATE TABLE guest_messages (
guest_id int(4)
NOT NULL
auto_increment,
guest_name varchar(50),
guest_email varchar(50),
guest_time timestamp(14),
guest_message text,
PRIMARY KEY (guest_id)
);
Let me get down to some of the details here. We have defined 5 types of data for our sample Guestbook. The first field here is guest_id. The int(4) means guest_id is defined as a 4 digit number. Here, NOT NULL requires a little explanation. In the world of databases, when a field doesn't have data in it, it's considered as NULL. Don't confuse NULL with '0' since '0' is a character, neither is NULL an empty string ''. The auto_increment increments the subsequent records by adding '1' for each row inserted.
The fields guest_name and guest_email are defined as varchar(50). This means the name can be from 0 to 50 characters long. Next we've defined the guest_time as timestamp(14). This datatype, timestamp, is automatically handled by the database and is used to store the time values. Here 14 characters include a 4-digit year, and a series of 2-digit fields for month, day, hour, minute and second.
And, finally, guest_message is defined as text. This type allows 65,535 characters to be stored in it. The type text is a non-indexed data type. The last line defines guest_id as PRIMARY KEY. In MySQL, a table should have at least one primary key defined.
Now, let's get down to building the script which uses the table we created.
Connecting to MySQL database
We connect to MySQL in PHP using mysql_connect() function. This function establishes a connection to the MySQL server.
Here's the syntax of mysql_connect():
<?php
mysql_connect("localhost", $username, $password);
?>
mysql_connect() requires server, username and password parameters to be passed to it in order to establish a connection.
Once we connect to the MySQL server, we need to select the database we want to use with our script. We do this by using the function mysql_select_db(). It sets the given database name as the active database on the server.
<?php
$link = mysql_connect("localhost", $username, $password) or die ("Cannot connect to MySQL Server");
mysql_select_db("guestbook", $link) or die ("Cannot connect to database Guestbook);
?>
The script:
Here's the actual script which does our work. The opening page displays a form with fields for entering name, email and message. The bottom part of the page displays the messages already in the table "guest_messages".
<?php
/*
Author: Shobhan Challa
PHP/MySQL Guestbook
*/
// Stop user from submitting a blank form
if ($name and $email and $message ) {
//connect to database
mysql_connect("servername","username","password")
or die("Unable to connect to SQL server");
mysql_select_db("yourdatabase")
or die("Unable to select database");
// Insert details into the table
$query = "INSERT INTO guest_messages ";
$query .= "(guest_name, guest_email, guest_time, guest_message) ";
$query .= "values('$name','$email',NULL,'$message')";
mysql_query($query)
or die("Insert Failed!");
}
?>
<!-- Start Page -->
<HTML>
<HEAD>
<TITLE>Sign my Guestbook</TITLE>
</HEAD>
<BODY BGCOLOR="white">
<H1>Sign my Guestbook</H1>
<FORM METHOD="post" ACTION="<?$PHP_SELF;?>">
<PRE>
Your Name: <INPUT TYPE="text" NAME="name" VALUE="<?php echo $name; ?>">
Your Email: <INPUT TYPE="text" NAME="email" VALUE="<?php echo $email; ?>">
Enter Message:
<TEXTAREA NAME="message" COLS="40" ROWS="8" WRAP="Virtual">
<?php echo $message; ?>
</TEXTAREA>
<INPUT TYPE="submit" VALUE="Add">
</PRE>
</FORM>
<HR>
<?php
//Connect to the database for getting the messages
mysql_connect("servername","username","password")
or die("Unable to connect to SQL server");
mysql_select_db("yourdatabase")
or die("Unable to select database");
// count the number of entries.
$query = "SELECT COUNT(*) FROM guest_messages";
$num_count = mysql_query($query) or die("Select Failed!");
$count = mysql_fetch_array($num_count);
?>
<!-- This is where we report the total messages. -->
<P>
Total messages posted
<A HREF="<?$PHP_SELF?>?complete=1"><?php echo $count[0]; ?> messages</A>.
</P>
<?php
// This is where we decide to get all the entries or just the last 20.
$query = "SELECT * FROM guest_messages ORDER BY guest_time DESC";
if ($complete != 1)
$query .= " LIMIT 20";
$guests = mysql_query($query) or die("Select Failed!");
// loop through the records.
while ($guest = mysql_fetch_array($guests)) {
?>
<TABLE BORDER="1" WIDTH="500">
<TR><TD>
Name: <?php echo $guest['guest_name']; ?>
</TD><TD>
Email: <A HREF="mailto:
<?php echo $guest['guest_email']; ?>">
<?php echo $guest['guest_email']; ?>
</A>
</TD><TD>
<?php
// The time at which the message was added by the user.
$date_message = $guest['guest_time'];
$year = substr($date_message,0,4);
$mon = substr($date_message,4,2);
$day = substr($date_message,6,2);
$hour = substr($date_message,8,2);
$min = substr($date_message,10,2);
$sec = substr($date_message,12,2);
$orgdate = date("l F dS, Y h:i A", mktime($hour,$min,$sec,$mon,$day,$year));
?>
Date:
<?php
echo $orgdate;
?>
</TD></TR>
<TR><TD COLSPAN="3">
<?php
echo $guest['guest_message'];
?>
</TD></TR>
</TABLE>
<BR>
<?php
}
?>
</BODY>
</HTML>
In the script you may have noticed that I used mysql_connect() at two places. This is not the right way of using this, from the point of security. Instead we can include the two lines:
mysql_connect()
and
mysql_select_db()
in a separate file and then include the file. This way you can hide the username and password of your database.
You can add a simple guestbook in a matter of a few minutes. Create a database and create a table as shown above. Copy the script to a file and change the required parameters for the mysql_connect() and mysql_select_db(), your guestbook will be flying. :=)
Shobhan Challa