Using MySQL with PHP

Introduction

In this blog post, we will take our PHP login page created for the previous post and build on to include functionality of MySQL databases.

The task for this week is to:

• Log into the SQL server using the command line and perform some commands such as listing the databases
• Attempt to connect to SQL by using PHPMYADMIN tool
• 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. 

Log into the SQL server using the command line and perform some commands such as listing the databases

First the command line is opened by running the cmd.exe in the windows 7. To login to the MySQL server first you need to find the Bin folder located in the MySQL directory in my case “D:\xampp\mysql\bin”. To go to this directory in the command line first enter "D:" to go to the D drive and then the command “cd D:\xampp\mysql\bin” was entered.

To log into the Server you must use the following command and login as root for the first time since no other users exist (unless more users where created using XAMPP, but more on that later), The command used was : “mysql -uroot –p”. When this command is entered a prompt for the root password appears as shown belong.

As soon as one logs into mysql the path in the command line will be changed to mysql. Here is where one can run commands in the server. To show all the databases you have in the MySQL server “show databases;” command was entered with the result below.

  

Attempt to connect to SQL by using PHPMYADMIN tool

To login into PHPmyAdmin the Xampp homepage was logged into (by typing http://localhost). From the menu here, the PHPmyAdmin was selected. This will prompt the user to insert the login for MySQL (in this case we kept using “root”).

The phpMyAdmin is a tool build in XAMPP to automate the creation of databases and tables without the need of writing any SQL code. As one can see from the screenshot below all the databases are listed on the left, and by clicking on any one of them, you will enter in another screen that allows you to edit the particular database, and also shows all the tables that are in that database.

Create a Database that stores usernames and passwords

To Create a Database using the interface provided in phpMyAdmin is very easy. In the main page of the tool there is a textfield under “MYSQL LOCALHOST” that allows you to enter the name of the database you want to create and by clicking the “create” button the database is now created in the MySQL server.

When this button is clicked the next screen shown is to start creating tables to be used in this database, using the same concept of before, the name of the table was entered and the number of fields in the table was also entered (in this case 2). The go button will take you to the next screen. This screen is used to create the fields of the table by inserting the name, type, and primary keys etc… The Save button in the bottom of the screen will then perform the SQL command to create the table with the information given.

Once the table is created some data of usernames and passwords where entered by using an insert command in the SQL screen or the insert feature from GUI of the Tool.

Modify your PHP program from the previous lab session to connect to the database to 

authenticate the user

Once all of that was set up, it was time to change the Login screen created to incorporate the use on the MySql database.

First to connect to the database a variable with  the "mysql_connect" function was used an it was given the server name and login and password.

Then to select the database "mysql_select_db" was used to select the database.

A query was used to select all the usernames and password where the username and password are the ones inputted in the textboxes. To reduce the chances of SQL injection attacks the "sprintf(" function along with the "mysql_real_escape_string" function to pass the username and password instead of just insert them into the sql query string.

A variable is then used to store the results of the query and another variable will hold the number of rows of the result. 

If there is a row in the results then the username is confirmed to be genuine thus a Session is started to send the username to the next page and the user is now redirected to the mainpage.

In the end the SQL connection is close to reduce the usage of resources.