How to create a PHP/MySQL login

[UndeadDragon]

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:

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.

<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
$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:

<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
$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
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
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
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]

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.

 

[UndeadDragon]

Forgot to use htmlspecialchars() when displaying the usernames

 

[UndeadDragon]

Bump. Anymore comments?

 

[DDRtists]

This:

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:

while($row = mysql_fetch_array($result)) $data[] = $row;

or if it's only one row of result data:

$row = mysql_fetch_array($result);

 

[UndeadDragon]

I didn't know about those methods

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

while($row = mysql_fetch_array($result)) $data[] = $row;  
$permissions = $data['permissions'];
$id = $data['id'];

?

 

[DDRtists]

I didn't know about those methods

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

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:

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:

$data[0]['rowname']

as 0 means the first in the array and theres only one row.