PHP and MySQL

Introduction

Welcome again to my blog. This weeks blog will focus on the usage of the MySQL database in the PHP language. Below is a list of tasks that I will be addressing in this post.

• Logging into the SQL server using command line and performing some commands such as listing the databases
• Attempting to connect to SQL by using PHPMYADMIN
• Creating a database that stores usernames and passwords
• Modifying my PHP program from the previous lab session to connect to the database to authenticate the user

Working with MySQL using Command Line

The first thing I did was to hold the window key and type r, to obtain the run window. By typing cmd into the TextField provided and pressing enter, I opened the windows Command Line window. At first I couldn't connect to the MySql database. Later I found out that to connect to the MySQL database, you need to find the Bin folder, in the MySQL directory first. I typed the command below to go to the apprapriate directory.

-> CD C:\xampp\mysql\bin

The first time you log into the MySQL database, you need to login as root, since no other user exists yet.  To login as root I used the command

-> msql -uroot -p

Immediatly, I was promted for the root password. This is what the login looks like.

As soon as i logged into mysql the command line path changed to simply mysql. In this state functions can be run in the command line. Below is a list of some commands I used in this exercise.

1 2 3 4 5

mysql -uroot -p show databases GRANT ALL ON *.* TO Mark@localhost IDENTIFIED BY 'password' exit mysql -u Mark -p

Command 2 in the table above lists all the databases available. The command looks like this in the CMD.

Before Starting the next tasks, I decided to create a new user called Mark. I granted Mark all privileges on all databases. GRANT ALL *.* does this job. This command if followed by TO username@host where username is the login name and the host address is the Mysql address. The command is followed by IDENTIFIED BY 'PASSWORD' where the word between the apostrophes is the user's password. The command is listed line 3 of the table provided. To login with the new user, you have to run the exit command to exit mysql and then login again. To login in as user Mark I used the command in Line 5.

There are other interesting MySql commands to explore. You can select a list of users existing in the mysql database. You do this by executing the Query:

Select  user FROM mysql.user;

This command produces the following output

You can also use the command show Tables, to view the tables in a database. To do this, first you must select the database you are using and then execute the show tables command. Once you have a list of tables in the database, you can view the details of each table by typing "desc" followed by a table name. The image below shows how the use command is executed to select a database, showing the message Database changed. It also shows the results for show tables and the description of the login table.

Loging In PHPmyAdmin

To login into PHPmyAdmin I typed localhost in the web address and logged into xampp main screen which was discussed in a previously dedicated post. From there I used PHPmyAdmin. Where I clicked on the shortcut, I was requested to login. I logged in as user Mark which I had previously created in the Command Line. The login screen looks like this.

phpMyAdmin provides a lot of tools that automate the creation of databases and tables without the need of writing any SQL code. Databases are listed on the left of the screen, and by clicking on the database name, you can enter in another screen that allows you to edit the particular database. The images below are show screenshots of this tool.

The image below shows the initial screen after logging into phpMyAdmin. To select a Database, you simply need to click on it from the list in the left panel.

When a database is selected, all tables in the database are listed.The image below shows the options available when editing a database.

User privileges can also be handled using phpMyAdmin. This can be done by clicking on the privileges tab.  The image below shows the various access options, that a user can be granted or denied.

phpMyAdmin allows you to also edit tables and table contents. If you click on the first icon in the Action column in the table list, you are allowed directed to another screen that allows you to edit table contents. Various option are available at this point, and are visible in the picture below.

The insert tab provides an easy to use graphical screen to input records in the  table.

The Sql tab allows you to run queries on the database and provides a list of fields of the current table in use to make query building easier.

For more advanced queries, phpMyAdmin provides a query builder, similar to the one used in Microsoft Access. In this query building tool, fields and conditions can be creating using the GUI tools. Finally these tools output the equivalent SQL code.

phpMyAdmin allows you to save time while editing the database and since it is running on the apache server, it can be run from all PCs on the network (if not restricted). There are various mysql manager and admin tools available other than phpMyAdmin. When Working from a local machine I prefer using SQLYog Which provides similar functionality, but is more user friendly.

A screenshot of SqlYog community edition

Creating The Database

To create the database I used the create database Query. The table below lists the queries I will be using in this section.

6 7 8 9

create Database phpfileupload use phpfileupload CREATE TABLE `login` ( `UserName` VARCHAR (10) NOT NULL, `Password` VARCHAR (10) NOT NULL, CONSTRAINT PK_Login PRIMARY KEY (UserName) ); INSERT INTO login (username, PASSWORD) VALUES ('Mark','Galea')

I assigned phpfileupload as the name for my database. Command number 6 shows how to create a database and command 7 directs the queries to a particular databases. The CREATE TABLE command on line 8 is used to create a table in the database, where login is the table name, UserName and Password are columns of the table and UserName is the primary key of the table. VARCHAR is the datatype to Store String values and the value in the brackets is the length of the string (number of characters allowed). NOT NULL indicates that the fields cannot be left empty.

Command 9 shows a simple insert command used to insert values in the table Login. In this case I inserted a row with Mark as Username and Galea as password.

Login using the login table in the MySQL database

For this task I used the same code as last weeks post, but I altered my code such that it obtains a list of logins from my database, instead of an inbuilt array. The Code below shows the what happens when the Login button is clicked in the login screen.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

<?php if(isset($_POST['btnLogin']))     {              $con = mysql_connect("localhost","Mark","password");              if (!$con)         {         die('Could not connect: ' . mysql_error());         }         else         {         $username = $_POST['txtUserName'];         $password = $_POST['txtPassword'];         $Remember = isset($_POST['ckRemember'])? true:false;                  mysql_select_db("phpfileupload",$con);                   $SqlQuery= sprintf("SELECT username,password from login where username = '%s'",         mysql_real_escape_string($username));                  $Passwords=mysql_query($SqlQuery);                  mysql_close($con);              while($row= mysql_fetch_array($Passwords))           {             if ($row['username'] == $username && $row['password']==$password )             {                          $date = getdate();                          setcookie('LastLogin', Date('d/m/Y').' '.$date[hours].':' .$date[minutes].':'. $date[seconds],time() + 3600);                    if ($Remember == true)                 {                     $_SESSION['User']=$username;                 }                 else                 {                     session_destroy();                 }                  header('Location: fileuploadwelcome.php?Name=' . $username);             }         }        echo "<script>alert('Invalid Login')</script>";     }                } ?>

Line 5 shows the mysql_connect() command. Given the host address, username and password of a database, this command creates a connection. Line 7 show the code that tests the connection,. If the connection fails, the die function displays an error message. The function mysql_select_db() connects to a database. the function takes in the database name and a connection as inputs.

To check if a login exists, I queried the table login to display the user with the login name provided. Instead of using the normal concatenation sign, I used the code shown on lines 20 and 21. The function sprintf is used to output a string and save it in a variable. This function also allows you to insert a string in the original string. The  new strings will replace the %s characters. In my case I used this method to insert the username in my query string. I also used the method mysql_real_escape_string($username)  such that any special characters in the variable are assigned an escape sequence such that the string becomes safe to be inserted in a query and SQL injection is avoided.

The variable $Passwords is assigned the results of the query on line 23. The query is executed with the command mysql_query() which takes in a string command as input. As soon are the data needed is retrieved, I used the command mysql_close($con) to close the database connection. Rows in the variable $Passwords are obtained using the function mysql_fetch_array($Passwords).  Values in rows can be viewed by writing the row name in the square brackets of the row array. Example $Row['UserName'] gets the username from the row. In line 29 I verify the login details to check if they are valid. The rest of the code remains unchanged from last week's session.

It is good practice to avoid storing passwords as plain text. The best way is to encrypt or generate a hash code for the passwords and store these in the mySql Database. To authenticate a login then you must simply encrypt the password and compare the encryption result with the encrypted password in the database. In this particular example I used no encryption, but it can easily be added later.

Conclusion

PHP provides quite a good and easy to use functionality for queering the database. In fact PHP avoids using DataReaders or DataAdapters which are slow and difficult to handle. Instead it provides simple functions that given a connection and a database, they are able to run an SQL command and Retrieve SQL Data easily. 

Introduction to PHP

Introduction

This week's post will be about the PHP server side technology. It will include two tasks which I merged into one big post. 

Taks 1 is very short and consists in:

• Verifying that PHP works on the web server.
• Creating an associative array of user names and passwords and listing the entire array in a table.
• Explaining the difference between the echo() and print() functions.

Task 2 adds up to the first task. Duties for task 2 include:

• Using PHP to create a login screen that accepts a user and a password that are validated on the server side.
• Adding a “remember me” option that uses a cookie so that the user does not have to log in again.
• Replacing the cookie mechanism with PHP sessions.

Some notes about PHP

On the main PHP website, PHP is described as a general purpose scripting language with the goal of allowing web developers to create Dynamic web pages. PHP can be embeded in HTML and uses syntax similar to other common languages such as C and Java.

PHP is useful when developing large websites as it would take less time to develop in PHP than in any other language. This language is easy to learn and provides a great deal of inbuilt commands that are useful when creating a website.

PHP is a Loosely-Typed Language, meaning that a variable does not need to be declared before adding a value to it. PHP automatically converts the variable to the correct data type, depending on its value.

Unlike in Strongly typed languages, in  PHP there is no need to declare the variable type. The variable type is chosen automatically when a variable is used.

Task 1

To test my web server, I connected my notebook to my home network and launched the apache server, through the XAMPP control Panel. Then I created a simple PHP webpage and saved it in the web server's path. Finally I tried to access it from another PC on the network and everything worked fine.

Creating an Assosiative Array

Well the first thing I did when I saw the first task, was to go to http://www.w3schools.com/ where I took a very short tutorial about PHP. I was not new to programming because I already have a little knowledge in Java and some .NET languages, but I had never written or seen any PHP script before. Since I am more used to program in strongly-typed languages, PHP seems a bit dangerous for me because there is a greater change to get confused when using variables. After reading the tutorial I tried to create my first script. I used the echo function to get a grasp of how PHP works.

In the array section of the tutorial there was an easy way to create an associative array and I used that method to create mine. An associative array is an array that allows the mapping of string keys to values, rather than the traditional zero-based index. In this type of array values can be used as keys that reference to other values. For the purpose of this task i created an array of Passwords using usernames as keys that map to the corresponding password. Notice how I used \$ instead of just $ to insert a dollar sign as part of the password.

$Passwords = array("Mark"=>"Galea", "Paul"=>"Borg", "Joe"=>"pa\$\$word");

This next part of the task is to display the array in a table on a web page. To list the usernames and password in a table, I used a loop that goes through all passwords and echoes each line in  <tr> and <td> tags that form the rows and cells of the display table. The function below starts by creating the previously described array, on Line 4. Line 5 and Line 6 show the use of the echo functions to create the initial tags of the HTML table.

The reset function on line 7 resets the array index of $Passwords, to the first element. The while function is used to loop through the elements in the password array. The each function returns the both the key and value of each element in the array while the list function assigns the key value to the $Key variable and the actual value to the $value variable. In this case, the $key is the username and the $value is the password. These values are echoed together with the appropriate HTML Tags to for a table. 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

<?php function showPSD() { $Passwords = array("Mark"=>"Galea", "Paul"=>"Borg", "Joe"=>"pa\$\$word"); echo "<div><br/><table border='1'><tbody >"; echo "<tr><th>Username</th><th>Password</th></tr>";  reset ($Passwords); while(list($key,$value)=each($Passwords))     {      echo "<tr ><td>" . $key . "</td>";      echo "<td >" . $value . "</td></tr>";      } echo "</tbody></table></div>"; } ?>

The above table would look like this:

Difference between echo and Print functions

The echo functions and the print functions, both do the same thing which is inserting text into the webpage's HTML. Although they do the same thing, they work slightly differently.

Well the first difference is that echo can print more than one line at a time, separated by a comma, while the Print function can only print one line at a time. Echo is faster than print, although not noticeable at all by users. This is because echo is more similar to a command while print works more like a function which also returns a value. Since print returns a boolean value, it can be used in situation where the value true must be returned after inserting text in the html. Print could be used in a situation similar to this:

$something = $myvar > 2 ? print("Success!") : false; 

Task 2

For task 2 I reused the array in task one to store the login details for the login screen. I decided to make a simple white, clean, centered login screen for this task since I was more concerned about the functionality of the page rather than it's looks.

Creating the Login Screen

The login screen consists of a header, and a table with two columns. The first two rows of the table contain the username and password, labels and input fields. Password uses password as input type, such that the content entered by the user is not visible on screen. The table is only used for alignment as is not actually visible. The screen also has a remember me check box and a Login button.

The user of the webpage must enter his user name and password and click on the login button. The login button submits the form and the values entered are validated. If the username and password are correct, the user is redirected to another page, else an error message is displayed. The code below shows the validation process.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30

<?php if(isset($_POST['btnLogin'])) {   $username = $_POST['txtUserName'];   $password = $_POST['txtPassword'];   $Remember = isset($_POST['ckRemember'])? true:false;       reset ($Passwords);      while(list($key,$value)=each($Passwords))        {         if ($key == $username && $value==$password )          {          $date = getdate();          setcookie('LastLogin', Date('d/m/Y').' '.$date[hours].':' .$date[minutes].':'.            $date[seconds],time()+3600);           if ($Remember == true)              {               setcookie("User",$username,time()+3600);               }           else              {               setcookie("User","",time()-3600);              }           header('Location: coursework_2p2.php?Name=' . $username);           }        }       echo "<script>alert('Invalid Login')</script>";     } ?>

Line 2 of the above table, shows the isset() function that verifies if a variable or object exists. In this case it is being used to check if the submit button was clicked. Lines 4 and 5 show how the values entered in the input boxes can be captured. The $_Post["itemName"] gets the values of the posted items.  To capture the value of a Check Box, I had to use the isset() function, because the post value of the check boxes is only created if the check box is checked, and therefore an error would be generated if i tried to get the post value of a non-checked check box. Similarly to what I did in task 1, I used the while loop on line 9 to loop through all login details. The if statement on line 11 checks if the login details entered match any of those in the $passwords array. In case of a valid login, I used the header function to go to another page and passed the username as a string query. The browser is then redirected to another webpage showing a welcome screen with the user name. If no login matches those in the array, line 28 is executed showing an alert saying invalid login.

In case of a valid login the welcome label on the redirected page is retrieved using the $_GET['ItemName'] Method. I used the following code in my screen :

echo "<h1>Welcome ". $_GET['Name']."</h1>";

The resultant page looks like this, with Mark being the username entered in the previous screen. Please ignore that date and the log out button for now.

Remember Me Feature

Instead of the remember me button as suggested, I used a check box for the remember me feature. When the check box is checked, a cookie is saved in the browser as saves the username of the valid login. Line 16 to 23 show how the remember me feature works. When a valid login is found, the code checks if the remember feature is needed, if yes, a cookie is created with the new username, else previously existing cookies are deleted. The setcookie() method is used to create a cookie. this method takes three arguments which are Cookie name, value and expiration date. For the expiration date I used Time() + a number of seconds. Time() returns the number of seconds from the first Date which is (January 1 1970 00:00:00 GMT). To delete a cookie I used the setcookie() method with an already expired date. Values stored within coockies can be accessed using $_COOKIE["ItemName"]. In the login screen I added the following code:

1 2 3 4 5 6

<?php if(isset($_COOKIE['User'])) { header ('Location: coursework_2p2.php?Name=' . $_COOKIE["User"]); } ?>

This code redirects the user to the welcome screen (second screen previously mentioned) without the need to re-login. The welcome screen contains a log out button that deletes the cookie and redirects the user back to the login screen.

Using Sessions

For task 2 we were asked to redesign the same system to use sessions instead of cookies. I decided to use cookies to store the first login date, and sessions to store the logged user. Sessions work a little bit different from cookies. Sessions must be started before the HTML tag with the function session_start().

To initialize a session variable you only have to use it as it were any other variable. For example $_SESSION['USER'] = "Mark" creates a session the the key 'User' and value 'Mark'. To destroy a session you must call the function session_destroy(). In my case I used the session_destroy() in the log out button. To convert my system from sessions to cookies I simply changed the parts with cookie methods, with the equivalent session statements. The table below shows how I changed lines 16 to 23 from the first table in task 2.

16 17 18 19 20 21 22 23

if ($Remember == true)              {               $_SESSION['User']=$username;               }           else              {               session_destroy();              }

Something new which I learned today and took me some time to figure out, was how to insert values in labels.   Finally I found this method which worked for me. I used it to show the first login date and time.

<label id="Label1" name="lblLoginTime"  > <?php if (isset($_COOKIE['LastLogin'])){ echo $_COOKIE['LastLogin'];}?></label>

Conclusion

PHP is very straight forward to use and has plenty of help available online. Although it differs from the kind of coding I am used to, I must say I enjoyed coding in PHP. PHP code can get quite messy, but I think if one uses good variable naming standards the problems occurring using loosely typed languages could be reduced. Particular attention must be given to Character Casing as if a variable is called mistyping a letter or Case of a letter, a new variable could be formed, without th programmer even noticing. Variable naming standards help avoiding these errors. I think if large websites like Facebook, manage to work using PHP, this language can be used practically for anything.