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.
|
| You will also need to create a new table in your database named users. Use the following SQL code to create this |
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 |
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' )
); |
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>
|
Use Session |
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'] |
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";
}
?> |
| 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 |
|
