This tutorial will teach you the basics of using MySQL with PHP.
Requirements
Webserver Running PHP & MySQL
IQ > 20
Executing MySQL Queries with PHP:
Explanation:<?php$host = '127.0.0.1';$user = 'username';$pass = 'password';mysql_connect($host, $user, $pass);$sql = '<the sql query>';$result = mysql_query($sql);?>
Line 1: Declare PHP code
Lines 2-4: Set connection variables
Line 5: Connect to the database server
Line 6: Set the SQL query
Line 7: Run query on the database
Line 8: Declare end of PHP code
Creating A Table
The SQL Query:
CREATE TABLE `test` (`user_id` INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(`user_id`), `username` VARCHAR(50), `password` VARCHAR(50), `salt` VARCHAR(5))Query Explanation:
•It creates a table named "test" with 4 values:
•user_id: Integer, Not Null, Automatically increases
•username: 50 characters of space
•password: 50 characters of space
•salt: 5 characters of space
Inserting Data Into the Table
The SQL Query:
Using it with PHPINSERT INTO `test` (`username`, `password`, `salt`) VALUES ('USER_NAME', 'PASS_WURD', '!@#$%')Query Explanation:It inserts a row into the database that will have information like so:0 USER_NAME PASS_WURD !@#$%
register.php:
PHP Explanation:<?php$host = '127.0.0.1';$user = 'username';$pass = 'password';$db = 'test';mysql_connect($host, $user, $pass);mysql_select_db($db)if (isset($_POST['submit'])) {$username = addslashes($_POST['user']);$password = addslashes($_POST['pass']);$salt = "";$salt_usable = "1234567890!@#$%^&*()<>?,./[]\{}|";$i = 0;while ($i < 5) {$char = substr($salt_usable, mt_rand(0, strlen($salt_usable) - 1), 1);$salt .= $char$i++;}$password = md5($salt . $password);$sql = "INSERT INTO `test` (`username`, `password`, `salt`) VALUES (\'" . $username . "\', \'" . $password . "\'. \'" . $salt . "\')";$result = mysql_query($sql);echo('User successfully registered. You may now login <a href="login.php">here</a>.');} else {echo('<html><head><title>Register</title></head><body><form action="" method="POST"><input type="text" name="user" value="Username" /><br /><input type="password" name="pass" value="Password" /><br /><input type="submit" name="submit" value="Register" /></form></body>');}?>
Lines 1-6: Previously Explained
Line 7: Select the database we want to access
Line 8: Check to see if the form was submitted...
Lines 9-10: Grab the submitted info (NOTE: The addslashes() command is very important here as it prevents SQL injection)
Line 11: Make salt variable
Line 12: Declare allowed characters for the salt
Line 13: Make i variable
Line 14: Simple while loop
Line 15: Pull a random character from the salt_usable variabe
Line 16: Add the random character to the salt string
Line 17: Increase i variable so we don't have an infinite loop
Line 18: End while loop
Line 19: Secure the password by MD5ing it along with the salt
Line 20: Create our SQL string based on our user inputs
Line 21: Run the SQL query on the database
Line 22: Inform the user they have been registered
Line 23: If the data wasn't submitted...
Line 24: Show the registration form
Line 25: End if statement
Getting Data From the Table
The SQL Query:
SELECT `salt`, `password` FROM `test` WHERE `username`='USER_NAME'Query Explanation:
The query looks in the table for the value of salt AND password ONLY WHEN the username is 'USER_NAME'
Using it with PHP
login.php:
PHP Explanation:<?php$host = '127.0.0.1';$user = 'username';$pass = 'password';$db = 'test';mysql_connect($host, $user, $pass);mysql_select_db($db)if (isset($_POST['submit'])) {$username = addslashes($_POST['user']);$password = addslashes($_POST['pass']);$sql = "SELECT `salt`, `password` FROM `test` WHERE `username`=\'" . $username . "\'";$result = mysql_query($sql);$return = mysql_fetch_array($result);if ($return(0) == "") {echo('Username not found in database');} else {$salt = $return(0);$real_pw = $return(1);$password = md5($salt . $password);if ($real_pw != $password) {echo('Incorrect password.');} else {echo('Successfully logged in as ' . $username);}}} else {echo('<html><head><title>Login</title></head><body><form action="" method="POST"><input type="text" name="user" value="Username" /><br /><input type="password" name="pass" value="Password" /><br /><input type="submit" name="submit" value="Login" /></form></body>');}?>
Lines 1-10: Previously Explained
Line 11: Form our SQL query
Line 12: Execute our query
Line 13: Retrieve the data from our query
Line 14: If no data was retrieved...
Line 15: Tell the user that the username was not found
Line 16: If data was returned...
Lines 17-18: Set the return data to clearer variables
Line 19: Update inputted password by MD5ing it with retireved salt
Line 20: If the passwords don't match...
Line 21: Inform them they have the wrong password
Line 22: If the passwords match...
Line 23: Inform the user they have been "logged in"
Lines 24-29: Previously explained.
Posts
{ 0 comments... Skip to Comments }
Please comment here