Coursework 2: JavaScript Animation Game Spiced Up – Post 3

In my last post on this coursework I wrote about the flop I ended up doing by using XML instead of a database and concluded by stating I was going back to the drawing board use a database instead...well guess what? It ready!!!

So today I will post on how to connect and communicate with a MySQL database using PHP.

Using MySQL to store data

The first thing you want to do is download and install a MySQL database on the hosting server. I have already installed a MySQL instance when I installed XAMPP which I posted about a while back, so I will move on to the table I needed to store the user's data.

The users table fields and data types

As you might have noticed I did not include a user's position field as now I can use an SQL query to sort the user's out by their score. But I also added the 'DATE_JOINED' field, as I am planning to use this field in cases where more than one user has the same score. This will act as a second ordering field and sort those users according to the date and time they first signed up, which is automatically populated by the database as I've set the default value to a MySQL system variable, named 'CURRENT_TIMESTAMP'.

Connecting to a MySQL database with PHP

To connect to a MySQL database using PHP is very straight forward and can be achieved in a couple of lines, The code below illustrates and displays how it's done.

    $con = mysql_connect("localhost", "root", "MySQLAdmin") or die(mysql_error());
    mysql_select_db("source", $con) or die(mysql_error($con));

Code Breakdown:
The first line uses the 'mysql_connect()' function and passes the MySQL database location, user name and password. This will create the connection with the database and return a reference to that connection which I am storing in '$con'.


The second line is optional, but I decided to do it anyway as what this line does is select which database you wish to use. I named my database 'source' and passed it as a parameter, along with the reference I am storing in memory.


Querying a MySQL database with PHP
Once you have established a connection and want to execute a query, simply use the 'mysql_query()' function and pass the SQL statement as a parameter.
This function can be used not just for SELECT statements but also for UPDATE, INSERT, REPLACE and DELETE as well. The only difference is when you execute a SELECT statement, the resultset is returned, otherwise a boolean value gets returned.
The code snippet below shows how to use this function and how to fetch those results.

    
$result = mysql_query("SELECT USER_FULL_NAME, SCORE FROM USERS", $con)or die(mysql_error());

while($row = mysql_fetch_array($result, MYSQLI_ASSOC)){
      echo $row['USER_FULL_NAME'];
      echo $row['SCORE']."</br>";
}

Code Breakdown:
On line 3 I am using the 'mysql_fetch_array()' function which will return a row in an associative array. Then I use this array to output the results of my query. The while loop is simply used to loop through all of the rows in my resultset.


Disconnecting from a MySQL database with PHP
To disconnect is another simple matter or using just one line of code and the snippet below shows how it's done.

    
mysql_close($con);

The reference parameter I passed is optional, but this will limit the function to disconnect only that connection if more than one connection are open simultaneously.


Conclusion
So communicating with a MySQL database in PHP is quite easy work, but I didn't want to have to repeat this code every time I needed to run a statement, therefore I created a new PHP file and named it DBConnections.php and created my own functions which make use of the code above but in a more dynamic and reusable way.

    
<?php
function execute($sql) {

    $userConn = getConnect();
    $result = mysql_query($sql, $userConn) or die(mysql_error($userConn));
    
    disconnect($userConn);

    return $result;
}

function getConnect() {
    // Make a MySQL Connection
    $con = mysql_connect("localhost", "root", "MySQLAdmin") or die(mysql_error());
    //Set the schema which contains the table/s required
    mysql_select_db("source", $con) or die(mysql_error($con));

    return $con;
}

function disconnect($con) {
    //close the connection
    mysql_close($con);

    //unset this connection
    unset($con);
}
?>

Now all I have to do is include this PHP file wherever I need it by using the following line:

include 'DBConnections.php';

That's it for this post, please feel free to utilise the above code for your own needs. As for my next post I will cover how I managed to redirect to another location and post information by using only JavaScript.


Paper Toss Game Progress
In this section I will simply illustrate the progress made on the coursework so far.

Login Screen

The first page the user will see (i.e. - the index page) is the user login. From this screen the user can input his/her login details in order to access the game. Once the user clicks on the 'Login' button the inputs are validated via JavaScript and if validation is passed, the values get posted to the authentication page which makes use of the DBConnections.php file I described in the previous section, in order to retrieve the user's record by using the posted login details as criteria in the SQL query.
Also from this screen the user can tick the 'Remember Me' check box. For this I will implement the use of cookie variables in order to remember the user's login details in the future. New users can sign up by clicking on the 'Sign Up' button, this will redirect the user to an appropriate form but I will post on these last two features once I have finished implementing them.

Game Area after user login

Once The user is authenticated, he/she is granted access to the paper toss game which I had described in coursework 1. I have made some changes to this page, most noticeably is the background image, which gives a nicer look to the game (N.B. - I am not the owner of this image, I got it from Visual Photos), then I have added some buttons here on the lower left corner and the five paper balls at the top-right corner in preparation for the functions which I will implement in the following days.


P.S. - I will also change the paper ball and bin images to PNG files and remove their white background in order to get rid of the box around them.