User Accounts
MySQL PHP Tutorial 1: MySQL User accounts
MySQL PHP Tutorial 2: PHP login Session
MySQL PHP Tutorial 3: PHP Form Validation
MySQL PHP Tutorial 4: PHP User Access Control
Create MySQL User Accounts
This MySQL PHP tutorial walks you through how to create user accounts with a username and password, using MySQL and PHP. The user’s password will also be saved with a cookie, using PHP, so next time they will be automatically logged into the site, the cookie will expire after 30 days.
Additional resources to help you learn PHP and go well with this guide are:
PHP Hypertext Preprocessor Manual
MySQL PHP Tutorial Step 1: phpMyAdmin and MySQL
The first step is to create the MySQL database and table, this will hold all user information and passwords. You can use phpMyAdmin which is what I prefer or you can use MySQL commands to create the database and table. To access phpMyAdmin, login to your web hosting account and you should find phpMyAdmin, most hosting company have it installed. Once you’re in phpMyAdmin create the database where it says Create new database, type the database name then click on create. The MySQL command line to create a database is “CREATE DATABASE database_name;”. The database in this guide is called login so the MySQL command would be “CREATE DATABASE login;”. To access the MySQL command prompt in phpMyyAdmin, click home, and click the tab that says SQL. Type in the SQL query inside the box, be sure to use capital letters for the commands, and lowercase for the variables. Then click on the GO button to execute the query.
After the database has been created you need to create a table. When you create a table it is important to know how many columns it will consist of and what their names are. You can always add additional columns later, but it is much cleaner to do it beforehand. The MySQL query command to add a new column would be, “ALTER TABLE table_name ADD COLUMN column_name variable(length) (NULL or NOT NULL);” So in this guide the query might be, “ALTER TABLE tuser ADD COLUMN age int(150) NOT NULL;” In this guide, we will be creating seven columns, user id, username, password, first name, last name, email address, and join date. the user id column, userid, will be the AUTO_INCREMENT column, this is important because it will define each row with a unique number.
In phpMyAdmin click on the database in the left column, where it says Create new table on database database_name, type the table name, enter the number of fields, and click GO. The table name in this guide is tuser. So in this case it will say Create new table on database login, the table name will be tuser, and there is 7 columns or fields. You will then enter the field names, the data types, and the data lengths for each row. In this guide the columns names will be userid, username, password, firstname, lastname, email, and join_date. The userid will be a numeric data type, INT, and the data length will be 11, under the index column choose primary since the user id is the primary key, and check the column A_I to turn on auto increment. The username will be a string data type, VARCHAR, and the data length will be 32. The firstname will be a string data type, VARCHAR, and the data length will be 50. The lastname will be a string data type, VARCHAR, and the data length will be 100. The email will be a string data type, VARCHAR, the data length will be 100. The password will also be a string data type, VARCHAR, the data length will be 200. The password is 200 characters long for encryption purposes. Last but not least the join_date will be a date data type.
The MySQL query to create the whole table is this:
CREATE TABLE IF NOT EXISTS `tuser` ( `userid` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(32) NOT NULL, `firstname` varchar(50) NOT NULL, `lastname` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, `password` varchar(200) NOT NULL, `join_date` date NOT NULL, PRIMARY KEY (`userid`) )
MySQL PHP Tutorial Step 2: Connecting to the MySQL database
When a user signs up or logs in, they will be connecting to the MySQL database. This config.php file must be included whenever a user signs up or logs into the site so they can connect to the login database. For the host you would replace example.com with your MySQL server name, the user is the user with access to the MySQL server, the password is the user’s password, and the database is login (the database name in this guide).
<?php
//create MySQL server information constants
define('DB_HOST', 'example.com');
define('DB_USER', 'user');
define('DB_PASSWORD', 'password');
define('DB_DATABASE', 'login');
//The link variable is the connection to mysql server
$link = mysql_connect(DB_HOST, DB_USER, DB_PASSWORD);
//If link fails to connect then display error message, failed to connect to server
if(!$link) { die('Failed to connect to server: ' . mysql_error()); }
//The db variable selects the database to connect to
$db = mysql_select_db(DB_DATABASE);
//If db does not connect show error message, unable to select database
if(!$db) { die("Unable to select database"); }
?>
MySQL PHP Tutorial Step 3: The PHP login form
In your PHP file you will need to include a form that will execute the PHP code. In this guide the form is in the file called login.php and is included using <?php include(“login.php”); ?>, which we will get into later. In the form tag the action attribute is the URL or the PHP file that will process the data when the form is submitted, so in this guide we will be using a PHP file called login_process.php. The method is the way PHP will be grabbing the data from the form. The method we are using in this guide is POST. To read more about forms go to W3Schools.com. In the form the input tag is used, three important attributes about the input tag are type, name, and value. The type specifies the input element, such as text, password, checkbox, radio, button, file, hidden, or submit. As you noticed in this guide, the input for the username is a text type, the input for the password is a password type, and the input for the button is a submit type. The value for the button is login. When we call certain inputs within PHP we refer to the name, in this guide the input for the username is named login and the input for the password is named password. We will talk about this more later. We are also going to be creating cookies, which will remember the username and password, we will get more into this later too, so the value of the username will be <?php echo $_COOKIE[‘username’]; ?> and the value of the password will be <?php echo $_COOKIE[‘password’]; ?> .
<form action="login_process.php" method="post" class="login"> Username: <input type="text" name="login" value="<?php echo $_COOKIE['username']; ?>" /><br /> Password: <input type="password" name="password" value="<?php echo $_COOKIE['password']; ?>"/><br /> <input type="submit" value="Login" /> </form>
MySQL PHP Tutorial Step 4: The PHP signup form
The sign up form is done the some way as the login form, in this guide it is called signup.php. The action is signup_process.php and the method is POST. The input type for first name is text and the name is fname. The input type for last name is text and the name is lname. The input type for email is text and the name is email. The input type for username is text and the name is username. The input type for the first password field is password and the name is password1. The input type for the second password field is password and the name is password2. The input type for the button is submit and the name is submit. The value of the button is sign up.
<form action="signup_process.php" method="post" > <span> <label for="firstname">First Name:</label><input name="fname" type="text"/><br /> </span> <span> <label for="lastname">Last Name:</label><input name="lname" type="text" /><br /> </span> <span> <label for="emailaddress">Email:</label><input name="email" type="text" /><br /> </span> <span> <label for="username">Username:</label><input name="username" type="text"/><br /> </span> <span> <label for="password1">Password:</label><input name="password1" type="password" /><br /> </span> <span> <label for="password2">Password (retype):</label><input name="password2" type="password" /><br /> </span> <input type="submit" value="Sign Up" name="submit" /> </form>
Now that the database has been created and both forms are setup, we can start part 2 of the MySQL PHP tutorial. Which is creating a PHP session code for the login and the signup process. The important notes to keep in mind are the names for each input type, the method used for the forms, and the action being used or the PHP file. Other notes to keep in mind are the column names in the MySQL table. We will be getting into sessions and cookies later, which are very important if you want to save information.
