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 AstaHost's hosting plans. It was designed and developed using WAMP5 (WampServer Version 2.0) with settings configured to match those used by AstaHost (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 AstaHost's servers. Where possible I'll give instructions tailored to AstaHost, 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. AstaHost 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 AstaHost 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):


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


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".


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.


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".


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.


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.


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.


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.


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.


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.


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


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.