Week 9 - More PHP (Databases)

During the last lesson, we learned more on PHP, this time using databases.

Database

A database is used to store data, using tables. The following are SQL commands which are commonly used:

CREATE – to create tables within the database
INSERT – to add data inside the tables
SELECT – to view data
DELETE – to remove data
UPDATE – to modify data
DROP – to delete tables

PHP and MySQL

In PHP one can interact to a database using the mysql_connect command:

 mysql_connect(server, username, password)  

Lab Session

During the lab session, the following tasks have been given:

• Log into the SQL server using command line and perform some commands such as listing the databases
• Attempt to connect to SQL by using PHPMYADMIN
• Create a database that stores usernames and passwords
• Modify your PHP program from the previous lab session to connect to the database to authenticate the user

Logging into the SQL server

To log into the the SQL server using command line:

1. Open command line prompt
2. Then if you have C:\Users\Name write cd ..\..\
3. Then the foldercd C:\XAMPP\mysql\bin was opened and the command mysql -u root -p and the datapase password have been entered.
4. To show a list of databases, the show databases; command have been inserted.

phpMyAdmin

phpMyAdmin is used to administer MySQL over the web.

phpMyAdmin

Creating database

A database that stores usernames and passwords have been created, where I used a GUI Tool named Workbench.

1:  create database `usersdatabase`;  
2:  DROP TABLE IF EXISTS `usersdatabase`.`usertable`;  
3:  CREATE TABLE `usersdatabase`.`usertable` (  
4:   `UserID` int(10) unsigned NOT NULL AUTO_INCREMENT,  
5:   `UserName` varchar(45) NOT NULL,  
6:   `Password` varchar(45) NOT NULL,  
7:   PRIMARY KEY (`UserID`)  
8:  );  

Records have been inserted into the database using the INSERTcommand. To view data, the SELECT command is used.

Modification of PHP program

In the previous post, an associative array was created to store the usernames and password. In this post, a database have been created to store the usernames and password.
To connect to the database the php code shown in lines 12-13 is used

12:  $con = mysql_connect("localhost","root","");  
13:  mysql_select_db("usersdatabase", $con);  

If the connection from the database will fail, the code shown in line 17 is used

17:  die('Could not connect: ' . mysql_error());  

Lines 20-24, a variable named $result is used to execute a query to select from the table where the username and password will be what the user inserted in the form. Line 21 will calculate the number of rows the $result variable has. If the result is not zero, a welcome message will appear. If the user checks on the 'Remember Me' checkbox, a cookie will be created. If the username and password does not match and alertbox that it is invalid will appear.

20:  $result = mysql_query("SELECT * FROM usertable where UserName=\"$user\" and Password=\"$pass\"");  
21:  $row = mysql_numrows($result);  
22:  if($row !=0)  
23:  {  
24:  echo ("Welcome $user");  

The rest of the code is the same as found in the last post. In my next post I will be discussing about MySQL injection and about the next coursework.