Creating a Simple Comments and Login System using MySQL and PHP--tutorial.jcwcn.com

Now that we’ve got a simple understanding of how to perform SELECT, INSERT and UPDATE MySQL queries (and if not, have a quick review from the last couple of tutorials) we can now work on building ourselves a very simple PHP application to accept user data and save it into our database. This information will then be displayed to anyone visiting the site. We’ll also take a quick look at how we can generate a script to authenticate administrative users based on a username and password combination and allow those admin users to delete unwanted comments from the database.

A simple comment system using PHP and MySQL

The first thing we’ll need to do is generate the HTML form that we’ll use to accept the user data, and include a couple of important settings which will let us access this information later, once the user has submitted the form.

First, here’s the code you’ll need to use to generate the HTML form. Save this file somewhere in your PHP directory, with the name mysql_form.php

 
A simple PHP/MySQL form
<h2>Please enter your name and a comment</h2>
<form action="form_submit.php" method="post">
Name :
<input size="32" type="text" name="username" /><br>
Comment : <textarea cols="40" rows="5" name="comment"></textarea><br>
<input type="submit" value="Send comment" />
</form>

Here, we’re using the <form> tag to generate a set of text-entry boxes where a user can enter their details, to be sent to the server for processing. We have specified that we wish the data to be sent using the POST delivery method. This simply means that the data will be sent within the request to the server, rather than attached to the query string, as would be the case if we were using the GET method.

Next, we need to create another file which will receive the request from the user, and process the data accordingly. We have specified that this file will be called form_submit.php. Create this file, and within it add the following code

<?php
include("db_connect.php");
$username = $_POST['username'];
$comment = $_POST['comment'];
if(get_magic_quotes_gpc()){
  $username = stripslashes($username);
  $comment = stripslashes($comment);
}
$username = mysql_real_escape_string($username);
$comment = mysql_real_escape_string($comment);
 
$result = mysql_query("INSERT INTO comments (name, comment) VALUES ('$username', '$comment')");
if($result == true) {
  echo "The comment was added successfully";
} else {
  echo "The comment could not be added, there was an error";
}
?>
<br/><a href="mysql_form.php">Go back to the comments page</a>

Before we go any further here, I’ll need to give you two things. Firstly, the contents of the file db_connect.php. What I’ve done here is simply place the mysql_connect() function within the file db_connect.php to save us having to type out the database connection code twice, as we’ll be adding that into our initial HTML file, mysql_form.php, in just a moment. Here is the connection file as it is set up on my computer (you may need to change the values to suit your installation of PHP)

<?php
$link = mysql_connect('localhost', 'root', 'pass'); //Connects to the database at "localhost"
mysql_select_db  ('test', $link); //Assuming you have a database named "test" set up
?>

Secondly, you’ll need the structure for the “comments” table. This should be created in the same database as is defined in the connection file above

DROP TABLE IF EXISTS `comments`;
CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `comment` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Notice how I’ve defined three columns, the first being an “id” column thay is also declared as a PRIMARY KEY, as well as having the AUTO_INCREMENT attribute set. This is very common practice in MySQL tables, and means we don’t need to concern ourselves with having comments with duplicate names, for example. These unique IDs also give us an easy way to refer to specific comments by number when administrating them at a future date, or deleting comments we don’t wish to keep.

Test Run

Try out the code I’ve included above. You should be able to see an HTML form and, when you submit that form, you should see a page saying “The comment was added successfully”. If not, then something has gone wrong and you should go back and recheck your code.

Checking for invalid data

One thing you might notice is if you try and submit the form without adding a name or a comment, then the MySQL query will still complete without a problem, and you’ll be left with a blank entry in the database. One way to get around this is to modify our code to check for such a situation and, if no data has been entered, prompt the user to go back and try again. The following change to form_submit.php will add this behaviour

<?php
include("db_connect.php");
$username = $_POST['username'];
$comment = $_POST['comment'];
if(get_magic_quotes_gpc()){
  $username = stripslashes($username);
  $comment = stripslashes($comment);
}
$username = mysql_real_escape_string($username);
$comment = mysql_real_escape_string($comment);
if(strlen($username) == 0 || strlen($comment) == 0){
  echo "You didn't enter all the data. Please go back and retry";
} else {
  $result = mysql_query("INSERT INTO comments (name, comment) VALUES ('$username', '$comment')");
  if($result == true) {
    echo "The comment was added successfully";
  } else {
    echo "The comment could not be added, there was an error";
  }
}
?>
<br/><a href="mysql_form.php">Go back to the comments page</a>

If you try again, you’ll now see that the form will not accept blank data.

There’s a great deal more to consider when accepting user input into your database, and we’re going to look at that in further detail in the next section. For now though, we’ll continue with our example, and provide our visitors with some feedback on the comments posted so far

Modifying the HTML file to display the user comments

With a little bit of modification, we can adjust the file we created earlier, mysql_form.php to display all the comments submitted so far

<?php
include("db_connect.php");
$comments = mysql_query("SELECT * FROM comments ORDER BY id DESC LIMIT 0, 10");
?>
A simple PHP/MySQL form
<h2>Please enter your name and a comment</h2>
<form action="form_submit.php" method="post">
Name :
<input size="32" type="text" name="username" /><br>
Comment : <textarea cols="40" rows="5" name="comment"></textarea><br>
<input type="submit" value="Send comment" />
</form>
<h2>Here are the comments submitted so far</h2>
 
<?php
if($row = mysql_fetch_array($comments)){
  do {
    echo "<h3>{$row['name']}</h3>";
    echo "{$row['comment']}";
  } while ($row = mysql_fetch_array($comments));
} else {
  echo "There are no comments";
}
 
?>

In our extended example, we’re making use of the ORDER BY and LIMIT keywords to show only the 10 latest comments, in order from newest to oldest.

If you try out the code above, you should see the same form on the page mysql_form.php, and underneath, a list of all the comments you’ve added. If you try adding a new one, and then return to this page, you should see it appear at the top of the list.

On the next page, we’re going to expand on our example, and see how it’s possible to add in some administration priveleges to a select group of people using the power of PHP and MySQL

Creating a Secure Login System

Now that we’ve built ourselves a nice little comment system for our site, it’s time to take a detour and look at something else. We’re going to create another system which will allow us to specify a name and password to use to authenticate ourselves to the site, to confirm that we are authorised users. We’ll then use this authorised status to let ourselves delete unwanted comments from the site, while refusing regular users this ability.

We’ll start with a new page, user_login.php (note that all these pages are being created within the same folder, in this case, the same folder that you created the form submission script)

This file will contain

<?php
session_start();
if(isset($_POST["submit"])){
  include("db_connect.php");
  $username = $_POST['username'];
  if(get_magic_quotes_gpc())
    $username = stripslashes($username);
  //apply the mysql escape function to "clean" the data
  $username = mysql_real_escape_string($username);
  $password = md5($_POST['password']);
  $result = mysql_query("SELECT COUNT(*) FROM users WHERE username = '$username' AND password = '$password'"); 
  $row = mysql_fetch_array($result);
  if($row["COUNT(*)"] == 1){
    $_SESSION["username"] = $username;
    echo "You are now logged in</br>";
  } else {
    unset ($_SESSION["username"]);
    echo "Incorrect username or password<br/>";
  }
}
?>
<form method="POST">
Name :
<input type="text" name="username"/><br>
Password :
<input type="password" name="password"/><br/>
<input name="submit" type="submit" value="Log in"/>
</form>

You will also need to create a new table in your database named users. Use the following SQL code to create this

DROP TABLE IF EXISTS 'users';
CREATE TABLE IF NOT EXISTS 'users' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'username' varchar(64) NOT NULL,
  'password' varchar(64) NOT NULL,
  PRIMARY KEY  ('id'),
  KEY 'user' ('username','password')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Notice how we’re creating an index on the “username” and “password” fields. This allows us to search quickly through the users table to find matching usernames and passwords when we try and log in.

Creating a user to authenticate

Now, try accessing the file user_login.php and see what happens when you click “Log in”. You should be presented with a screen saying “Incorrect username or password”. This is exactly what should happen, since we’ve not yet defined a user within our user table.

Using your database management tool, perform the following query. This will insert a user with the username “admin” and the password “pass”

INSERT INTO 'users' (
'username',
'password'
)
VALUES (
'admin', MD5( 'pass' )
);

You may be wondering here, what’s with the “MD5″ thing? You can see it in the file user_login.php too, at the top, where we’re grabbing the value of $password to use in the query. Also, if you check the users table now, you’ll see that our new user “admin” has been stored along the password “1a1dc91c907325c69271ddf0c944bc72″.

This is because the password is being stored in an encrypted format using the MD5 algorithm. The benefits of this are that it is virtually impossible to gain access to the user’s password by looking at the database, but it is utterly trivial to perform a check on a submitted password to see if it matches the record held in the database. In our case, when we try and log in with the password “pass”, it won’t compare the word “pass” to what’s held in the database, it will compare the value of md5(pass) to what’s held in the database.

Try it now, and see. You should now see the phrase “You are now logged in” when you enter admin/pass

The use of Sessions

I need to explain something else here. Namely, the use of the function session_start() on line 2, and the use of the array $_SESSION further down in the script.

What we are doing here is making it so that when a valid login attempt is made, the server will remember this attempt, and keep that authenticated user logged in. By setting the value of $_SESSION['username'] to the user’s login name when they succeed, and by destroying the value of $_SESSION['username'] via unset() when they fail to log in, we can ensure that only people who know a password and username combination from the database can be allowed to set the variable $_SESSION['username']

This then brings us to an interesting conclusion. Now that we have a variable which tells us, with no doubt whatsoever, whether a particular user has been authenticated or not, we can use that variable in our scripts, to do some pretty interesting things

Allowing administrators to delete comments

With a little modification, and a bit of know-how, we can now adjust our initial file, mysql_form.php to allow us to delete unwanted comments, only if we have been authenticated as an administrator. The following changes to mysql_form.php will achieve this

<?php
session_start();
//it is _vitally important_ that there is no whitespace
//in the output before session_start()
include("db_connect.php");
if($_GET['delete']=="yes" && is_numeric($_GET['id'])){
  //perform this action if we receieve a "delete" variable in the query string
  //_and_ also a numeric id representing the comment to delete
  //we know $_GET['id'] is_numeric, so we can assign it to $id here
  $id = $_GET['id'];
  if(isset($_SESSION['username']))
  {
    $result = mysql_query("DELETE FROM comments WHERE id = '$id'");
    if($result == true){
      echo "Comment $id successfully deleted";
    } else {
      echo "Comment $id could not be deleted";
    }
  } else {
    echo "You are not authorised to delete this comment";
  }
}
$comments = mysql_query("SELECT * FROM comments ORDER BY id DESC LIMIT 0, 10");
?>
A simple PHP/MySQL form
<h2>Please enter your name and a comment</h2>
<form action="form_submit.php" method="post">
Name : <input size="32" type="text" name="username" /><br>
Comment : <textarea cols="40" rows="5" name="comment"></textarea><br>
<input type="submit" value="Send comment" />
</form>
<h2>Here are the comments submitted so far</h2>
<?php
if($row = mysql_fetch_array($comments)){
  do {
    echo "<h3>{$row['name']}</h3>";
    echo "<p>{$row['comment']}</p>";
    if(isset($_SESSION['username'])){
      echo "<a href='mysql_form.php?delete=yes&id={$row["id"]}'>delete?</a>";
    }
  } while ($row = mysql_fetch_array($comments));
} else {
  echo "There are no comments";
}
?>

A Note About SQL Injection Attacks : This is a very important consideration when creating forms to accept user data, and something I’ll cover in more detail in the next tutorial. See how in the above code sections, I use the function mysql_real_escape_string()? That function modifies the submitted data in such a way so that a malicious user cannot engineer a string designed to interfere with our database. Such attacks can be devastating to a website without proper data backups in place, and you should try and familiarise yourself with ways to prevent such attacks.

Now you should see, based on whether or not you’ve logged in successfully through user_login.php, either a list of comments with a “delete?” link underneath each one, or just the comments on their own. Clicking on each “delete?” link will remove a comment from the database, and then when the page tries to display all the recent comments, it will not find that one.

If you try accessing the delete link directly when you’re not logged in (to log out, just try entering an incorrect username and password) it will fail, and you will receive a warning, e.g.

mysql_form.php?delete=yes&id=1

Your first working application!

If this is your first working PHP/MySQL application, then congratulations! You’ve already covered a great deal of the material needed to get started on building even the most complex of websites. A lot of sites around today use the exact same method for authorising users, although they implement many more security measures and have a lot more complex form submission techniques.

To see some of these security concerns, and learn how to start building foolproof websites, check out the next tutorial, and you’ll soon be on your way to building your own amazing website!

discuss this topic to forum

[Print][Close][Bookmark:digg+,reddit+,del.icio.us+,dzone+]