Creating A Php Login Script A thorough look at the process behind it

[Mordent 0]

Hey all, after reading through a fair number of tutorials on this subject I decided to write a pretty detailed one myself. Apologies for those who don't like my structured layout, it's just the way I do things.

 

Title: Creating a PHP Login Script

Objective: To go through a series of basic steps required to create a method of user registration, login and permission management using PHP and MySQL.

Notes: The information is designed to work fully on Xisto's hosting plans. It was designed and developed using WAMP5 (WampServer Version 2.0) with settings configured to match those used by Xisto (magic quotes and whatnot).

 

Introduction

 

Login scripts are a fairly commonly covered topic in PHP tutorials but nevertheless one that gives a fantastic initial grounding in both PHP and in manipulating databases. MySQL is the database of choice for this tutorial as it's particularly easy to use with PHP and works brilliantly on Xisto's servers. Where possible I'll give instructions tailored to Xisto, but 99% of the actual work was done on a local server so apologies if any of the steps aren't quite the same.

 

Step 1: Creating the Database

 

If you already have a database created that you can use then you can skip this step.

 

Any good login script (registration/login/permissions) needs a database to store the user information. Creating one is relatively straightforward. Navigate your cPanel until you come across MySQL Databases (or similar). In it is a very simple interface to let you create both databases and MySQL users. Normally you'll want a user for each site you create. Xisto appends whatever name you decide to give your user to your cPanel username followed by an underscore. For instance, if your cPanel username was "foo" and you named your MySQL user "bar" then the full MySQL username would be "foo_bar". There is a limit on the length of the usernames you use. I'm not entirely sure if the limit is based on your cPanel username (i.e. a character limit to "foo_bar") or just the MySQL username suffix (i.e. a character limit to "bar").

 

For the purposes of this tutorial I'm going to use "fred" as my cPanel username and "tutor" as my MySQL user, giving a full MySQL username of "fred_tutor". Naturally you should replace this with whatever you end up using throughout the rest of the tutorial. Make sure you note the password you use, as it'll be very important later on.

 

After you've created your MySQL user you need to create the database itself. Similar to the MySQL usernames, your database name will have the prefix of "foo_" (see above) to it. Choose something sensible, most likely based to fit your entire site. I'm going to be using "fred_tutorials" as mine throughout this tutorial, so obviously replace it with whatever you use.

 

You need to give the user you created permission to manipulate the database, so (using the cPanel appearance at the time of writing) scroll down to Add User To Database. Select the user and database from the dropdown menus and hit Add.

 

Step 2: Creating the Table

 

Your database is most likely currently empty (certainly so if you just created on in Step 1), so we need to add a table to it. One of the easiest ways to add one is using phpMyAdmin, which is found by navigating your cPanel (near MySQL Databases from earlier). This page lets you do all sorts of things (in fact, pretty much anything) to your MySQL databases. In Xisto you can't create or delete databases, but other than that you've got pretty much free roam.

 

So, on the left should be the name of the database you just created. Clicking on it once opens up a page with information about the structure of the database. Currently this will be empty, as you haven't put any tables in yet. Let's add one now. There are two ways of doing this: the user-friendly way or the more complicated way that lets you see what you're really doing. I'll mention the first one, but as I always use the second I'm not going to focus on it too much. Simply put, it's a way of making the functionality look prettier.

 

Next to the "Structure" tab is a tab called "SQL". This tands for "Structred Query Language", and clicking on this lets you run queries (i.e. tasks or operations) on your database. The query we're going to run is the one below (note that the query is case-insensitive, and you should be able to copy and paste pretty much from this if you need to):

 

CREATE TABLE `users` (	`id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,	`username` VARCHAR(256) NOT NULL,	`password` VARCHAR(16) NOT NULL,	`email` VARCHAR(256) NOT NULL,	`active` TINYINT(1) UNSIGNED NOT NULL default '0',	`activation_code` VARCHAR(16) NOT NULL,	`admin` TINYINT(1) UNSIGNED NOT NULL default '0',	PRIMARY KEY (`id`),	UNIQUE (`username`)) ENGINE=MyISAM

So what does all of this do? Let's take it line by line:

 

CREATE TABLE `users` (

This bit is fairly self-explanatory. We want to create a table in the current database and we want to call it "users". Tables in your databases don't have your cPanel username added as a prefix, so the table name will actually be "users".

 

`id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,

This is the first "field" of the table. Its name is "id", which we'll be using to identify individual users later on. It is a "SMALLINT", or "small integer". Because we say that the field is "UNSIGNED" that means it can range 0 to 65535. If we'd left it as it was then it would have values in the range -32768 to 32767. We could quite happily use the standard-sized "INT" (for "integer") here, but a "SMALLINT" can have one of 65536 different values, so that should be plenty for most sites. If you need more then firstly: dibs on a small percentage of your advertising income (I'm sure I deserve it! ), and secondly you can use "MEDINT" (short for "medium integer") instead. "MEDINT" will give you 16777216 different user accounts, so that should keep most people happy. You can look here for more information on different numeric data types.

 

For the purposes of this tutorial, I'll be using a small integer.

 

The next part is "NOT NULL". This means that the all entries in the "id" field have to have a value and can't just be left as NULL. NULL means that absolutely nothing has been assigned to the value. It isn't the same as an empty string. We want every user to have an id, so we make sure it's "NOT NULL".

 

Finally, there's "AUTO_INCRMENT". Rather than having to worry about assigning each new user an id ourselves we let the database do the legwork and set the field to "AUTO_INCREMENT". Each new entry in the table will get an id one greater than the last. Provided you don't start manually playing around with the tables after people have started creating accounts (i.e. adding new users directly in or playing with individual users' ids) then this takes a fair amount of hassle out of it for us.

 

Also to note, the line ends in a comma. This tells it that we're done with this field, on to the next line! Technically lines aren't even necessary, but it makes for easier reading so they are highly recommended.

 

`username` VARCHAR(256) NOT NULL,

Our next field is "username". This one is a "VARCHAR" type field, or "variable length string". This basically means you can have text in there up to a maximum length of the number in the brackets. In this case the maximum string length is 255 characters. That should be plenty for usernames, so no need to go any bigger. Again, we don't want NULL usernames, so we make sure the field is "NOT NULL".

 

`password` VARCHAR(16) NOT NULL,

This one's a bit more interesting. Clearly this is where we'll be storing the user's password, but all good sites encrypt the passwords rather than storing them directly. The number in brackets next to the "VARCHAR" effectively limits the length of the string to 16 characters, but as we'll be encrypting the passwords anyway (I'll be showing you how to do that later) this limits the encrypted string to 16 characters. Again, not really worth bothering yourself too much over, it'll make much more sense later on.

 

`email` VARCHAR(256) NOT NULL,

Knowing the user's email is very useful for many sites, not least for account activation (see later). This is simply where we'll be storing the users' email addresses.

 

`active` TINYINT(1) UNSIGNED NOT NULL default '0',

We're back to integers with this "TINYINT", the "active" flag. It's got a number in brackets again, limiting the length of it to 1 bit. As we also say that it can't be NULL, and that it's "UNSIGNED" (not that that really matters in this case) it has one of two states: 0 or 1. Also note the default state of "0", meaning that when a new user is created we don't have to worry about setting that to "0" as it will already be done for us. Default values are very useful and save both security slip ups (see later) and effort. This flag will be used to store whether the user has activated their account. If it's "0" then they haven't (meaning that they shouldn't be able to log in yet) and if it's "1" then they already have, so all is well. This field goes hand-in-hand with the next one.

 

`activation_code` VARCHAR(16) NOT NULL,

The activation code is another VARCHAR, again limited to 16 characters. You know those random activation codes you get in emails when you sign up to a new site? This will be used to store the users' one of those. If the active flag is 1 then it will never be needed again, but we need to store it somewhere while the active flag is 0.

 

`admin` TINYINT(1) UNSIGNED NOT NULL default '0',

Recognise this sort of entry? Yup, it's another flag. This one tells us whether or not the user is an admin. If 0 then the user is a standard member, if it's 1 then the user had admin rights. You can expand this field to include a number of permission settings by, for example, changing the name to "user_level" and increasing the number of possible states by increasing the length of the TINYINT. This makes for more interesting checks later on, and may be something I add to expand upon the tutorial later, but for now this is what we'll be running with.

 

PRIMARY KEY (`id`),

This line is a bit more complicated, and it helps if you know what a "primary key" is. In effect, it's a way of sorting or identifying individual entries from others, and in this case we use the user id ("id" field) to do this. It isn't vital that you understand this, but it helps.

 

UNIQUE (`username`)

We don't want users running around with the same usernames, so we make sure to set it so that each one is unique.

 

) ENGINE=MyISAM

This closes the bracket from the first line and sets the storage engine to use. In this case we'll be using MyISAM. Other options include InnoDB and range of other ones. The pros and cons of each are pretty much irrelevant at a basic level. Probably just best if you accept that you can use whichever takes your fancy until you know the ins and outs of them.

 

One thing you may have noticed as you go through this tutorial is the constant limitation of the length of the data that we want. For example, we limit the "active" field to a TINYINT of length 1 (i.e. 1 or 0). If we didn't specify the lengths of these fields then they'd use up a lot more room in the database. A TINYINT, for example, is 8 bits long (i.e. a byte), but we only need one of them. If we didn't limit its length that'd be 7 bits wasted per user, which is a fair amount considering how much you're using (12.5% of that byte). It makes sense to think a bit more about how long you actually need your database to be, especially when you have limited storage space to spare.

 

And that's our table created! We now have a database that we have access to which contains a table structured to our needs. Now that that's done we can actually start looking at the PHP and HTML code.

 

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

 

This is the intital post in my tutorial, and I'll get to writing up the rest later. I may change the odd bit as I realise that I need another field in my database etc. If you've got any comments or spot a problem with the database just give me a shout (preferably by PM) and I'll tweak it.

Edited July 21, 2008 by Mordent (see edit history)

[vujsa 0]

Very nice detailed explanation of how to get your database set up for a log in script. I think this an area that is very often overlooked when explaining how to create such a script. Most of the time the author simply gives the MySQL code and continues on with the description of the PHP that does all of th work but this is a great guide for getting ready for your script.I look forward to reading the rest of the tutorials you are planning for this systemvujsa

[khalilov 0]

Nice =)I am wondering if you can show me how to link this to html, meaning the user will enter his username/password.... in text boxes. Can you tell me how to make a textbox for username/password...Also Can you make a similar detailed guide for linking PHP/HTML with mysql. I know i can find the commands/functions on google, but i'd like to see similar details =)

[Arbitrary 0]

Ooh, quite nice. I do like the level of detail here--it helps people actually get a grasp of MySQL instead of just looking at the code and guessing that x happens and y happens. I'll be looking forward to any other tutorials on this you have to write.

 

Khalilov:

 

I am wondering if you can show me how to link this to html, meaning the user will enter his username/password.... in text boxes. Can you tell me how to make a textbox for username/password...

Let's say your form looks something like this:

<form action="index.php" method="post"></form

In this case, after the user clicks the "submit" button, it will lead to the file index.php and execute any php scripts that are located in that file. The method "post" simply refers to how the data is transferred from the form to the server--if it is "post", the data is not revealed in the url, whereas if it is request, the data is revealed in the url. A good example of a site that uses "request" methods would be Google--if you look at this url (https://www.google.com/search?q=php+hash+functions), you can see that there is data being passed with the q=php+hash+functions.

 

Meanwhile, to the actual meat of the form, say you add a few text boxes and a submit button to the form so that it now looks something like this:

<form action="index.php" method="post"><input type="text" name="username" value="" /><input type="password" name="password" value="" /><input type="submit" value="Add" /></form>

Pay special attention to the name attribute in the input tags, as it is the name that determines how to access the data.

 

If you choose to use "post" as your method, then all form data is stored in a php variable called $_POST after you submit the form. $_POST is an associative array, and its structure looks something like this:

 

array ( ["name"] => "value", ["name2"] => "value2" )

 

In order to get the post data, you would use this:

$username = $_POST['username'];$password = $_POST['username'];

This is all relatively straightforward. Then you would want to store the data you retrieve from the form into the database with MySQL. Php has a nice function for executing MySQL queries called mysql_query. So, a sample query to put the data into the database would be something like the following:

mysql_query("INSERT INTO table_name (field1, field2) VALUES ('value1', 'value2')");

In order to actually make this work for our earlier example involving inserting a username and password into the database, you'd put replace the table_name with its obvious replacement (say users), field1 and field2 with 'username' and 'password' and value1 and value2 with the variables we defined earlier from our $_POST values. So in short, like this:

mysql_query("INSERT INTO users (username, password) VALUES ('$username', '$password')");

The query is relatively obvious--you insert the values of the username and password under the fields username and password into the table users. The current query is obviously immensely insecure, and you should not use it for actual storage of passwords. Before doing anything with passwords it's best to hash them (with a hash function of sha1 or better), and it's even better with a salt (a random string hashed with the password to make it more secure). But as this is supposed to be simple, I'll leave discussion of hash functions to another thread.

 

There are other MySQL queries you can do, such as editing data or delete data from the table. Those would look something like the following:

 

For updating:

mysql_query("UPDATE table_name SET field1 = 'one' WHERE field2 = '1'");

For deleting:

mysql_query("DELETE FROM table_name WHERE field='robot';");

Wikipedia also has a nice collection of SQL queries if you'd like to check it out. (Scroll down to the bottom and you'll see a full list next to the "SQL" tab.

 

Have fun.