Article
Managing Users with PHP Sessions and MySQL
The Signup Script
If you're used to writing database-driven Web sites, then the signup script should seem pretty straightforward. First we need to write a couple of snippets of code that will perform common functions like connecting to the database that will store the usernames and passwords for the site. These snippets will take the form of PHP functions stored in include files. We'll use one include file to house database-related functions (db.php), and another to store more general functions (common.php).
First, here's the code for db.php:
<?php // db.php
$dbhost = "localhost";
$dbuser = "user";
$dbpass = "password";
function dbConnect($db="") {
global $dbhost, $dbuser, $dbpass;
$dbcnx = @mysql_connect($dbhost, $dbuser, $dbpass)
or die("The site database appears to be down.");
if ($db!="" and !@mysql_select_db($db))
die("The site database is unavailable.");
return $dbcnx;
}
?>
The dbConnect function defined here can be called with or without an argument, because we've assigned a default value ("") to the $db argument in the function declaration. The function begins by connecting to the MySQL server using the $dbhost, $dbuser, and $dbpass variables at the top of the file (you'll need to set these to appropriate values for your server), and then if a database name was given it selects that database. Assuming everything proceeds without error, the reference to the database connection is returned.
The second include file, common.php, also contains a single function:
<?php // common.php
function error($msg) {
?>
<html>
<head>
<script language="JavaScript">
<!--
alert("<?=$msg?>");
history.back();
//-->
</script>
</head>
<body>
</body>
</html>
<?
exit;
}
?>
We'll use this error function to tell the user when he or she has done something wrong. It takes an error message as an argument, and then displays it in a JavaScript pop-up message before backing up to the previous page. This function ends the script that calls it with the exit command, so it's suitable for use when something goes wrong in one of your scripts.
With these boring details out of the way, you can now turn your attention to the signup script (signup.php). The script begins by loading the two include files we just wrote:
<?php // signup.php
include 'common.php';
include 'db.php';
This assumes that the files in question are available in the include path. Consider, for example, the following include path, which I use in my php.ini file:
include_path = ".;c:\php4\pear;d:\www\phpinclude"
On a Unix server, it might look like this:
include_path = ".:/usr/local/php/lib/php:/home/kyank/phpinclude"
In either case, you can choose to put your include files in the same directory as the file(s) that use them, or place them in a directory listed in the include path. The latter choice is a safer for files containing sensitive information like passwords, because if the PHP support in your Web server ever fails, the information in PHP files not stored below your server's Web root directory will not be exposed to prying eyes.
Next, the script checks for the presence of a $_POST['submitok'] variable, which would indicate that the signup form had been submitted. If the variable is not found, the script displays the form from the previous section for the user to fill in:
if (!isset($_POST['submitok'])):
// Display the user signup form
?>
<!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>
<title>New User Registration</title>
...
The rest of the script is responsible for processing the form submission by adding the new user to the database, so let's pause for a moment and look at the design of the database table. Here's the MySQL command to create the table:
mysql> CREATE TABLE user (
-> ID INT PRIMARY KEY AUTO_INCREMENT,
-> userid VARCHAR(100) UNIQUE NOT NULL,
-> password CHAR(16) NOT NULL,
-> fullname VARCHAR(100) NOT NULL,
-> email VARCHAR(100) NOT NULL,
-> notes TEXT);
As you can see, the table is fairly simple. In your application, you might have need of additional columns or even multiple tables, but for the purposes of this example this simple design will suffice.
So, returning to the script, when the $submit variable is detected, the script needs to prepare to insert the new entry into the database using the dbConnect function from db.php:
<?php
else:
// Process signup submission
dbConnect('sessions');
Note that I'm assuming the database containing the user table is named sessions. Be sure to change this if you're using a different database name.
The next thing the script needs to do is check that the required fields in the form are filled in. If any of the corresponding variables are found to be empty strings, the script calls the error function from common.php to tell the user what went wrong and return to the form:
if ($_POST['newid']=='' or $_POST['newname']==''
or $_POST['newemail']=='') {
error('One or more required fields were left blank.\\n'.
'Please fill them in and try again.');
}
Next, the script needs to determine whether the ID selected by the user is already present in the database, in which case the user will have to select a new ID. The handling of this query is somewhat unusual, in that we fetch a single value (the count of entries with matching user ID's) that will practically be either 0 or 1. Since there's just the one value, there's no need to loop through the result set. Instead, we use the mysql_result function to fetch the value of the first column (index 0) of the first row (index 0):
// Check for existing user with the new id
$sql = "SELECT COUNT(*) FROM user WHERE userid = '$_POST[newid]'";
$result = mysql_query($sql);
if (!$result) {
error('A database error occurred in processing your '.
'submission.\\nIf this error persists, please '.
'contact you@example.com.');
}
if (@mysql_result($result,0,0)>0) {
error('A user already exists with your chosen userid.\\n'.
'Please try another.');
}
Don't forget to change the email address in the error message!
With all the checking done, there remains only one task to be completed before the entry is added to the database. You may have noticed that the signup form didn't contain a field for the user to enter a password. This was done deliberately. Instead of letting the user set a password upfront, a common trick that many sites use is to automatically generate an initial password and send it to the email address entered by the user.
This is an effective method for checking that a valid address is entered; however, in a practical system you might end up with a lot of "bad" entries in the database. To correct this, one solution would be to add two columns to the user database. The first would hold a timestamp for when the user was created. The second would be a Boolean (true/false) value that would be initially set to false and would be set true the first time the user logged in. You could then write a script that would be run once a week as an automated task (using cron, for instance) to delete any entries for users that had registered over a week ago and had not yet logged in. In practice, these would be entries with invalid email addresses, whose passwords had never reached their users.
The task that the script must perform next, then, is to generate a password for the user. While there are many methods to generate semi-random text strings for use as passwords, the following one-liner has served me well:
$newpass = substr(md5(time()),0,6);
This works by taking the current time and performing an MD5 hash on it. This is basically a one-way cryptographic encoding into a text string, which is then chopped to 6 characters using the substr function. The result is a 6-character password that would be fairly difficult to guess.
Finally, the script performs the database insert:
$sql = "INSERT INTO user SET
userid = '$_POST[newid]',
password = PASSWORD('$newpass'),
fullname = '$_POST[newname]',
email = '$_POST[newemail]',
notes = '$_POST[newnotes]'";
if (!mysql_query($sql))
error('A database error occurred in processing your '.
'submission.\\nIf this error persists, please '.
'contact you@example.com.');
Once again, change the email address in the message to your own.
The only point worthy of mention on the above code is the use of MySQL's PASSWORD function to encode the password in the database. You could, of course, store the passwords in plain text, but users of a system often feel more comfortable when the administrator cannot read their password. Although this doesn't add much in the way of security, since presumably the administrator has full access to the database, users generally appreciate any privacy they can get.
Now that the user has been registered in the database, the script needs to send an email message indicating the password that has been assigned to the user. This is easily done using PHP's email function:
// Email the new password to the person.
$message = "G'Day!
Your personal account for the Project Web Site
has been created! To log in, proceed to the
following address:
http://www.example.com/
Your personal login ID and password are as
follows:
userid: $_POST[newid]
password: $newpass
You aren't stuck with this password! Your can
change it at any time after you have logged in.
If you have any problems, feel free to contact me at
<you@example.com>.
-Your Name
Your Site Webmaster
";
mail($_POST['newemail'],"Your Password for Your Website",
$message, "From:Your Name <you@example.com>");
Customize the message for your own purposes, and ensure that you slot in your own email address and your site's URL and name where appropriate.
Finally, the script outputs the HTML for the page that the user will see upon successful registration:
?>
<!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>
<title> Registration Complete </title>
<meta http-equiv="Content-Type"
content="text/html; charset=iso-8859-1" />
</head>
<body>
<p><strong>User registration successful!</strong></p>
<p>Your userid and password have been emailed to
<strong><?=$_POST[newemail]?></strong>, the email address
you just provided in your registration form. To log in,
click <a href="index.php">here</a> to return to the login
page, and enter your new personal userid and password.</p>
</body>
</html>
<?php
endif;
?>
Notice that this message directs the user to index.php as the "login page" for the site. This assumes that your entire site will require registration for access, with the sole exception of the signup page itself. If you only intend to protect a particular area of your site, you should probably change this message to direct the user to the password-protected area of the site. You don't need to direct them to a particular login page; as we shall see in Part 2 of this article, any protected page of the site will prompt for access if the user is not already logged in.
With the signup process ready to roll, we have laid the groundwork for a Website that requires users to register for access. Users can now create an account for themselves and have it instantly available to them, with no intervention required on the part of the site administrator. By automatically generating an initial password for each new user and emailing it to them at the address they provide during registration, you can be certain that the email address provided by each user is valid. This also opens the door to a handy "email me my forgotten password" feature if you decide it is appropriate.