User Accounts with PHP and MySQL
Hey there! Welcome to Making the Web - my personal blog about website development. Feel free to subscribe to my RSS feed to keep up with the latest. Alternatively, subscribe by email. Hope you enjoy this article!
Having a user accounts feature is something that I'd recommend to almost any website. It is one of the best ways to make visitors really feel part of your site. And, it's also gives you an idea of how many "valuable" users you have.
In this article, I'll be telling you how to create a user accounts feature, complete with a login form, registration form and "user area." To make the system, you'll need PHP and MySQL.
Before you start coding in PHP, you'll need to create the table to store the user's details. We'll call it "users". On our server, the table is stored in the "user_accounts" database. Your MySQL table will need the following fields:
username - VARCHAR(20)
password - CHAR(32)
name - VARCHAR(100)
email - VARCHAR(255)
If you know what you are doing, feel free to add more fields, as necessary. You might also want to place a field called "id" which is primary and auto-increments, but this is not necessary with our simple system.
You might to insert a row into the table, maybe with these details:
username = demo
password = fe01ce2a7fbac8fafaed7c982a04e229
name = Name Surname
email = whatever@domain.ext
fe01ce2a7fbac8fafaed7c982a04e229 is the MD5 hash for "demo". For extra protection, MD5 encryption will be used to encrypt the password. Although a MD5 hash for a password such as "demo" can be easily solved in a matter of seconds, it can not be solved for longer, more complicated passwords so easily.
Now, we'll create a login form. We'll need a username field and a password field. Just copy this code onto the page where you want the login form to appear:
<table cellspacing="2" cellpadding="0" border="0">
<form method="post" action="login.php">
<tr>
<td>Username:</td>
<td><input type="text" name="username" /></td>
</tr>
<tr>
<td>Password:</td>
<td><input type="password" name="password" /></td>
</tr>
<tr>
<td> </td>
<td><input type="submit" name="submit" value="Login!" /></td>
</tr>
</form>
</table>
Now, you'll need to create a file called login.php. This file will take the user's details and check them with the details stored in the database. If the details are in the database, the file creates the session variables and redirects the user to the user area. Just create the file and insert this code into it:
<?php
session_start();
$user_area_location = 'account.php'; // Location of the user area
// Connect to MySQL database:
$access = mysql_connect('localhost','root','') or die ('Could not connect to database');
mysql_select_db('user_accounts',$access) or die ('Could not select table');
# #
$error = array();
if($_GET['action']) {
switch($_GET['action']) {
case 'logoff':
unset($_SESSION['loggedIn']);
array_push($error, 'You were logged off.');
break;
}
}
if(!$error) {
if(empty($_POST['username'])) { array_push($error, 'You didn\'t supply a username'); }
if(empty($_POST['password'])) { array_push($error, 'You didn\'t supply a password'); }
}
if(!$error){
$result = @mysql_query('SELECT name, email FROM `users` WHERE username = \''.mysql_real_escape_string($_POST['username']).'\' AND password = \''.mysql_real_escape_string(md5($_POST['password'])).'\'');
if($row = @mysql_fetch_row($result)) {
$_SESSION['loggedIn'] = true;
header('Location: '.$user_area_location);
die('<a href="'.$user_area_location.'">Go to your user account</a>');
}else{
array_push($error, 'The credentials you provided were not correct');
}
}
?>
<!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=iso-8859-1" />
<title>Login</title>
</head>
<body>
<table cellspacing="2" cellpadding="0" border="0">
<form method="post" action="login.php">
<?php if(isset($error) && $error) { ?>
<tr>
<td colspan="2">
<ul><?php foreach($error as $key => $value) echo '<li>'.$value.'</li>'; ?></ul>
</td>
</tr><?php } ?>
<tr>
<td>Username:</td>
<td><input type="text" name="username" /></td>
</tr>
<tr>
<td>Password:</td>
<td><input type="password" name="password" /></td>
</tr>
<tr>
<td> </td>
<td><input type="submit" name="submit" value="Login!" /></td>
</tr>
</form>
</table>
</body>
</html>
session_start() creates a session so we can register variables such as $_SESSION['loggedIn']. This allows us to use this variable in any other document which uses sessions. To change the location of the user area, change the $user_area_location variable from 'account.php' to your page.
The next part of the script is the database connection. The first parameter of mysql_connect is the server (which is usually localhost if the server is run on the same machine). The second parameter is the MySQL username. And, the third parameter is the MySQL password. You'll need to change these to the details for your MySQL server.
The first parameter of mysql_select_db is the database you want to use. On our server, it is "user_accounts".
Next, the script checks whether $_GET['action'] is set. If it is, then the user would like us to perform an action. At the moment, logoff is the only action the script can perform - but, feel free to add more if you know how to.
The next part of the script checks for any errors - blank username, blank password or incorrect credentials.
Once you have saved the file, you can (upload if nesecarry,) request it in your web browser, and login using the username "demo" and password "demo" (providing you have set this account up in the table).
You should notice that this script will take you to account.php (or another file if you changed it). As account.php doesn't exist, you'll be given a 404 error. So, that's the next step - to create account.php.
Create a blank file with whatever contents you want and call it account.php. In this file, you'll need to put this code right at the top:
<?php session_start();
if(!isset($_SESSION['loggedIn'])) { header('Location: login.php'); die('<a href="login.php">Login first!</a>'); }
?>
All this code does is start sessions, and redirect the user to login.php if they haven't logged in. You may want to add more content, even more pages, to your user account if you want. If you want a "Log off" link, insert this code where you want the link to appear:
<a href="login.php?action=logoff">Log off</a>
If you try logging in again now, you should be taken to your user accounts page. Try clicking the Log off link to see if it works. And, just to see if it is secure, request account.php without logging in - you'll notice that you are redirected to login.php.
The final thing to do is create a registration form. Start a file, call it register.php and put this code:
<?php
session_start();
// Connect to MySQL database:
$access = mysql_connect('localhost','root','') or die ('Could not connect to database');
mysql_select_db('user_accounts',$access) or die ('Could not select table');
# #
$error = array();
if(isset($_POST['username'])) {
$result = @mysql_query('SELECT username FROM `users` WHERE username = \''.mysql_real_escape_string($_POST['username']).'\'');
if($row = @mysql_fetch_row($result)) {
array_push($error, 'Your username is already being used. Please select another.');
}
$len = strlen($_POST['username']);
if($len < 3 || ($len > 20)) { array_push($error, 'Your username must be between 3 and 20 characters long.'); }
$len = strlen($_POST['password']);
if($len < 6 || ($len > 20)) { array_push($error, 'Your password must be between 6 and 20 characters long.'); }
$len = strlen($_POST['name']);
if($len > 100) { array_push($error, 'Sorry, your name can be no longer than 100 characters long.'); }
if(!$_POST['name']) { array_push($error, 'You must provide your name'); }
if(preg_match('/^[_a-z0-9-]+(\.[_a-z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$/i', $_POST['email']) == false) {
array_push($error, 'Your email address is incorrect');
}
$len = strlen($_POST['email']);
if($len > 255) { array_push($error, 'Sorry, your email address is too long.'); }
if(!$error) {
@mysql_query('INSERT INTO `users` (username, password, name, email) VALUES (\''.mysql_real_escape_string($_POST['username']).'\', \''.mysql_real_escape_string(md5($_POST['password'])).'\', \''.mysql_real_escape_string($_POST['name']).'\', \''.mysql_real_escape_string($_POST['email']).'\')');
header('Location: login.php');
die('<a href="login.php">Login</a>');
}
}
?>
<!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=iso-8859-1" />
<title>Register</title>
</head>
<body>
<table cellspacing="2" cellpadding="0" border="0">
<form method="post" action="register.php">
<?php if(isset($error) && $error) { ?>
<tr>
<td colspan="2">
<ul><?php foreach($error as $key => $value) echo '<li>'.$value.'</li>'; ?></ul>
</td>
</tr><?php } ?>
<tr>
<td>Username (3-20 chars):</td>
<td><input type="text" name="username" /></td>
</tr>
<tr>
<td>Password (6-20 chars):</td>
<td><input type="password" name="password" /></td>
</tr>
<tr>
<td>Your name:</td>
<td><input type="text" name="name" /></td>
</tr>
<tr>
<td>Email address:</td>
<td><input type="text" name="email" /></td>
</tr>
<tr>
<td> </td>
<td><input type="submit" name="submit" value="Register!" /></td>
</tr>
</form>
</table>
</body>
</html>
Again, change the parameters of mysql_connect() to the details of you MySQL server. You may also need to change the first parameter of mysql_select_db() if the users table is not stored in user_accounts. Once the visitor has signed up, they'll be taken the the login page where they can login using their newly-created credentials.
And, that's it - if you have any problems, post a comment and I'll try to help you.
Jay Garcia Said,
September 27, 2007 @ 3:59 am
Can't get this to work. I added the table to my DB correctly, configured the user data along with the username "demo" and the MD5 password.
When I try to login I get "login first" and some smiley stuff. Then I tried to register with a new user/pass and was told I didn't enter a username / password.
I have some idea of what I'm doing as I have been running a large MySQL / PHP site for many years. But this script has me scratching my head …
Thanks, Jay
Oh and BTW the email VARCHAR can't be more than a value of 255 and you suggest (320).
admin Said,
September 27, 2007 @ 7:54 am
http://dev.mysql.com/doc/refman/5.0/en/char.html < Reading that I've realised that the maximum length for VARCHAR is 255 for versions 5.0.3 and less and 65,535 for versions 5.03 and higher, which I have. I've change it to 255 just so it works with earlier versions.
Jay, you might have sessions disabled which isn't letting you log in. As for the registration problem, I have no idea why that is happening.
Ryan S Said,
December 1, 2007 @ 6:27 pm
Jay Garcia,
The reason you are getting the "login first" error message is due to a syntax error in the account.php script. The first line should be:
Brendon Said,
December 1, 2007 @ 7:01 pm
Thanks for your comment Ryan. Would you be able to tell me what the syntax error is, so I can fix it.
Edit: I think I spotted it. Was the problem with the ? in <?php ?
Daniel Said,
December 16, 2007 @ 3:46 am
I followed the Steps and for some reson it keeps comeing back wtih
The credentials you provided were not correct The username is in the database with the Password
Brendon Said,
December 16, 2007 @ 10:02 am
Have you set up the database properly and put your MySQL connection details in mysql_connect()?
Cassie Said,
January 4, 2008 @ 8:12 pm
Mine is doing the same thing as Daniel's.
Brendon Said,
January 4, 2008 @ 8:34 pm
I tested it, and it seems to be working fine. At the end of login.php, put
<?php echo mysql_error(); ?>
If there is an error, please post it here so I can fix it (unless it's a local error)
Thanks,
Brendon
Pedro Said,
January 22, 2008 @ 12:48 am
I have a problem with my login.php file.
In the login page i enter the correct details but i get this back.
"Notice: Undefined index: action in C:\Program Files\Apache Group\Apache2\htdocs\users\login.php on line 10
* The credentials you provided were not correct"
Therefore there is something wrong with this line:
"if($_GET['action']) {"
The register form works fine and adds details to the database but displays the same error when i submit the data.
Im using PHP4. Could that be the problem?
meres Said,
April 4, 2008 @ 5:21 am
I was having the same problem of :
* The credentials you provided were not correct"
On the login page.
This code works fine, the problem was that I didn't realize that passwords were being converted to md5. Try using the register page to make the entry in the database or converting the pass to md5 before entering it in with mysql.
vinh Said,
April 14, 2008 @ 9:10 am
thank you very much. i am starting php and mysql , also i don't understand a lot of . can you give me code logout complete ?
SamC Said,
November 3, 2008 @ 11:14 pm
I have to say, this was exactly what i was looking for and it was way easier than i thought to set up. It worked on the FIRST TRY. Many thanks!!!
Sam
http://www.pizap.com