Introduction to PHP: Hello World + MySQL

This tutorial builds on the previous tutorial – Introduction to PHP: Hello World and as such, you should take that tutorial first if you have not already.

In this tutorial, we are going to take our previous Hello World script, and store the date in a database. We will then add the last access time to our original script. We are going to use MySQL in this tutorial. I will assume that you already have MySQL and phpMyAdmin (or equivalent) installed.

The state of our script at the end of the last tutorial was as follows:

<?php
 
echo 'Hello World. Today\'s date is ',date('d/m/Y'),'.';
 
?>

Our database table will consist of two fields, `ID`, and `current_timestamp`. Both will be of type, integer (INT). You should make `ID` a primary key, and auto_increment. `current_timestamp` can be left with the default options.

For your convenience, I will provide a SQL query to create the table.

CREATE TABLE `last_access` (
`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`current_timestamp` INT NOT NULL
) ENGINE = InnoDB

You can run the above query in the SQL section of phpMyAdmin when viewing your database.

To connect to our database, we use two functions, mysql_connect, and mysql_select_db.

The first function, mysql_connect takes three parameters or arguments. These parameters are the host name (most of the time, localhost), and a username and password. These should have been provided by your web host. If you installed a server on your own computer, it is likely that username is root, with no password. We are going to place the mysql_connect function in a variable called $connection (be sure to change the parameters to suite your own specific situation):

1	$connection = mysql_connect('localhost', 'USERNAME', 'PASSWORD');

Next, we need to use mysql_select_db to access our specific database. I’ve called mine date, but you may have called it something different. If you have, change as appropriate. mysql_select_db takes two parameters. These are the $connection variable we just set and the name of your database.

1	mysql_select_db('date', $connection);

You can go ahead and place these functions at the top of our Hello World script.

Next, we are going to use the mysql_query function to insert the current time into our new database table. mysql_query only takes one parameter and this is an SQL statement. SQL stands for structured query language and is how we interact with our database within our applications. I won’t go into detail about SQL at this time, but for now, W3Schools offers a decent introduction.

Within our query, we will also use PHP’s time function. This function takes no parameters. It returns the current UNIX time stamp which is the number of seconds since the start of 1970.

Place the following in your script after the connection functions, and run it.

1	mysql_query('INSERT INTO `last_access` SET `current_timestamp` = \''.time().'\'');

Now, we are going to use the mysql_query function again to retrieve the last access time. This time we will place the mysql_query function into a variable, so we can access the results.

1	$result = mysql_query('SELECT `current_timestamp` FROM `last_access` ORDER BY `current_timestamp` DESC LIMIT 1');

Now, we want to be able to retrieve the data our SELECT query is retrieving. We can do this with, mysql_fetch_assoc. It takes one parameter, and this is our query variable which we named $result. Like mysql_query, we will also place this into a variable.

1	$row = mysql_fetch_array($result);

Place the SELECT query before the INSERT query but after the connection information.

Now, all the pieces are in place to put our final script together.

Your script should look like this at this point:

<?php
 
$connection = mysql_connect('localhost', 'USERNAME', 'PASSWORD');
mysql_select_db('date', $connection);
 
$result = mysql_query('SELECT `current_timestamp` FROM `last_access` ORDER BY `current_timestamp` DESC LIMIT 1');
$row = mysql_fetch_array($result);
 
mysql_query('INSERT INTO `last_access` SET `current_timestamp` = \''.time().'\'');
 
echo 'Hello World. Today\'s date is ',date('d/m/Y'),'.';
 
?>

Now, we are going to add the current hour, minute and second to our date function in our echo language construct. The letters for these are h (hour), i (i since m is reserved for month, the current minute) and s (seconds).

1	echo 'Hello World. Today\'s date is ',date('d/m/Y h:i:s'),'.';

Next, we want to display the last access date. To do this, we are going to add another echo language construct. Since row only returned one piece of data due to LIMIT 1, we can use it just like we would an array (we will discuss arrays in a later tutorial). For now, all you need to know is that they are used like variables but can store more then one piece of information.

1	echo ' This script was last accessed at ',date('d/m/Y h:i:s', $row['current_timestamp']),'';

You will notice this date function is taking a second parameter. This changes the output of date from the current time/date to the time and date which it was when the timestamp was recorded.

Our final script looks like this:

<?php
 
$connection = mysql_connect('localhost', 'USERNAME', 'PASSWORD');
mysql_select_db('date', $connection);
 
$result = mysql_query('SELECT `current_timestamp` FROM `last_access` ORDER BY `current_timestamp` DESC LIMIT 1');
$row = mysql_fetch_array($result);
 
mysql_query('INSERT INTO `last_access` SET `current_timestamp` = \''.time().'\'');
 
echo 'Hello World. Today\'s date is ',date('d/m/Y h:i:s'),'.';
echo ' This script was last accessed at  ',date('d/m/Y h:i:s', $row['current_timestamp']),'';
 
?>

So that concludes this tutorial. As usual, comments, suggestions and most importantly, questions, are welcome.

This entry was posted on Tuesday, June 30th, 2009 at 3:57 PM by Chris Clarke and is filed under PHP, Tutorials. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.