sql
db.php
index.php
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.
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.
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:
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.
The first line says "While there are still rows (results) inside $result, display them as follows:".
stripslashes() removes any slashes which mysql_real_escape_string() may have inserted into submissions.
$grav_url creates our Gravatar from each users email address.
We then output (echo) each shout in a specific manner. Basically displaying the Gravatar, Name and Shout in a list we can easily style later.
The Form
The final step for this page is to include a form to the bottom of the page which users can submit posts through.
Note that we reference the $self variable to tell the form where to send it's results; and we also send via the POST method. Below the form we close off any HTML tags we opened.
Styling
Try it out! You've finished all the PHP code, and you should be able to add a new shout and see the 8 latest ones.
Code:
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`) );
Code:
<?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 ?>
Code:
<!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">
Code:
<?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>');
Code:
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 {
Otherwise, we continue:
Code:
$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>');
}
}
}
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.
Code:
$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><? target="" content="
</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 name="code" class="php">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 src="'.$grav_url.'" alt="Gravatar">
<P>'.$ename.'</P></DIV><DIV class="shout"><P>'.$epost.'</P></DIV></LI>');
}
?></UL>
stripslashes() removes any slashes which mysql_real_escape_string() may have inserted into submissions.
$grav_url creates our Gravatar from each users email address.
We then output (echo) each shout in a specific manner. Basically displaying the Gravatar, Name and Shout in a list we can easily style later.
The Form
The final step for this page is to include a form to the bottom of the page which users can submit posts through.
Note that we reference the $self variable to tell the form where to send it's results; and we also send via the POST method. Below the form we close off any HTML tags we opened.
Styling
Try it out! You've finished all the PHP code, and you should be able to add a new shout and see the 8 latest ones.
Code:
*{
margin: 0;
padding: 0;
}
body {
background: #323f66 top center url("images/back.png") no-repeat;
color: #ffffff;
font-family: Helvetica, Arial, Verdana, sans-serif;
}
h1 {
font-size: 3.5em;
letter-spacing: -1px;
background: url("images/shoutbox.png") no-repeat;
width: 303px;
margin: 0 auto;
text-indent: -9999em;
color: #33ccff;
}
h2 {
font-size: 2em;
letter-spacing: -1px;
background: url("images/shout.png") no-repeat;
width: 119px;
text-indent: -9999em;
color: #33ccff;
clear: both;
margin: 15px 0;
}
h5 a:link, h5 a:visited {
color: #ffffff;
text-decoration: none;
}
h5 a:hover, h5 a:active, h5 a:focus {
border-bottom: 1px solid #fff;
}
p {
font-size: 0.9em;
line-height: 1.3em;
font-family: Lucida Sans Unicode, Helvetica, Arial, Verdana, sans-serif;
}
p.error {
background-color: #603131;
border: 1px solid #5c2d2d;
width: 260px;
padding: 10px;
margin-bottom: 15px;
}
p.success {
background-color: #313d60;
border: 1px solid #2d395c;
width: 260px;
padding: 10px;
margin-bottom: 15px;
}
#container {
width: 664px;
margin: 20px auto;
text-align: center;
}
#boxtop {
margin: 30px auto 0px;
background: url("images/top.png") no-repeat;
width: 663px;
height: 23px;
}
#boxbot {
margin: 0px auto 30px;
background: url("images/bot.png") no-repeat;
width: 664px;
height: 25px;
}
#content {
margin: 0 auto;
width: 664px;
text-align: left;
background: url("images/bg.png") repeat-y;
padding: 15px 35px;
}
#content ul {
margin-left: 0;
margin-bottom: 15px;
}
#content ul li {
list-style: none;
clear: both;
padding-top: 30px;
}
#content ul li:first-child {
padding-top:0;
}
.meta {
width: 85px;
text-align: left;
float: left;
min-height: 110px;
font-weight: bold;
}
.meta img {
padding: 5px;
background-color: #313d60;
}
.meta p {
font-size: 0.8em;
}
.shout {
width: 500px;
float: left;
margin-left: 15px;
min-height: 110px;
padding-top: 5px;
}
form {
clear: both;
margin-top: 135px !important;
}
.fname, .femail {
width: 222px;
float: left;
}
form p {
font-weight: bold;
margin-bottom: 3px;
}
form textarea {
width: 365px;
overflow: hidden; /* removes vertical scrollbar in IE */
}
form input, form textarea {
background-color: #313d60;
border: 1px solid #2d395c;
color: #ffffff;
padding: 5px;
font-family: Lucida Sans Unicode, Helvetica, Arial, Verdana, sans-serif;
margin-bottom: 10px;
}