[Tutorial] - User Authentication

[Gaijin]

Introduction:
In this Tutorial, I will try to show you how to create a User Authentication System.
I will use MySql to store user data to and also read from it.. Therefor you need access to a MySql Database. ;)

The script that I will try to teach you with, has functions as;
Sign Up, Login/Logout, Edit Profile, View profile and Administrator functions, Add User, Edit User, Delete User.
And I also decided to put a comment script in here too, your users can write comments on each profiles...
The whole project is split into multiple files, for the sake of better maintance.

Requirements:

• PHP and MySql enabled Host
  
• Basic understainding of PHP
  
• You need to know how to create a Table in your Database
  

Files:

• config.inc.php # this file contains a code which connects to, by $config defined information.
  
• index.php # this is our main file which checks for $_GET requests and then includes the right file.
  
• signup.php # here we will handle user registrations.
  
• login.php # with this file we allow the user to login to hise/her account.
  
• admin.php # this file is same as index.php, it is only not avaliable to the public.
  
• ucp.php # this file is included when the user wants to edit his/her profile. It shows a simple User control panel.
  
• profile.php # with this file we load the requested user and show his/her profile.
  
• comment.php # this will handle the comment script, it loads given id and prints out comments for the same.
  
• admin/add.php # this file adds a user when requested.
  
• admin/edit.php # functions for editing requested user.
  
• admin/delete.php # this one is very simple, it just deletes the wanted user.
  
• list.php # this is our member list, it will load all members and print them out
  

Note: I will follow the list above and explain the files in their order..

PART 1: Creating MySql Tables (Click to View)

We will start with MySql first, open your mysql admin, phpmyadmin...
On localhosts you would access it over; http://localhost/phpmyadmin/index.php, you need WAMP, XAMPP or LAMP for this to work..

I will provide SQL command strings for you, but that still needs you to know how to execute them.

We will have only 2 Tables in our Database, 1st is our main Table called users and the 2nd is comments..
The Table users have 10 fields in it;

user_id is set as PRIMARY field and to auto increase on new entries.
user_auth is a field of the type TinyInt and has length set to only 1, the range for this field is from 0 to 9. 0 = BANNED, 1 = Normal User, 3 = Admin... you can edit it to your likes...
user_name if you don't know what this is, well it holds the name of the user
user_pass this filed's length is set to max. 32 because we will encrypt it to a MD5 hash, for security sakes...
user_mail here we store the email of the user
user_sig, user_ava, user_bio, is used to give your users a little bit uniqueness.
user_ip is where we store the users IP for other uses, like IP banning. (but I will not teach you that)
reg_date is the date on which the user has signed up or was created by a admin..

Code:

CREATE TABLE `users` (
`user_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`user_auth` TINYINT( 1 ) UNSIGNED NOT NULL ,
`user_name` VARCHAR( 25 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
`user_pass` VARCHAR( 32 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
`user_mail` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
`user_sig` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
`user_ava` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
`user_bio` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
`user_ip` VARCHAR( 15 ) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
`reg_date` DATE NOT NULL ,
PRIMARY KEY ( `user_id` ),
INDEX ( `user_id` )
) ENGINE = MYISAM ;

The Table comments have only 5 fields in it;

post_id is our main field for comments.
usr_id here we store the user_id on which profile the comment was posted
poster_id this is where we store the comment poster's id.
date and here we save the date of the posted message.
comment last on the list is the field where we save the actual message.

Code:

CREATE TABLE `comments` (
`post_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
`usr_id` INT UNSIGNED NOT NULL ,
`poster_id` INT UNSIGNED NOT NULL ,
`date` DATE NOT NULL ,
`comment` TEXT CHARACTER SET utf8 COLLATE utf8_bin NOT NULL ,
PRIMARY KEY ( `post_id` ),
INDEX ( `post_id` )
) ENGINE = MYISAM ;

So far so good, you should now use the above SQL commands to create our 2 needed fields before you continue...
You might also want to run this command, it will add an user (admin), so we test the code without having to add new users...

Code:

INSERT INTO `users` (
`user_id` ,`user_auth` ,`user_name` ,`user_pass` ,`user_mail` ,`user_sig` ,`user_ava` ,`user_bio` ,`user_ip` ,`reg_date`
)VALUES (
NULL , '2', 'Admin', '098f6bcd4621d373cade4e832627b4f6     ', 'admin@host.com', 'Test Admin', '', 'This is just a test user with admin rights.', '', ''
);

The password for this user is simply, test. ( MD5 hash: 098f6bcd4621d373cade4e832627b4f6 )

PART 2: Writing the MySql Connection Script (Click to View)

For this steps you need the 2 tables mentioned in the 1st part, if you didn't make them, please go back to part 1 and create the tables.

We will now work on the config.inc.php file, I'll show here how to use PHP to gain access to
your Database.

For more information, you will also need to llok at the PHP docs.
http://php.net/manual/en/book.mysql.php

Now that you have everything setup in the DB, you can start with the writing.
Create a folder on your testing environment, this will be the root folder of our little project.
You will save all files inside of the new folder, so I can explain it better....

Fire up your Editor of choise, HINT: notepad++, and we can start with the first file.

PHP Code:

<?php

$config = array(
    'host' => 'localhost',
    'user' => 'root',
    'pass' => '',
    'base' => 'sf_tut'
);

if(!$connect = mysql_connect($config['host'], $config['user'], $config['pass'])) {
    die("Unable to connect to the Database; ".mysql_error());
}

if(!$select = mysql_select_db($config['base'])) {
    die("Unable to select the given database; ".mysql_error());
}

function sqlClose() {
    global $connect;
    
    mysql_close($connect);
}

?>

The file config.inc.php uses the defined values of the array $config to make a connection to your databse.
Edit the values of $config so they suit your needs and your database login information..

$config['host'] can be left as it is, localhost... This also applies if you use a Real Server instead of a Virtual one.
$config['user'] here you need to write your username which you use to login to your database
$config['pass'] this is the password field, if you use one you need to write it in here
$config['base'] this member holds the Database name in which we have our 2 tables, users and comment, mine is called "sf_tut"

Once you have that setup, the first IF block executes the function mysql_connect.
If the function can't connect to the databse it will return 0 or FALSE, we use the ! character to check for the FALSE value.
That means, if the function returns FALSE, we will use the function die to output an error message and kill the script.
Our die function has another mysql function in it, mysql_error prints out a Error message, so we can see what's wrong.
This should be removed if you use it on a real site.

If the connection works out, we go to the next IF block and try to select the database for further operations.
We do that by using the function mysql_select_db, and we call the function with Database name as value, $config['base'].
If this function also returns FALSE, we kill the script again with the die function.

If the above functions execute without any errors, a function is defined called; sqlClose.
This function uses mysql_close to close the connection that was made at the top of the file.
The function is called at the end of our index.php file.

PART 3: Writing the main Index (Click to View)

At this Part we work with the file, index.php.
The file first includes the config.inc.php, then it checks if the user is logged in...

After that all, we check for a GET request, $_GET['list']..
This will check for the request and include the needed file. So if you visit http://examp.le/index.php?list, it will include the list.php file.

At the very end of the file, we execute the sqlClose function defined in the config.inc.php and close the connection.

PHP Code:

<?php

require_once "connect.inc.php";

if(isset($_COOKIE['tut_user'])) {
    echo "Welcome back {$_COOKIE['tut_user']} | <a href=\"index.php\">Home</a> | <a href=\"index.php?cp\">User CP</a> | <a href=\"index.php?logout\">Logout</a><hr />";
    
    if($_COOKIE['tut_auth'] >= 2) {
        $admin = true;
    }
}else{
    echo "<a href=\"index.php?login\">Login</a> | <a href=\"index.php?signup\">Sign Up</a><br />";
}

if(isset($_GET['list'])) {
    include "list.php";
}elseif(isset($_GET['profile'])) {
    include "profile.php";
}elseif(isset($_GET['comment'])) {
    include "comment.php";
}elseif(isset($_GET['cp'])) {
    include "ucp.php";
}elseif(isset($_GET['signup'])) {
    include "signup.php";
}elseif(isset($_GET['login'])) {
    include "login.php";
}elseif(isset($_GET['logout'])) {
    include "logout.php";
}else{
    include "list.php";
}

sqlClose();

?>

The first IF block checks if a cookie was set, upon the login process we create 2 cookies at the users PC.
1st is tut_user and the 2nd tut_auth, I'll explain that more when I get to the login.php.

If the cookie was set, we nicely welcome our user, by reading his/her name from the cookie, the ELSE case executes
when the cookie wasn't found (wasn't set). Then we print links to login or register to our guest...
We also check if tut_auth has a value over or equal to 2, if it does we define the $admin variable...

Further down in the second IF/ELSEIF block, we chech for each $_GET request, those are list, profile, comment, cp, signup and login..
Some of those GET request require a value defined, like index.php?profile=1, that would then include the profile.php file and load the user with the ID 1

PART 4: Writing the Sign Up script (Click to View)

Now we work with the signup.php file, this file check if the HTML registration FROM was sent.
Then adds the user into the database, otherwise it prints out the form...

PHP Code:

<?php

if(isset($_POST['signup'])) {
    if($_POST['user_name'] == "" || $_POST['user_pass'] == "") {
        die("Please specifiy reuired information! <a href=\"index.php?signup\">Go Back</a>");
    }
    
    $pass_hash = md5($_POST['user_pass']);
    $date = date("d-m-Y");
    
    $sql = <<<SQL
INSERT INTO `users` (
`user_id` ,`user_auth` ,`user_name` ,`user_pass` ,`user_mail` ,`user_sig` ,`user_ava` ,`user_bio` ,`user_ip` ,`reg_date`
)VALUES (
NULL , '1', '{$_POST['user_name']}', '{$pass_hash}', '{$_POST['email']}', '', '', '', '{$_SERVER['REMOTE_ADDR']}', '{$date}'
);
SQL;

    if(!$query = mysql_query($sql)) {
        die("Failed to add a user! ".mysql_error());
    }else{
        echo <<<SUCCESS
Thank you for Registering. You can now <a href="index.php?login">login</a> to your profile!
SUCCESS;
    }
}else{
    echo <<<HTML
    <form action="index.php?signup=true" method="post">
        Username: <input type="text" name="user_name" /><br />
        Password: <input type="text" name="user_pass" /><br />
        E-Mail: <input type="text" name="email" /><br />
        <input type="submit" name="signup" value="Sign Up" />
    </form>
HTML;

}

?>

In the first IF/ELSE block, we use the function isset to check if the user has pressed the submit button on the registration from..
$_POST['signup'], if it wasn't set, ELSE, we echo the html form...
If the user has pressed the button our IF block will execute and we first check, if the user hasn't left Username and/or Password field empty..
If s/he did, we kill the scipt using the die function and print a error message.

Next we define $pass_hash, which holds the MD5 encrypted value of the given password, and also define the date the user has joined our database.
The variable $sql holds our SQL command to execute, we use the given form inputs and pass them into our SQL command...

Next step is to execute the command, using the mysql_query function...
If the function executes without any errors, we print out a Message (<<<SUCCESS), otherwise we kill the script again and print out the error message.

PART 5: Allowing your users to Login (Click to View)

Now we need to give our users the abillity to login, so we write our login.php file.
This file works the same way as signup.php, it checks if the form was submitted and then executes the functions...
If the form was not submitted it will pprint the form to the user.

PHP Code:

<?php

if(isset($_POST['login'])) {
    if($_POST['user_name'] == "" || $_POST['user_pass'] == "") {
        die("Please specifiy reuired information! <a href=\"index.php?signup\">Go Back</a>");
    }
    
    $pass_hash = md5($_POST['user_pass']);
    $date = date("d-m-Y");
    
    $sql = <<<SQL
SELECT `user_id`, `user_auth`, `user_name`, `user_pass` FROM `users` WHERE `user_name` = '{$_POST['user_name']}' LIMIT 1
SQL;

    if(!$query = mysql_query($sql)) {
        die("There was an error while trying to log you in! ".mysql_error()." <br /> Go back and try again!");
    }
    
    if(mysql_num_rows($query) <= 0) {
        die("There are no users with this name");
    }
    
    $user = mysql_fetch_array($query);
    mysql_free_result($query);
    
    if($_POST['user_name'] == $user['user_name'] && md5($_POST['user_pass']) == $user['user_pass']) {
        setcookie("tut_user", $user['user_name'], time()+3600);
        setcookie("tut_auth", $user['user_auth'], time()+3600);
        
        echo "You are logged in, you can now visit your <a href=\"index.php?profile={$user['user_id']}\">profile</a>.";
    }else{
        die("Login Error");
    }
    
}else{
    echo <<<HTML
    <form action="index.php?login=true" method="post">
        Username: <input type="text" name="user_name" /><br />
        Password: <input type="text" name="user_pass" /><br />
        <input type="submit" name="login" value="Login" />
    </form>
HTML;

}

?>

In this file we also first use IF to check if the submit button was pressed, if it wasn't we show the forum where the user can
input his/her login details.
If the form was sent, we again check for empty fields and kill the script if there are any..

If all goes well, we define $sql command to run, again this command is executed with the function mysql_query...
The command will select fields, user_id, user_auth, user_name and user_pass from the table users from the entry with the inputed name... $_POST['user_name']
If the function fails to execute the command we kill the script and print out the error message.

Then we use mysql_num_rows to get the number of affected rows, if the number is 0 or under then we print out an error message and kill the script...

Otherwise we use a new function called; mysql_fetch_array. With this function we fetch the data that was requested with mysql_query.
And then in the next IF block, we use Form submitted values to compare it with our stored values $_POST == $user..
We use the inputed name and md5 encrypted password to compare it with requested query result.
If the username and password match the stored values, we use the function setcookie.
We set 2 cookies, tut_user contain the username and tut_auth the users authorization level.
The cookies are set to expire after 1 Hour. Note time()+3600
And finally show a message to our user, to let him/her know that they are now logged in.

PART 6: Writing the administration Area (Click to View)

Now that we have our script going on, let's make things simple and write an administration area.
We now create the file, admin.php, this file as I've said it works the same way as index.php.

PHP Code:

<?php

require_once "connect.inc.php";

if(isset($_COOKIE['tut_user']) && isset($_COOKIE['tut_auth'])) {
    if($_COOKIE['tut_auth'] < 2) {
        die("You don't have the premission to view this page");
    }
}else{
    die("You don't have the premission to view this page");
}

if(isset($_GET['list'])) {
    include "list.php";
}elseif(isset($_GET['edit'])) {
    include "admin/edit.php";
}elseif(isset($_GET['add'])) {
    include "admin/add.php";
}elseif(isset($_GET['delete'])) {
    include "admin/delete.php";
}else{
    include "list.php";
}

sqlClose();

?>

The main difference between our admin.php and index.php is that this time, we check if both of our Cookies were set, else we kill the script.
If both cookies were set, we check if the value of cookie tut_auth is under 2, the Admin level... every user with auth under 2 will
not be able to view this page.

The rest of the file is same as index.php, only that this time we chack for list, add, edit and delete GET requests..
edit and delete require a ID defined, admin.php?delete=3

At this time, you are now able to login and try to access the admin area...
I will show you more in the next part.

PART 7: User Control panel (Click to View)

In this part we will play with the file, ucp.php..
This file loads the requested user ($_GET['profile']) and loads his/her profile...

PHP Code:

<?php

if(!isset($_COOKIE['tut_user'])) {
    die("You need to login to be able to access this page!");
}

$sql = "SELECT * FROM `users` WHERE `user_name` = '{$_COOKIE['tut_user']}'";
if(!$query = mysql_query($sql)) {
    die("Failed to executed SQL command! ".mysql_error());
}

$user = mysql_fetch_array($query);
mysql_free_result($query);

if(isset($_POST['edit'])) {
    
    $sql = <<<SQL
UPDATE `users` SET `user_name` = '{$_POST['user_name']}',
`user_pass` = '{$_POST['user_pass']}',
`user_mail` = '{$_POST['user_mail']}',
`user_ava` = '{$_POST['user_ava']}',
`user_sig` = '{$_POST['user_sig']}',
`user_bio` = '{$_POST['user_bio']}' WHERE `user_id` = {$_GET['cp']} LIMIT 1 ;    
SQL;

    if(!$query = mysql_query($sql)) {
        die("Unable to edit the user! ".mysql_error());
    }else{
        echo "Your profile has been updated. <a href=\"index.php\">Home</a>";
    }

}else{
    echo <<<FORM
    <form action="index.php?cp={$user['user_id']}" method="post">
        First Name: <input type="text" name="user_name" value="{$user['user_name']}" /><br />
        Password: <input type="text" name="user_pass" /><br />
        Email: <input type="text" name="user_mail" /><br />
        Avatar: <input type="text" name="user_ava" /><br />
        Signature: <input type="text" name="user_sig" /><br />
        Bio: <textarea name="user_bio"></textarea><br />
        <input type="submit" name="edit" value="Edit" />
    </form>
FORM;
}

?>

This time we check if the cookie wasn't set and notify the user that s/he needs to login in order to view the page.
Next we define a SQL command wich will select entries with the value of the Cookie tut_user...
The use the mysql_fetch_array to read the data from executed query and finally free up the used memory with the function, mysql_free_result.

The next IF block, checks if the user has submitted the Edit form, if not it will print the form to the user.
Otherwise it will Upadte the user, with requested user_id ($_GET['cp']).

PART 8: Profile Page (Click to View)

Now, profile.php..
This file loads the requested user ($_GET['profile']) and loads his/her profile...

PHP Code:

<?php

if($_GET['profile'] == "") {
    die("Invalid user id!");
}
    
$sql = <<<SQL
SELECT * FROM `users` WHERE `user_id` = {$_GET['profile']}
SQL;

if(!$query = mysql_query($sql)) {
    die("Unable to load the user! ".mysql_error());
}
    
$user = mysql_fetch_array($query);
mysql_free_result($query);

echo <<<PROFILE
<div style="font-size:26px;font-weight:bold;">
    {$user['user_name']} {$user['user_mail']}
</div>

<div>
Joined: {$user['reg_date']}
<hr />
Signature:
{$user['user_sig']}
</div>
PROFILE;

$sql = "SELECT * FROM `comments` WHERE `user_id` = '{$_GET['profile']}'";

if(!$query = mysql_query($sql)) {
    echo "Could not load Comments for this user!";
}else{
    while($row = mysql_fetch_array($query)) {
        echo <<<COMMENT
        <div>
            <div>Posted by <a href="index.php?profile={$row['poster_id']}">This User</a> on {$row['date']}</div>
            <div>{$row['comment']}</div>
            <hr />
        </div>
COMMENT;
    }
}

echo <<<FORM
<form action="index.php?comment={$user['user_id']}" method="post">
Comment: <textarea name="comment"></textarea><br />
<input type="submit" name="post" value="Post" />
</form>
FORM;

?>

First we use IF to check if a valid GET request was sent, otherwise kill the script... again!
Next parts of the code should look familiar to you, as we are using the same function like in the login.php
Note that you sould always call mysql_free_result when you run functions like mysql_fetch_array.

Further down, starting at the lines...
[ode]
PROFILE;

$sql =.....
[/code]

Here we prepare next sql command which will load the comments for the requested user, then if our function mysql_query executes the command with success.
We run a while loop, the loop exectues the mysql_fetch_array function every new round and so it loads all entries one by one...

At the end of the file, we print out a Form to submit comments...

PART 9: The Comment Script (Click to View)

Let us now work with the, comment.php file.
This file is pretty small, it just adds the new post to the database.

PHP Code:

<?php

if($_GET['comment'] == "") {
    die("Wrong user ID! ".mysql_error());
}

if(isset($_POST['comment'])) {
    $date = date("Y-m-d");
    $message = htmlspecialchars($_POST['comment']);

    $sql = <<<SQL
    INSERT INTO `comments` (`post_id`, `usr_id`, `poster_id`, `date`, `comment`) 
    VALUES 
    (NULL, '{$_GET['comment']}', '{$_COOKIE['tut_uid']}', '{$date}', '{$message}');
SQL;

    if(!$query = mysql_query($sql)) {
        die("There was an error while posting your comment; ".mysql_error());
    }else{
        echo "Comment Added";
    }
}

?>

We first check if the request has a valid value, if not wie kill the script.

The next step should also tell you what we are doing...
We again check if a Form was submitted only this time we don't do anything if the form wasn't sent.
Because we don't expect calls on this page other than by submitting a Form...

We define the $date and we use the function htmlspecialchars to clean the comment from malicious input..
Then we pass our vriables to the SQL command and use mysql_query to execute it....
If mysql_query returns FALSE we print out a error message and kill the script, otherwise we notify the user that his/her comment was posted.

PART 10: Giving the Admin some Powers (Click to View)

Now we will talk about the admin folder, this folder will contain 3 files;
edit.php, add.php, delete.php, all those files are only accessable by users with user_auth level set to 2 or Higher.
But that only works as long as you user don't go directly to this file, you can stop that too if you again check if the user has right type of premissions.
For the sake of learning I'll leave that up to you... We did already checked this and you should have learned it somehwere above ;)

PHP Code:

<?php

$sql = "SELECT * FROM `users` WHERE `user_id` = '{$_GET['edit']}'";
if(!$query = mysql_query($sql)) {
    die("Failed to executed SQL command! ".mysql_error());
}

$user = mysql_fetch_array($query);
mysql_free_result($query);

if(isset($_POST['edit'])) {

    $pass_hash = md5($_POST['user_pass']);
    
    $sql = <<<SQL
UPDATE `users` SET `user_name` = '{$_POST['user_name']}',
`user_pass` = '{$pass_hash}',
`user_auth` = '{$_POST['user_auth']}',
`user_mail` = '{$_POST['user_mail']}',
`user_ava` = '{$_POST['user_ava']}',
`user_sig` = '{$_POST['user_sig']}',
`user_bio` = '{$_POST['user_bio']}',
`user_ip` = '{$_POST['user_ip']}',
`reg_date` = '{$_POST['reg_date']}' WHERE `user_id` = {$_GET['edit']} LIMIT 1 ;    
SQL;

    if(!$query = mysql_query($sql)) {
        die("Unable to edit the user! ".mysql_error());
    }else{
        echo "Your profile has been updated. <a href=\"index.php\">Home</a>";
    }

}else{
    echo <<<FORM
    <form action="index.php?cp={$user['user_id']}" method="post">
        Auth Level: <input type="text" name="user_auth" value="{$user['user_auth']}" /><br />
        Username: <input type="text" name="user_name" value="{$user['user_name']}" /><br />
        Password: <input type="text" name="user_pass" value="{$user['user_pass']}" /><br />
        Email: <input type="text" name="user_mail" value="{$user['user_mail']}" /><br />
        IP: <input type="text" name="user_ip" value="{$user['user_ip']}" /><br />
        Join Date: <input type="text" name="reg_date" value="{$user['reg_date']}" /><br />
        Avatar: <input type="text" name="user_ava" value="{$user['user_ava']}" /><br />
        Signature: <input type="text" name="user_sig" value="{$user['user_sig']}" /><br />
        Bio: <textarea name="user_bio">{$user['user_bio']}</textarea><br />
        <input type="submit" name="edit" value="Edit" />
    </form>
FORM;
}

?>

This file works same as our script in the ucp.php, the difference is that the admin can edit all fileds on a user.

All the functions I'm using should now be really familiar to you, but nay ways...
We first setup the SQL command to load the requested user and it's stored values, then Place the values into $user variable and free up the used space. (mysql_free_result)

Once we have that, we again check what to do.
If the form was sent we execute our UPDATE sql command and if not we print the form to our user.

The next file is, add.php...
This file works exact the same as signup.php, only that you now have more fields to input values in it, since you're an admin.

PHP Code:

<?php

if(isset($_POST['add'])) {
    $pass_hash = md5($_POST['user_pass']);
    $date = date("Y-m-d");
    
    $sql = <<<SQL
INSERT INTO `users` (
`user_id` ,`user_auth` ,`user_name` ,`user_pass` ,`user_mail` ,`user_sig` ,`user_ava` ,`user_bio` ,`user_ip` ,`reg_date`
)VALUES (
NULL , '1', '{$_POST['user_name']}', '{$pass_hash}', '{$_POST['email']}', '', '', '', '{$_SERVER['REMOTE_ADDR']}', '{$date}'
);
SQL;

    if(!$query = mysql_query($sql)) {
        die("Failed to add a user! ".mysql_error());
    }else{
        echo <<<SUCCESS
User have been added! <a href="index.php">Home</a>
SUCCESS;
    }
}else{
    echo <<<HTML
    <form action="admin.php?edit={$user['user_id']}" method="post">
        Auth Level: <input type="text" name="user_auth" value="" /><br />
        Username: <input type="text" name="user_name" value="" /><br />
        Password: <input type="text" name="user_pass" value="" /><br />
        Email: <input type="text" name="user_mail" value="" /><br />
        IP: <input type="text" name="user_ip" value="" /><br />
        Join Date: <input type="text" name="reg_date" value="" /><br />
        Avatar: <input type="text" name="user_ava" value="" /><br />
        Signature: <input type="text" name="user_sig" value="" /><br />
        Bio: <textarea name="user_bio"></textarea><br />
        <input type="submit" name="edit" value="Edit" />
    </form>
HTML;

}

?>

If you look at this code, you'll see that it looks like signup.php and it does work exactly the same.
Refer to PART 4

The last file in our admin directory is the easiest one.

PHP Code:

<?php

if(!$_GET['delete'] == "") {
    die("Invalid User ID");
}

$sql = "DELETE FROM `users` WHERE `user_id` = {$_GET['delete']} LIMIT 1";

if(!$query = mysql_query($sql)) {
    die("There was an error while trying to delet a User; ".mysql_error());
}else{
    echo "The User have been deleted. <a href=\"index.php\">Home</a>";
}

?>

We use DELETE FROM sql command and execute it using the function, mysql_query, again
On success we print out a message and a link back to the index.php..

PART 11: Creating a Member List (Click to View)

For this tutorials last step, we talk about list.php
This file simply loads all entries in the users table and prints them out..

PHP Code:

<?php

$sql = "SELECT * FROM `users`";
if(!$query = mysql_query($sql)) {
    die("Could not fetch user table! ".mysql_error());
}

if(mysql_num_rows($query) <= 0) {
    die("The table does not contain any fields");
}

while($result = mysql_fetch_array($query)) {
    echo "User ID: ".$result['user_id']."<br />"; 
    echo "Username: ".$result['user_name']."<br />"; 
    echo "E-Mail: ".$result['user_mail']."<br />"; 
    echo "Joined: ".$result['reg_date']."<br />";
    echo "<a href=\"index.php?profile={$result['user_id']}\">View Profile</a> | ";
    
    if(isset($admin)) {
        echo "<a href=\"admin.php?del={$result['user_id']}\">Delete User</a> | ";
        echo "<a href=\"admin.php?edit={$result['user_id']}\">Edit User</a>";
    }
    echo "<hr />";
}

mysql_free_result($query);

?>

We run a simple SQL command using the mysql_query function, and if the function don't return any errors we use a while loop.
The loop will go through the entries and fetch tem one by one, mysql_fetch_array...
Then we simple echo the stored data...

At the end of the file, use mysql_free_result to free up the used memory...

PART 12: Useful Information (Click to View)

Now I hope I could teach you atleast how to print out things in PHP..
I just didn't care about security of this script, as it was never meant to be used in public..
This is up to you to learn, I have also posted a tutorial on how to secure your scripts. You can read it over here;
http://www.supportforums.net/showthread.php?tid=700

All other information about MySql Functions and PHP can be found on PHP's homepage;
http://php.net/manual/en/book.mysql.php

And Also make sure to read as much as you can about MySql command itself...
http://dev.mysql.com/doc/

I hope this tutoial have showed you how to work with MySql using PHP...
It's 03:23 AM and I'm tired, my keyboard batteries also give up on me, good night and good luck learning...

[Spl3en]

Awesome tutorial !!

Your SQL requests can injected (COOKIES and GET), but i guess that security is not the goal of this tutorial...
Keep up the good work MoTU

[Gaijin]

Awesome tutorial !!

Your SQL requests can injected (COOKIES and GET), but i guess that security is not the goal of this tutorial...
Keep up the good work MoTU

Thanks Spl3en...
I left the security away from this, so people can learn by them self... Security isn't a copy & paste thing... while this tutorial can just be that... ;)

[Spl3en]

I see what you mean, and i agree
Really good job there

[Gaijin]

Thanks man, means alot ;)

[tacolord455]

I got

Warning: require_once(connect.inc.php) [function.require-once]: failed to open stream: No such file or directory in /home/freecomp/public_html/testing/index.php on line 3

Fatal error: require_once() [function.require]: Failed opening required 'connect.inc.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/freecomp/public_html/testing/index.php on line 3

Can you tell me what I did wrong please?

[Gaijin]

I got

Warning: require_once(connect.inc.php) [function.require-once]: failed to open stream: No such file or directory in /home/freecomp/public_html/testing/index.php on line 3

Fatal error: require_once() [function.require]: Failed opening required 'connect.inc.php' (include_path='.:/usr/lib/php:/usr/local/lib/php') in /home/freecomp/public_html/testing/index.php on line 3

Can you tell me what I did wrong please?

Obviously, you don't have the file connect.inc.php..
Chnage the file config.inc.php to connect.inc.php...
Sorry that was my mistake...

[tacolord455]

Works great now, thanks. Could you do a tut on how to make it more secure now? That would be a huge help.

[Gaijin]

Works great now, thanks. Could you do a tut on how to make it more secure now? That would be a huge help.

Sure man, Will be up a day or 2... Check back ;)

Thank you!

[Unwanted]

Why are you not escaping your query's to the database that allow the user to add their own text?