Tutorial How to create a PHP/MySQL login

Discussion in 'Webmaster Tutorials and Resources' started by UndeadDragon, Sep 17, 2009.

  1. UndeadDragon

    UndeadDragon Super Moderator Staff Member

    Ratings:
    +450 / 0 / -0
    How to create a PHP/MySQL Login

    Introduction
    In this tutorial, I will be explaining how to create a login system using PHP and MySQL. You will need to have (or your server needs to have) PHP installed and also a MySQL database set up.

    The SQL Query
    Firstly, you are going to need to set up your user database, ready for inputting information. I will show you the SQL and then I will explain it:

    PHP:
    USE DATABASE_NAME;
    CREATE table users (
    id integer auto_increment,
    username varchar(32),
    password varchar(32),
    permissions int(1),
    primary key (id)
    );
    Line 1: “DATABASE_NAME” needs to be replaced by the name of the database you want to run the query on.
    Line 2: “CREATE table users” simply creates a table named “users” in the database “DATABASE_NAME”.
    Line 3: “id integer auto_increment” creates a column, called id and sets it to automatically increase by 1 every time a new record is added.
    Line 4: “username varchar(32)” creates a column, called username and makes it use the varchar() type, which is limited to 32 characters.
    Line 5: See line 4.
    Line 6: “permissions int(1)” creates a column, called permissions and it uses the type int() and limits it to 1 character. Note: This is optional, but it lets you distinguish between normal members and admins, for example.
    Line 7: “primary key(id)” sets the table’s primary key (the id) to the column called “id”.

    You can run this SQL through phpMyAdmin, or you can create a query using PHP (you would also need to connect to the SQL server to be able to do this).

    The HTML Form
    This part should be simple for most people who want to create a login script, but I will show you how to do it, anyway.

    Code:
    <form action="login.php" method="post">
    <table border="0">
    <tr><td><span>Username: </span></td>
    <td><input type="text" name="user" size="30" /></td></tr>
    <tr><td><span>Password: </span></td>
    <td><input type="password" name="pass" size="30" /></td></tr>
    <tr><td><a href="register_form.php">Register</a></td>
    <td><input type="submit" value="Login" /></td></tr>
    </table>
    </form> 
    
    I used a simple table to evenly space out the different inputs and titles.

    The Login Script
    This piece of code is the part which does most of the work. It works by connecting to the database, selecting any results, which match the username and password and it checks if there is a result. If there is a result, it will set $rows to 1 (number of rows which match the conditions). If there is no matches, it will redirect back to the login page. Here is the code:

    PHP:
    <?php
    $host 
    "localhost"//DB host
    $username "DB_USERNAME";  //DB Username
    $password "DB_PASSWORD"//DB Password
    $db_name "DB_NAME"//DB Name
    $tbl_name "users"//Table name, where users are stored

    mysql_connect("$host""$username""$password")or die("cannot connect"); //Connect to DB
    mysql_select_db("$db_name")or die("cannot select DB"); //Select DB

    $username $_POST['user']; //Get username from login form
    $password $_POST['pass']; //Get password from login form

    $username stripslashes($username); //Makes string safe
    $password stripslashes($password); //Makes string safe
    $username mysql_real_escape_string($username); //Makes string safer
    $password mysql_real_escape_string($password); //Makes string safer

    $sql "SELECT * FROM $tbl_name WHERE username='$username' and password='$password'"//SQL Query
    $result mysql_query($sql); //Executes Query

    $rows mysql_num_rows($result); //Count rows selected (1 if a username/password combo can be found)

    if($rows == 1){
    session_start(); //Starts a PHP session
    $_SESSION['username'] = $username//Allows $username to be used later

    $query  "SELECT * FROM $tbl_name WHERE username='$username' and password='$password'";
    $result mysql_query($query);
    while(
    $row mysql_fetch_array($resultMYSQL_ASSOC))
       {
       
    $permissions $row['permissions']; //Gets the permissions of the user
       
    $id $row['id']; //Gets the ID of the user
       
    }
    $_SESSION['permissions'] = $permissions//Allows $permissions to be used later
    $_SESSION['id'] = $id//Allows $id to be used later
    $_SESSION['authenticated'] = 1//Allows $id to be used later

    echo("Login Succesful");//Prints success message
    }
    else 
    {
    echo(
    "Invalid Username/Password");
    }
    ?>
    I have commented almost every line of the code, so you should hopefully be able to work out how it works. It is simple really; If a username and password combo can be found in the database, it will set $rows to 1, which will set $username, $password, $permissions and $id into the session, so they can be used later (Using $var = $_SESSION[‘username’] or $var = $_SESSION[‘password’] etc).

    User Registration
    Firstly, for the registration process, I will start with a simple form:

    Code:
    <form action="register.php" method="post">
    <table border="0">
    <tr><td><span>Username: </span></td>
    <td><input type="text" name="user" size="30" /></td></tr>
    <tr><td><span>Password: </span></td>
    <td><input type="password" name="pass" size="30" /></td></tr>
    <tr><td><span>Retype password: </span></td>
    <td><input type="password" name="pass2" size="30" /></td></tr>
    <tr><td></td>
    <td><input type="submit" value="Register" /></td></tr>
    </table>
    </form>
    
    Of course, that will not do much on it’s own. We will need to create a PHP registration script, which will first check whether the 2 passwords match; If they do, the script will then check if the username/password combo already exists. If the first condition is true and the second is false, it will create a new row in the “users” table, which will add all the data in.

    PHP:
    <?php
    $host 
    "localhost"//DB host
    $username "DB_USERNAME";  //DB Username
    $password "DB_PASSWORD"//DB Password
    $db_name "DB_NAME"//DB Name
    $tbl_name "users"//Table name, where users are stored

    mysql_connect("$host""$username""$password")or die("cannot connect"); //Connect to DB
    mysql_select_db("$db_name")or die("cannot select DB"); //Select DB

    $username $_POST['user']; //Get username from registration form
    $password $_POST['pass']; //Get password from registration form
    $password2 $_POST['pass2']; //Get password 2 from registration form

    $username stripslashes($username); //Makes string safe
    $password stripslashes($password); //Makes string safe
    $password2 stripslashes($password2); //Makes string safe
    $username mysql_real_escape_string($username); //Makes string safer
    $password mysql_real_escape_string($password); //Makes string safer
    $password2 mysql_real_escape_string($password2); //Makes string safer

    if($password == $password2)
       {
         
    $sql "SELECT * FROM $tbl_name WHERE username='$username'"//SQL Query to check if username exists
         
    $result mysql_query($sql); //Executes Query
         
    $rows mysql_num_rows($result); //Count rows selected (1 if a username/password combo can be found)
         
    if($rows != 1)
         {
            
    $sql "INSERT INTO $tbl_name (username, password, permissions) VALUES ($username,$password,1)"//Insert user into database
            
    $result mysql_query($sql); //Executes Query
                
    echo("User succesfully created"); //Print success message
         
    }
         else
            {
                echo(
    "Username already exists"); //Print failure message
            
    }
       }
    else
       {
            echo(
    "The passwords do not match"); //Print failure message
       
    }
    ?>
    If everything was successful the user should be added to the database.

    Logging out
    This is probably the simplest part of the code. All you need to do is start the session and destroy it. What could be simpler?

    PHP:
    <?php
    session_start
    ();
    session_destroy();
    header("location: index.php");
    ?>
    The above code will destroy the session and redirect the user back to the index page.

    How to apply the system
    Now that your users can register and login you will want to make certain pages available to certain members. You can do this by adding this at the very top of the PHP document. (It must be before ANYTHING)

    PHP:
    <?php
    session_start
    ();
       if(
    $_SESSION['authenticated'] != 1)
       {
       echo(
    "You must be logged in");
       }
    ?>
     
    This basically reads as: If $username is equal to nothing, then print an error message. If $username has a value, it will do nothing. You can also incorporate the permissions into this:

    PHP:
    <?php
    session_start
    ();
    $permissions $_SESSION['permissions'];
       if(
    $_SESSION['authenticated'] != 1)
       {
       echo(
    "You must be logged in");
       }
       else
       {
       if(
    $permissions 3)
          {
          echo(
    "Your permissions are not high enough");
          }
       }
    ?> 
    Note: You can also replace the "echo" with a redirect.


    You can test this here: http://www.omega-designs.com/test/login_tut

    You can also try some features using basically the same code: List users by ID; List users by Username; Anyone want to request another?

    You should now have the framework to do whatever you want with this login system. It should be easy to edit, but if you need any help you can post in this thread and I will try and help you.
     
    • Like Like x 1
  2. ReVolver

    ReVolver Moderator Staff Member

    Ratings:
    +608 / 0 / -0
    Make sure you add security to your site, or when you use forms because I did a little html code on it and it worked. :p
     
  3. UndeadDragon

    UndeadDragon Super Moderator Staff Member

    Ratings:
    +450 / 0 / -0
    Forgot to use htmlspecialchars() when displaying the usernames :p
     
  4. UndeadDragon

    UndeadDragon Super Moderator Staff Member

    Ratings:
    +450 / 0 / -0
    Bump. Anymore comments?
     
  5. DDRtists

    DDRtists ɹoʇɐɹǝpoɯ ɹǝdns Staff Member

    Ratings:
    +413 / 0 / -0
    This:
    PHP:
    while($row mysql_fetch_array($resultMYSQL_ASSOC))
       {
       
    $permissions $row['permissions']; //Gets the permissions of the user
       
    $id $row['id']; //Gets the ID of the user
       
    }
    can be this:
    PHP:
    while($row mysql_fetch_array($result)) $data[] = $row;
    or if it's only one row of result data:
    PHP:
    $row mysql_fetch_array($result);
     
  6. UndeadDragon

    UndeadDragon Super Moderator Staff Member

    Ratings:
    +450 / 0 / -0
    I didn't know about those methods :p

    So, using the first method, would the code be like:

    PHP:
    while($row mysql_fetch_array($result)) $data[] = $row;  
    $permissions $data['permissions'];
    $id $data['id'];
    ?
     
  7. DDRtists

    DDRtists ɹoʇɐɹǝpoɯ ɹǝdns Staff Member

    Ratings:
    +413 / 0 / -0
    using the first method ( Meaning you have more than one row ), you would do something like:

    PHP:
    while($row mysql_fetch_array($result)) $data[] = $row;
    foreach(
    $data as $at)
    {
         echo 
    'The data is: ' $at['rowname'];
    }
    If you use it and only want to access the first row, you would use:
    PHP:
    $data[0]['rowname']
    as 0 means the first in the array and theres only one row.
     
    • Like Like x 1

Share This Page