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.

More PHP

PHP, the Basics

Introduction

This blog will start discussing the basics of PHP (Hypertext Pre-Processor), a free, server side scripting language that is combined with HTML. Although targeted towards Web is has its concepts and ideas similar to that of JAVA and C++, and is now one of the most popular scripting languages today.

The main reasons why PHP is so popular amongst web developers apart from the fact that it’s free is because of its high performance, its ease of learning and its comprehensive set of database access functions.

Task

In this week's exercise we were asked to:

•  Verify that PHP is working on our web server using our browser.
• Create an associative array of usernames and passwords and list the entire array in a table.
• Explain in our blog the difference between the echo() and print() functions.

Verify that PHP is working on our web server using our browser.

With the XAMPP package downloaded last week, checking if PHP is working is easy. In the XAMPP homepage (http://localhost) in the menu status, XAMPP shows the status of all its services and one of them is PHP. This service needs to be activated in order for PHP to work on the web server.  Also further proof of the PHP service is running is the address being used in the web browser which is http://localhost/xampp/index.php. Since the webpage is working using a .php file then the PHP service is working.

Create an associative array of usernames and passwords and list the entire array in a table.

To script with PHP first you must create the "<?php" and "?>" open and close tags within the HTML file. Then I created an associative array “$logins” which lists all the user names and passwords.  Then to list all the items in the array in a table, I first echoed the table border that will be used and then I used a while loop to echo each username and password.

Difference between Echo and Print Functions

After some research lead me to websites such as http://www.learnphponline.com/php-basics/php-echo-vs-print the following differences could be said about both language constructs.

Even though both are used for the same operation, Echo is considered to be faster (although no considerable speed differences will be noticed during runtime) than "print" because it simply spits out HTML mark-up in its calling position within the document without returning a value unlike “print”. Print - Returns an integer value 1 for no reason.

Also "Echo" can take more than 1 parameter when used without a parentheses by using commas (","), "print" cannot have multiple expressions.

As you can see above the differences between both functions are small, the one to choose depends on ones’ likes. Choosing "echo" should be better because of performance and multiple parameters but print will always retain its nostalgia factor.