Create a Basic Shoutbox with PHP and SQL--tutorial.jcwcn.com

Introduction

This tutorial will guide you through the process of creating a basic "shoutbox" with PHP, using a MySQL database to store the shouts - and then make it look nice with some CSS. The tutorial is aimed at designers who are confident with HTML & CSS, but want to try their hand at developing with PHP.

Following the tutorial, you should hopefully have a good understanding of the basics of using PHP to communicate with a database to send, request and receive information. We will also be including the use of Gravatars in our Shoutbox, adding that little extra oomph!

For those who haven't, I recommend you read our PHP From Scratch series in order to understand exactly what PHP is, and get a look at some of the basic syntax and how we use variables.

The sources files are also commented for those who would prefer to learn that way.

Step 1 - Getting Started

Database

Before starting, you should already have a database setup on your web server. Make sure you have the following details at hand:

Hostname (eg. localhost)
Database name
Username for database
Password

In the database, you will need to create a table named shouts with five fields:

id
name
email
post
ipaddress

To create this, run the following SQL code. You will normally run this from the SQL tab in phpMyAdmin.

view plaincopy to clipboardprint?

CREATE TABLE `shouts` (
`id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`email` VARCHAR(60) NOT NULL,
`post` TEXT NOT NULL,
`ipaddress` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`)
);

CREATE TABLE `shouts` (
  `id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `email` VARCHAR(60) NOT NULL,
  `post` TEXT NOT NULL,
  `ipaddress` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`)
);

You should receive a "Your SQL query has been executed successfully" message

The Files

We will need three files created for this project:

index.php
style.css
db.php

You will also need a folder with our required images. Grab this from the source files.

Database Connection Details

The db.php file will be used to store our database details. Open it and insert the following code:

view plaincopy to clipboardprint?

<?php
$host = 'localhost'; //usually localhost
$username = 'root'; //your username assigned to your database
$password = 'password'; //your password assigned to your user & database
$database = 'shoutbox'; //your database name
?>

<?php
$host = 'localhost'; //usually localhost
$username = 'root'; //your username assigned to your database
$password = 'password'; //your password assigned to your user & database
$database = 'shoutbox'; //your database name
?>

Step 2 - Interaction

Start your index.php file with the following code, it just begins our document and places a few sections to style later.

view plaincopy to clipboardprint?

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Shoutbox for NETTUTS by Dan Harper</TITLE>
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<div id="container">
<h1>Shoutbox<H1></H1>
<h5><a href="http://www.danharper.me" title="Dan Harper">Dan Harper </a> : <a href="http://www.nettuts.com" title="NETTUTS - Spoonfed Coding Skills">NETTUTS</a></h5>
<div id="boxtop" />
<div id="content">

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Shoutbox for NETTUTS by Dan Harper
<link rel="stylesheet" href="style.css" type="text/css" />
</head>
<body>
<div id="container">

  <h1>Shoutbox
  <h5><a href="http://www.danharper.me" title="Dan Harper">Dan Harper </a> : <a href="http://www.nettuts.com" title="NETTUTS - Spoonfed Coding Skills">NETTUTS</a></h5>

  <div id="boxtop" />
  <div id="content">

Establishing a Connection

Before we can do anything with a database, we need to connect to it. Insert the following after the previous code. It is explained below.

view plaincopy to clipboardprint?

<?php
$self = $_SERVER['PHP_SELF']; //the $self variable equals this file
$ipaddress = ("$_SERVER[REMOTE_ADDR]"); //the $ipaddress var equals users IP
include ('db.php'); // for db details
$connect = mysql_connect($host,$username,$password) or die('<P class=error>Unable to connect to the database server at this time.</P>');
mysql_select_db($database,$connect) or die('<P class=error>Unable to connect to the database at this time.</P>');

<?php
$self = $_SERVER['PHP_SELF']; //the $self variable equals this file
$ipaddress = ("$_SERVER[REMOTE_ADDR]"); //the $ipaddress var equals users IP
include ('db.php'); // for db details

$connect = mysql_connect($host,$username,$password) or die('Unable to connect to the database server at this time.');

mysql_select_db($database,$connect) or die('Unable to connect to the database at this time.');

The first two lines use a built-in PHP function to get the name of this file, and the other line to get the visitors IP address. We will use the two variables later in the tutorial.

We then include our db.php file so we can retrieve the database details you filled in. Alternatively, you could paste everything from db.php here, but it's good practice to separate the details.

$connect stores a function to use our database details in order to establish a connection with the database server. If it can't connect, it will display an error message and stop the rest of the page loading with die().

Finally, we connect to our database.

Has anything been submitted?

The next thing we will do is check whether someone has submitted a shout using the form (which we will include shortly). We check the documents POST to see if something has been submitted from a form.

view plaincopy to clipboardprint?

if(isset($_POST['send'])) {
if(empty($_POST['name']) || empty($_POST['email']) || empty($_POST['post'])) {
echo('<P class=error>You did not fill in a required field.</P>');
} else {

if(isset($_POST['send'])) {
    if(empty($_POST['name']) || empty($_POST['email']) || empty($_POST['post'])) {
        echo('You did not fill in a required field.');
    } else {

We start with our if() which checks our POST to see if an item named 'send' has been submitted. If it has we use the empty() function to make sure the 'name', 'email' and 'post' fields were filled in. If they weren't, we display an error.

Otherwise, we continue:

view plaincopy to clipboardprint?

$name = htmlspecialchars(mysql_real_escape_string($_POST['name']));
$email = htmlspecialchars(mysql_real_escape_string($_POST['email']));
$post = htmlspecialchars(mysql_real_escape_string($_POST['post']));
$sql = "INSERT INTO shouts SET name='$name', email='$email', post='$post', ipaddress='$ipaddress';";
if (@mysql_query($sql)) {
echo('<P class=success>Thanks for shouting!</P>');
} else {
echo('<P class=error>There was an unexpected error when submitting your shout.</P>');
}
}
}

$name = htmlspecialchars(mysql_real_escape_string($_POST['name'])); 
$email = htmlspecialchars(mysql_real_escape_string($_POST['email'])); 
$post = htmlspecialchars(mysql_real_escape_string($_POST['post']));

$sql = "INSERT INTO shouts SET name='$name', email='$email', post='$post', ipaddress='$ipaddress';";

    if (@mysql_query($sql)) {
        echo('Thanks for shouting!');
    } else {
        echo('There was an unexpected error when submitting your shout.');
    }
}
}

On the first three lines, we run each of our fields (name, email and post) through the htmlspecialchars() and mysql_real_escape_string() functions and place them into their own variables.

htmlspecialchars() is a function designed to prevent users from submitting HTML code. If we didn't do this, someone could put any HTML into our database which would then be executed to other users. This is especially bad if someone submitted javascript code that would transfer visitors to a malicious website!

mysql_real_escape_string() is a similar function. Except this one stops the user from submitting any sort of SQL code to the server. If we didn't do this, someone could execute code to steal, edit or erase our database!

Using our new details, we create a SQL query to insert the submitted shout into the database. In the if() tags, we execute the SQL Query. If the query was successfully executed, and the shout added to the database, we display a "Thanks for shouting!" message; otherwise we display an error.

Retrieving the Shouts

We will now retrieve the 8 latest shouts from our database to display them to the user.

view plaincopy to clipboardprint?

$query = "SELECT * FROM `shouts` ORDER BY `id` DESC LIMIT 8;";
$result = @mysql_query("$query") or die('<P class=error>There was an unexpected error grabbing shouts from the database.</P>');
?><UL><?
</pre>
<P>On the first line, we create a new SQL query to "Retrieve all fields from the 'shouts' table, order them descending by the 'ID'; but only give us the latest 8".</P>
<P>On the second line we execute the query and store it in $result. We now:</P>
<PRE class=php name="code">while ($row = mysql_fetch_array($result)) {
$ename = stripslashes($row['name']);
$eemail = stripslashes($row['email']);
$epost = stripslashes($row['post']);
$grav_url = "http://www.gravatar.com/avatar.php?gravatar_id=".md5(strtolower($eemail))."&size=70";
echo('<LI><DIV class=meta><IMG alt=Gravatar src="'.$grav_url.'">
<P>'.$ename.'</P></DIV><DIV class=shout><P>'.$epost.'</P></DIV>');
}
?></LI></PRE></UL>

$query = "SELECT * FROM `shouts` ORDER BY `id` DESC LIMIT 8;";
    
$result = @mysql_query("$query") or die('There was an unexpected error grabbing shouts from the database.');

?>
On the first line, we create a new SQL query to "Retrieve all fields from the 'shouts' table, order them descending by the 'ID'; but only give us the latest 8".
On the second line we execute the query and store it in $result. We now:
view plaincopy to clipboardprint?
while ($row = mysql_fetch_array($result)) {   
  
    $ename = stripslashes($row['name']);   
    $eemail = stripslashes($row['email']);   
    $epost = stripslashes($row['post']);   
       
    $grav_url = "http://www.gravatar.com/avatar.php?gravatar_id=".md5(strtolower($eemail))."&size=70";    
       
    echo('<LI><DIV class=meta><IMG alt=Gravatar src="'.$grav_url.'">  
    <P>'.$ename.'</P></DIV><DIV class=shout><P>'.$epost.'</P></DIV>');   
  
}   
?></LI>  
while ($row = mysql_fetch_array($result)) {

    $ename = stripslashes($row['name']);
    $eemail = stripslashes($row['email']);
    $epost = stripslashes($row['post']);
    
    $grav_url = "http://www.gravatar.com/avatar.php?gravatar_id=".md5(strtolower($eemail))."&size=70"; 
    
    echo('
%20%20%20%20'.$ename.'
'.$epost.'

The%20first%20line%20says%20"While%20there%20are%20still%20rows%20(results)%20inside%20$result,%20display%20them%20as%20follows:".

stripslashes()%20removes%20any%20slashes%20which%20mysql_real_escape_string()%20may%20have%20inserted%20into%20submissions.

$grav_url%20creates%20our%20Gravatar%20from%20each%20users%20email%20address.

We%20then%20output%20(echo)%20each%20shout%20in%20a%20specific%20manner.%20Basically%20displaying%20the%20Gravatar,%20Name%20and%20Shout%20in%20a%20list%20we%20can%20easily%20style%20later.

The%20Form

The%20final%20step%20for%20this%20page%20is%20to%20include%20a%20form%20to%20the%20bottom%20of%20the%20page%20which%20users%20can%20submit%20posts%20through.