Tutorial How to create a PHP/MySQL login

UndeadDragon

Super Moderator
Staff member
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($result, MYSQL_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.
 

ReVolver

Moderator
Staff member
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
 

UndeadDragon

Super Moderator
Staff member
Forgot to use htmlspecialchars() when displaying the usernames :p
 

UndeadDragon

Super Moderator
Staff member
Bump. Anymore comments?
 

DDRtists

ɹoʇɐɹǝpoɯ ɹǝdns
Staff member
This:
PHP:
while($row = mysql_fetch_array($result, MYSQL_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);
 

UndeadDragon

Super Moderator
Staff member
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'];
?
 

DDRtists

ɹoʇɐɹǝpoɯ ɹǝdns
Staff member
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'];
?
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.
 
General chit-chat
Help Users
  • No one is chatting at the moment.
  • Ghan Ghan:
    Test!
  • tom_mai78101 tom_mai78101:
    I must be in a test server.
  • tom_mai78101 tom_mai78101:
    Nice, Twitter tweets embedding now works
  • Wizard Wizard:
    Yup.
  • Ghan Ghan:
    Excellent.
  • Ghan Ghan:
    @tom_mai78101 Hello there.
  • Ghan Ghan:
    Tagging works in the chat too.
  • tom_mai78101 tom_mai78101:
    @Ghan Missed it.
  • Wizard Wizard:
    Still fixing things here and there. Added widgets to the portal, will make it match the ones here on the forum index tomorrow.
  • Ghan Ghan:
    The venerable World Editor Tutorials site has been converted to HTTPS at last.
  • jonas jonas:
    cool
  • jonas jonas:
    and I can even edit my messages, nice
  • seph ir oth seph ir oth:
    GENERAL CHIT CHAT, YOU ARE A BOLD ONE
  • Ghan Ghan:
    Hello there
  • The Helper The Helper:
    this new chatbox is great and the forum software update is great too
    +1
  • The Helper The Helper:
    upgrade has fixed forum registration spam problem
  • tom_mai78101 tom_mai78101:
    Something tells me we might be able to customize the chatbox a bit, considering that there's a gap under every message.
  • Wizard Wizard:
    Going to deploy a fix soon, just had to take some time for myself this weekend.
  • Varine Varine:
    Unbelievable. Time for yourself? How dare you!
  • Wizard Wizard:
    xD
  • tom_mai78101 tom_mai78101:
    Hm, it is now harder to type anything on an Android phone. Pressing Backspace or Enter keys will dismiss the Android keyboard for some reasons.
  • tom_mai78101 tom_mai78101:
    Just noticed there's a delay of at least 2 minutes before each post. Guess I can't post Headline News quickly as I used to.
  • tom_mai78101 tom_mai78101:
    As far as I can tell, there are definitely things I need to get accustomed to first.
  • Varine Varine:
    FCC is cracking down recently

    Members online

    No members online now.

    Affiliates

    Hive Workshop
    Top