[TUT] Multi-user login with SQL

[L?KE]

In this tutorial I will walk you through creating a simple system by which users can register, log in and view pages that are only available to registered users. This type of system would be useful in a user system or an interactive site, but this is only a basis for bigger things. I do not recommend using this in any system like that, but as an idea of how you would go about doing this. There are many things that can be added and perhaps I will bring out a more detailed tutorial in future.

A basic knowledge of php/html is required.

------------------------------------------------------------------------

Step 1
Creating the table

The first thing you will want to do is create a table in the database to store the user information. Open up a PMA (phpMyAdmin) and create a new databse, or in one you already have use the option to enter your own SQL code. Input the follow:

 
CREATE TABLE `users` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(32) NOT NULL,
`password` VARCHAR(32) NOT NULL,
PRIMARY KEY (`id`)
)

Explanation:
CREATE TABLE `users` does the obvious - it creates a table named 'users'. `id` INT(11) NOT NULL AUTO_INCREMENT creates a column in the databse which is named 'id' (INT(11) means it can only be a number, max length of 11). NOT NILL means that it cannot be left empty, and AUTO_INCREMENT means that it will go up by 1 for every addition. 'username' and 'password' are the same as 'id' (new columns), difference being that they can be 32 in length and VARCHAR means they can be a selection of VARied CHARacters. PRIMARY KEY identifies that column as the unique number for each row - so no two rows will have the same id.

NOTE: The password field is 32 characters long because when we encrypt the password using md5() it creates a hash which is 32 characters long.

------------------------------------------------------------------------

Step 2
Creating config file

Next, you'll need to make a file called config.php and use it to connect to the database. This part should need no explanation as I have commented the code enough:

 
<?php
 
//====================
 
$db_host = "localhost";            // The name of your SQL host
$db_name = "mysite";            // The name of your database
$db_user = "root";            // The database username
$db_pass = "";                // The database password
 
//====================
 
// Connecting to the databse
$con = mysql_connect( $db_host, $db_user, $db_pass );
 
// Selecting the database
$db = mysql_select_db( $db_name, $con );
 
// Testing if the connections were made:
if(!$con) { die("Could not connect to database."); }
if(!$db) { die("Could not select database."); }
 
//====================
 
?>

------------------------------------------------------------------------

Step 3
Creating the register file

This is the page on which users can register. This is going to be quite hefty, so I'll give you the code and explain it after. The comments in the code refer to which part of the explanation goes with which code. register.php:

 
<?php
 
// 1
session_start();
include("config.php");
 
// 2
if(isset($_SESSION['logged_in'])) {
 
    header("location: index.php");
 
}
 
// 3
if(isset($_POST['submit'])) {
 
    // 4
    $username = addslashes($_POST['username']);
    $password1 = $_POST['password1'];
    $password2 = $_POST['password2'];
 
    // 5
    if(empty($username) ||
       empty($password1) ||
       empty($password2)) {
 
        die("You left out a field.");
 
    } else {
 
        // 6
        if($password1 != $password2) {
 
            die("Your passwords did not match.");
 
        } else {
 
            // 7
            $junk = array('¬','`','!','\"','£','$','%','^','&','*','(',')','_','-','+','=','[',']','{','}',';',':','@','\'','#','~','<','>',',','.','/','?','\\','|',' ');
 
            $new_un = str_replace($junk,"",$username);
 
            if($new_un < $username) {
 
                die("Your username contained invalid characters.");
 
            } else {
 
                // 8
                $pw_len = strlen($password1);
 
                if($pw_len < 6) {
 
                    die("Your password is too short. It needs to be 6 or more characters.");
 
                } else {
 
                    // 9
                    $istaken = mysql_num_rows(mysql_query("SELECT * FROM `users` WHERE `username`='" . $username . "'"));
 
                    if($istaken >= 1) {
 
                        die("That username is already being used.");
 
                    } else {
 
                        // 10
                        $password = md5($password1);
 
                        $create = mysql_query("
                        INSERT INTO `users` (
                        `id`,
                        `username`,
                        `password`
                        ) VALUES (
                        NULL,
                        '" . $username . "',
                        '" . $password . "'
                        );
                        ");
 
                        // 11
                        if(!$create) {
 
                            die("There was a problem creating the user.");
 
                        } else {
 
                            die("You were registered successfully. Click <a href='login.php'>here</a> to login.");
 
                        }
 
                    }
 
                }
 
            }
 
        }
 
    }
 
} else {
 
// 12
 
?>
 
<form id="register" method="post" action="<?= $_SERVER['PHP_SELF'] ?>">
 
Username:<br />
<input type="text" name="username" /><br /><br />
 
Password:<br />
<input type="password1" name="password1" /><br /><br />
 
Confirm Password:<br />
<input type="password2" name="password2" /><br /><br />
 
<input type="submit" name="submit" value="Register" />

</form>
 
<?php
 
}
 
?>

Explanation time!

1 - here we start the session, without this you cannot detect any sessions. We also 'include' the config file we made earlier, meaning it takes all the code from it and also meaning we dont have to connect to the database over and over!

2 - here we check is the session 'logged_in' is set. If it is, we don't want them registering, they already have an account and are logged in on it.

3 - here we see if the submit button was pressed by seeing if any fields with the name 'submit' were sent to the server. If it has been we process the input, if it hasn't we display the register form.

4 - this simply retrieves the data from the fields by their 'name' on the input tag. We store the info in suitable variables. addslashes() adds backslashes to any characters that may interrupt our SQL queries.

5 - here we see if any fields were left out. As they are all necessary we error them if ANY are empty. From this point on, we use die() as it shows the message we want and kills the rest of the script, so nothing else happens.

6 - here we see if the two passwords match. The ! asks if the opposite has happened, so by using != we see if they do NOT match.

7 - this creates an array of all the characters they are NOT allowed in their username. For the quotes " and ', you need to backslash them. After the array is created, we remove all of the junk characters from the username and compare it to the original. If there is less in the new one, we know they had invalid characters in their name.

8 - here we count the length of their password and error them if it is less than 6 characters.

9 - this part queries the database to see if there is any rows with a username that matches the one submitted, and counts how many. Then we see if the amount of rows is 1 or more (shouldn't be more, but worth a check), and if it is we tell them they cannot use that name.

10 - here is the final stage. We encrypt the password using md5(), and then insert all the details into the database.

11 - After that the query is checked if it worked, and if it hasn't it errors them, and if it has it tells them so and gives them a link to log in.

12 - This part is what would be shown if the submit button was NOT pressed. It shows a form, which I shouldn't need to explain as it is simple html. The '<?= $_SERVER['PHP_SELF'] ?>' is a simpler way of writing '<?php echo($_SERVER['PHP_SELF']); ?>', $_SERVER['PHP_SELF'] being a superglobal to determine the name of script currently executing. The '<?php } ?>' at the end simply ends the initial if/else statement to see if the submit button was pressed.

------------------------------------------------------------------------

Step 4
Creating the login file

Same as for the register script, I'll give the code and explain after. Anything that is the same as the previous page I will not explain, no point doing it twice ^^. login.php:

 
<?php
 
session_start();
include("config.php");
 
if(isset($_SESSION['logged_in'])) {
 
    header("location: index.php");
 
}
 
if(isset($_POST['submit'])) {
 
    $username = addslashes($_POST['username']);
    $password = md5($_POST['password']);
 
    if(empty($username) ||
       empty($password)) {
 
        die("You left out a required field.");
 
    } else {
 
        // 1
        $isreal = mysql_num_rows(mysql_query("SELECT * FROM `users` WHERE `username`='" . $username . "' AND `password`='" . $password . "'"));
 
        if($isreal == 0) {
 
            die("There are no users with those login details.");
 
        } else {
 
            // 2
            $_SESSION['logged_in'] = 1;
            $_SESSION['username'] = $username;
 
            echo("<meta http-equiv='refresh' content='0;url=index.php'>");
 
        }
 
    }
 
} else {
 
?>
 
<form id="login" method="post" action="<?= $_SERVER['PHP_SELF'] ?>">
 
Username:<br />
<input type="text" name="username" /><br /><br />
 
Password:<br />
<input type="password" name="password" /><br /><br />
 
<input type="submit" name="submit" value="Log in" />
 
</form>
 
<?php
 
}
 
?>

Anything not commented should be described in the register script.

1 - This checks if there are any users with the inputted username AND password. If there is none, than either their username or password is incorrect.

2 - This is where they have succeded in logging in, and there are two session that are set. 'logged_in' is set to 1 (true), and 'username' is set to their username, so you can pick up who they are on the hidden pages.

------------------------------------------------------------------------

Step 5
Creating a hidden page

The idea of this was to create a system where users can register and log in. Well if they register they are going to want to be able to access a feature that non-registered users can't, so you will need to create some pages that only they can visit. This is what is necessary on one of these pages:

 
<?php
 
session_start();
 
if(!isset($_SESSION['logged_in']) ||
   !isset($_SESSION['username'])) {
 
   header("location: login.php");
 
}
 
?>
 
<!-- Page here? -->

This basically starts the session, and checks if either of the session that are set in the login script are NOT set. If they aren't it sends 'em packing. Else, it just carries on with the page.

------------------------------------------------------------------------

Step 6 (Final Step)
Creating a page to log them out

If users do happen to be logged in, they might want to log out. You can do this by creating a file named logout.php:

 
<?php
 
session_start();
session_destroy();
 
die("You have successfully logged out. <a href='login.php'>Login</a>");
 
?>

This is simple. Start session. Kill all sessions. Tell them they are logged out.

------------------------------------------------------------------------

Extra

The reason we set a session for the username was to determine who is logged in and also if you want to perform extra queries such as updating their profile, changing password etc. This tutorial does not include scripts like these but they are a possible addition for yourself to try. I will leave you with a simple string to display the user's username:

 
<?php
 
session_start();
 
if(!isset($_SESSION['logged_in']) ||
   !isset($_SESSION['username'])) {
 
   header("location: login.php");
 
}
 
$username = $_SESSION['username'];
 
?>
 
Hello, <?= $username ?>! Welcome to my website.

------------------------------------------------------------------------

That's it for now, I may add a more detailed and advance tutorial in the future, but keep in mind this is a simple tutorial, to build off of.

Farewell

Thread moved to Web designing tutorials by Cheekykarl (Forum Moderator): Nice tutorial, well done.

[VPSwow]

Wow that is amazing.
+REP

[Invent]

Not bad Let's see what other comments you get and I may just move it into the tuts forum

[Tom-743]

Excellent tut, and it's explained extremly well

Not bad Let's see what other comments you get and I may just move it into the tuts forum

Looks like its allready been moved

[L?KE]

Lol thanks for replies

[Invent]

I would've waited longer for more replies, but still, wd :]

[VPSwow]

I would've waited longer for more replies, but still, wd :]

I could tell it was Tutorial worthy .

Just read it a bit more in detail and its even better than when i scanned through.

[L?KE]

Wow, better replies than i expected.

As long as it helps

[Zaub]

When I try to register, it comes up with the message:

You left out a field.

Help pls, looks like a great starter.

[L?KE]

You need to change the register form to:

<form id="register" method="post" action="<?= $_SERVER['PHP_SELF'] ?>">
 
Username:<br />
<input type="text" name="username" /><br /><br />
 
Password:<br />
<input type="password1" name="password1" /><br /><br />
 
Confirm Password:<br />
<input type="password2" name="password2" /><br /><br />
 
<input type="submit" name="submit" value="Register" />

</form>

I forgot to name the password fields accordingly

I would appreciate if a mod could update the first post

Edited by Invent (Forum Moderator): Done