PHP and MySQL Programming

Part 1: A Guest Book

Your tasks is to create a guest book that contains three small php-scripts as follows:

connect.php, which will set up a connection to the database.

guestbook.php, which will show all the guestbook entries and allow the user to make a new entry.

post.php, which will save new entries into your database.

The database table

To store your guestbook entries you will need to set up a new table in your database. Were only going to be storing nicknames and messages so your SQL table creation statement should be simple enough. You can use this if you would like:

CREATE TABLE guestbook (

nickname VARCHAR(32),

message TEXT

);

The create table statement tells the database that you want your new table to be called guestbook and that it should have two columns (nickname and message). In the nickname column, you want to be able to store strings, and you dont want any nicknames to exceed 32 characters. You can do this by setting the nickname type to VARCHAR (which can hold up to 256 characters) and you limit its size to 32 characters. You want the message column to store text that is longer than 256 characters, so you set the type to TEXT, which can pretty much store, an unlimited number of characters.

The Connection

For the PHP portion of this system we will start with connect.php. This script will simply establish a connection to your database. Since both your other scripts will require a database connection, we put the connection code in a file of its own, which can be included wherever a connection is needed. Thus, saving you some time.

$connection = mysql_connect('localhost', 'username', 'password');

mysql_select_db('database', $connection);

We create and store our connection link in a variable called $connection by calling mysql_connect which takes host, username and password as its arguments. Host is usually localhost if the mysql server is on the same server as the http server. You will of course have to replace this login information with your own.

mysql_select_db makes our lives a little bit easier. By calling this function, a default database will be set so we wont have to specify which database to use every time you execute a query. You should replace database with the name of your own database.

And thats it for our connection script!

The Form

Ok, its time to write some HTML!

You want your users to be able to fill in their nickname and a message into a simple form. This data will be sent over to our server once the user hits the submit-button. We can set up a form like this in HTML by using the form, input and textarea tags like this:

Nickname

Message

In this piece of code we have three input elements inside a form-tag. Our first input element is of the type text which is a simple 1-line text field. We give the field a name, which we will need later when we have to collect all the submitted data. We also give the elements ids because we want to associate the fields with its labels (making the labels clickable). We also set maxlength to 16, which will, indeed, limit the input to 16 characters (This isnt 100% secure though. We will still have to check the size later, using PHP)

The textarea works pretty much like the text input field, but it will be possible to type in more than one line. The textarea is also given an id and a label.

And finally, we have a submit element, which is basically just a button that submits the form it is nested inside. When the submit button is clicked, the browser will collect all the input data from the form and send it along to the page which is specified in the forms action attribute. In this case, this will be post.php, which is the page we will be writing next.

The method argument, in the form tag, specifies what kind of HTTP-request should be used. We will be using post which can be used to transfer larger amounts of data.

The Handling

When the user submits a message, it will be sent to post.php. To avoid security breaches, we will have to check and secure the submitted data before we try to store it in our database. This is really important, as this can easily lead to one of the most common security holes.

But lets start by collecting the input data (in post.php):

$nickname = $_POST['nickname'];

$message = $_POST['message'];

The input data we need to collect is simply stored in the $_POST array, and the field names we assigned in our form is used as keys. We now have two variables with untreated input data (nickname and message). The next step is to validate and secure them.

There are several things we have to worry about, like:

HTML-injections / XSS We dont want people to be able to post HTML and JavaScript code which can cause us any harm.

SQL-injections We dont want people to execute SQL-code on our server by entering it into one of our fields.

Size We want to set minimum and maximum lengths for both nickname and message.

If the data isnt valid, we will simply stop everything and display a simple message (by calling die(some message)). In reality you would want to something more.

Lets start by eliminating the possibilities for SQL-injections:

$nickname = mysql_real_escape_string($nickname);

$message = mysql_real_escape_string($message);

mysql_real_escape_string escapes any quotes and strips certain special characters in a given string. Any or characters will turn into \ and \ which will be treated as regular characters in SQL and thus rendering them harmless.

To get rid of HTML-injections, we could have used htmlspecialchars, which turns special characters into html entities. e.g. < turns into < The problem with this is that the strings can get longer and we want to keep them as short as possible when were storing them in our database. So were just going to use htmlspecialchars when we output our messages on our site.

We also want to limit the length of the nicknames and messages and we can accomplish this by using a little function called strlen, some if-statements and the nasty die function like so:

if (strlen($nickname) > 16 || strlen($nickname) < 3)

{

die('Sorry, your nickname has to be between 3 and 16 characters.');

}

if (strlen($message) > 512 || strlen($message) < 32)

{

die('Sorry, your message has to be between 32 and 512 characters.');

}

As you probably know by now, strlen (short for string length) returns the length (as in number of characters) of a given string. We use the less than and greater than operators along with the || (Logical OR) operator to check if the nickname or message length is out of bounds. If it is, the die function will get called and the script execution will be terminated, printing out nothing but a short little message which is passed to the die function as its argument.

Ok, now its time to insert the new data into our database:

if (!mysql_query(

"insert into guestbook (nickname, message) VALUES ('$nickname', '$message')"

))

{

die('Sorry, a database error occurred. Your message was not saved.');

}

mysql_query executes our query and returns false if an error occurs. In this case we simply kill our script with the die function if something goes wrong.

If your script is still running at this point, then the messages has been added to our database and all that is left for us to do is to print out a little message for the user:

echo 'Your message was successfully added.';

You should of course leave a longer message than that and maybe even redirect the user back to the main page, but we wont go into that in this tutorial.

Finishing up guestbook.php

The only thing left for us to do is to display the guestbook entries on our site. So go ahead and fire up the file containing your form, guesbook.php, and then add the following code wherever you want the entries to show up. (Before or after

...

)

$query = mysql_query('select * from guestbook');

while ($entry = mysql_fetch_object($query))

{

printf("

%s
- %s

",

htmlentities($entry->message),

htmlentities($entry->nickname)

);

}

?>

In this piece of code we get to meet your old friend mysql_query again, but this time we use it to get existing data from the database rather than inserting new data. The SQL query is simple enough; We just select * (all the columns) from our guestbook. If the query is successful, $query will contain a result set with all our guestbook entries.

To extract our entry data from the result set, we use the mysql_fetch_object function, which fetches and removes an item from the result set. If the result set is empty, the function will return false and thus ending our while-loop. Each entry gets stored in $entry which we use inside our loop to print out the messages.

We use printf (print formatted) to print out our entries, just to keep our code a bit cleaner. The first argument in printf is the format we want the output to be in and the rest of the argument is the data we want to display in the given format. In this case the first %s (which tells printf that the values should be displayed as a string) in our format will be replaced with our message, and the second %s will be replaced with our nickname.

When inserting the nickname and message into your database, we didnt strip any potential HTML-code and that is why we run both nickname and message through htmlentities before outputting them. htmlentities renders any html tags hamless by replacing characters such as < with < and so on.

Part 2: Chat using a Database

In this assignment you will be writing a simple chat program, where you can talk to yourself and see the message history.

Step 1: You need to write a simple web page that will use PHP to run a single or multi-user chat program. Your page will have a small text box (input box), a large text box (history box) and a button. The input box is where you will type messages and the button will append what is in the input box (provided it isn't empty) to the history box. The chat data should be stored in a database table. Design the table in any way that you want. For a multi-user chat you will need to add a box for the users name. You could also provide time stamp information. You do not need to thread the discussions. Simply show the input line by line as it as type into the input textbox. When you save the chat messages in a database, they will appear the next time you go back to the page and chat again.