Virtual Workshops

Part of keithjbrown.co.uk

Main Menu

• Server Environment
• MySQL
• PHP Databases
• Web Design
• Excel

Database Connectivity

Workshop Requirements

Before beginning this workshop ensure that you have the facility to upload PHP files to a PHP enabled webserver that has a MySQL database server installed. See Setting Up a Server for details on how to install one if you don't already have access.

You should also have completed the following Virtual Workshops:

• PHP Web Databases - Part 1
• PHP Web Databases - Part 2
• The MySQL Series of Workshops

You should also have some experience with HTML programming.

Where are the databases?

So I know that it says 'Web-based Databases' at the top of the page and I haven't even spoken about them yet, but that will change in this virtual workshop. In MySQL part 7 - Normalisation we looked at creating a database structure. In these PHP workshops we will use that structure with some sample data that you can download and install to your MySQL server.

A little bit of revision

In order to set up the database for use by PHP we need to revisit our MySQL experience.

To connect to our MySQL database, we had to launch the MySQL client program with certain parameters.

$ mysql -u <username> -p <password> <databasename>

Once connected, we need to create and set the appropriate user permissions for the database we are going to use (see MySQL part 6 - Administration for explanations of what is to follow). So start the MySQL command line software as the 'root' user and create the database (if you don't have the permissions to create a database, skip this step then use one that you have access to, remembering to substitute that name throughout).

mysql> CREATE DATABASE cdlibrary;

Next set the permissions for a user (called vworksusr in this example) that the the PHP script will use to connect to that database:

mysql> GRANT ALL PRIVILEGES 
    -> ON cdlibrary.* 
    -> TO vworksusr@localhost 
    -> IDENTIFIED BY 'vworkspass';

You can obviously change the username or password, again as long as you remember to substitute the values throughout. Next download the cdlibrary.sql file to the machine that has the MySQL server on it and we'll import that data (at the command line rather than within the MySQL client).

$ mysql -u root -p < /path/to/cdlibrary.sql cdlibrary

This should have now imported all the data into the database for use by PHP. There is one last thing that we should check before beginning: that the username and password does indeed allow us to connect to the database. Start the MySQL client again - this time using our newly created database and user.

$ mysql -u vworksusr -p vworkspass cdlibrary
Password:

Next enter a 'SHOW TABLES' command:

mysql> SHOW TABLES;
+---------------------+
| Tables_in_cdlibrary |
+---------------------+
| borrowed_cds        |
| borrower            |
| cd_releases         |
| cds_bought          |
| loans               |
+---------------------+
5 rows in set (0.00 sec)

And finally a simple SELECT statement:

mysql> SELECT releaseID, Artist, Title 
    -> FROM cd_releases;
+-----------+--------------------+-------------------+
| releaseID | Artist             | Title             |
+-----------+--------------------+-------------------+
|         1 | Britney Spears     | Britney           |
|         2 | Pink               | Cant Take Me Home | 
|         3 | Christina Aguilera | Stripped          | 
|         4 | Darius             | Dive In           |
|         5 | Will Young         | From Now On       |
|         6 | Robbie Williams    | Escapology        |
+-----------+--------------------+-------------------+
6 rows in set (0.03 sec)

If all of that works then we're ready to begin connecting to the database with PHP.

Getting Connected

PHP has many inbuilt functions, intended specifically for interacting with a MySQL database (as well as other database server technologies such as SQL Server, Oracle, etc). The first of these we are going to use is mysql_connect.

mysql_connect (server, username, password)

This establishes a connection to the database and returns (gives a result of) a connection reference. However, establishing a connection isn't much good on its own, and it is other PHP functions that use the connection to manipulate the database. Thus is it a good idea to store the reference to this connection in a variable that can be used as part of other functions. This also allows for some error checking to warn if the connection to the database has failed. So the code for connection is actually:

$link = mysql_connect (server, username, password)
or die ("Could not connect");

If the connection is successful, it is assigned to the variable $link, and if it fails returns the error message "Could not connect".

It is also important to include the code that closes the connection to the database, the mysql_close function, which again would use the connection reference:

mysql_close ($link);

First Contact

Let's now put this connection code into a PHP file called cdsbought.php and check that we can indeed make the connection.

<html>
<head>
<title>List of CDS</title>
</head>
<body>
<h4>Cds Bought</h4>
<?php 


$server = "localhost:3306"; // this is the server address and port
$username = "vworksusr"; // change this to your mysql username
$password = "vworkspass"; // change this to your mysql password


$link = mysql_connect ($server, $username, $password)
or die ("Could not connect");

?>

</body>

<? 
mysql_close ($link);
?>

</html>

NOTE: The PHP code is split into two separate sections of the page: the second section doesn't need the full <?php tag but a shorter<? tag as the PHP part was declared in the first section. Also, to a add comments to your PHP file, enter '//' and anything after that on a line is ignored (we'll look at comments in more detail in a moment).

If we upload this file and view it, all we will see is 'Cds Bought' as the connection is being created and closed without us doing anything with it. Let's deliberately create an error to see that working. Change the password to something that is wrong and re-upload the file. Viewing the file you should see something like:

Warning: Access denied for user: 'vworksusr@localhost' (Using password: YES) in /path/to/script/cdsbought.php on line 15

or

Warning: MySQL Connection Failed: Access denied for user: 'vworksusr@localhost' (Using password: YES) in /path/to/script/cdsbought.php on line 15
Could not connect

Which as we can see gives a couple of error messages that tell us what is wrong, and our die() message. This is a bit messy for any end user as there is a lot of information including the filename, path and the line on which the connection failed. We can make a nicer error message.

mysql_error()

This first thing we have to do is suppress the two error messages that are generated. This is done by preceding the connect statement with a '@':


$link = @mysql_connect ($server, $username, $password)
or die ("Could not connect");


This should result in the simple 'Could Not Connect' which is correct, but not very helpful. The other 'messy' messages said that access was denied using a certain password, and this would be good information to pass on in an error message. Luckily we can use another function called mysql_error() in place of the phrase 'Could not connect':


$link = @mysql_connect ($server, $username, $password)
or die (mysql_error());


This should print the more helpful message:

Access denied for user: 'vworksusr@localhost' (Using password: YES)

At this point reset the password in the script to the correct one.

Choosing a database from the server

Now we have a connection established we need to specify an Active Database to use and then begin retrieving data from it. The PHP function to connect to a specific MySQL database is mysql_select_db and is used like so:

mysql_select_db(database, connection)

We are using the function to pass information about which database and which connection to use. Again we will want to include some error checking and ensure that we can give a useful error back to the user. This time we are going to use a slightly different method of doing this, by using an 'if' control statement instead of die(). Most functions return two states on completion - true (if the function succeeds) and false (if the function fails).

Thus we can use an 'if' control to test for a function returning true:

if (function) {
	    do stuff here
}

or we could also test for a false return by using the 'not' operator '!' (see MySQL part One) if the function is 'not true'.

if (!function) {
    do stuff here
}

Anything between the braces '{ }' will be executed if the condition is met, which is better if we want to do more than one thing if an error occurs. In our example we want to run the function and check for a false return (failure), then give a message to the user. So beneath our connection code add the following:


$link = @mysql_connect ($server, $username, $password)
or die (mysql_error());

if (!@mysql_select_db("anydb", $link)) {   // leave the database 
                                           // name as anydb for now
                                           
     echo "<p>There has been an error. This is the error message:</p>";
     echo "<p><strong>" . mysql_error() . "</strong></p>";
     echo "Please Contant Your Systems Administrator with the details";
     
}


If you upload the file and view it you should see the error messages appear as there is not a database called 'anydb' in existence. Change that to the name of your own database and upload the file again. This time it should work.

So to summarise what is happening so far... we are establishing a connection to the MySQL server, setting an Active Database and then closing that connection again.

Querying the Database

At this point it is worth pausing to revisit SQL statements. The simplest data to display at this point comes from one table (that relating to the CD releases) and thus we can use a very simple SQL statement to retrieve this data (scroll to see the full results):

mysql> SELECT * FROM cd_releases;
+-----------+--------------------+-------------------+------+-----------+--------------+
| releaseID | Artist             | Title             | Year | Label     | No_of_tracks |
+-----------+--------------------+-------------------+------+-----------+--------------+
|         1 | Britney Spears     | Britney           | 2001 | Jive      |           14 |
|         2 | Pink               | Cant Take Me Home | 2002 | LaFace    |           15 |
|         3 | Christina Aguilera | Stripped          | 2002 | RCA       |           20 |
|         4 | Darius             | Dive In           | 2002 | Mercury   |           14 |
|         5 | Will Young         | From Now On       | 2002 | RCA       |           13 |
|         6 | Robbie Williams    | Escapology        | 2002 | Chrysalis |           14 |
+-----------+--------------------+-------------------+------+-----------+--------------+
6 rows in set (0.00 sec)

The next stage is to pass this SQL query to our database via PHP using the PHP function mysql_query().

mysql_query(sql_query, connection)

What we are going to do with this function is slightly different, as executing it will return three possible outcomes that we can interpret. These are 'true', 'false' and 'resource identifier '. The 'resource identifier' is returned when the SQL statement is a SELECT statement and thus there are some data resources that can be retrieved using a different function. In terms of checking for success, the other SQL statements, such as INSERT, UPDATE, DELETE etc are to do with changing the database and only return success or failure. So this time we need a piece of code that does check for success / failure but also stores the potential 'resource identifier' for use in other functions.


$result = mysql_query("SELECT * FROM cd_releases", $link);
if (!$result) {
  echo("<p>Error performing query: " . mysql_error() . "</p>");
  exit();
}


Thus a return value is stored in the variable $result and only if there is a failure (false value) is the error message generated. The exit() function quits the script so that no further code in the script is executed.

Displaying the Data

Our next step is to retrieve the result set from the stored 'resource identifier' using the function my sql_fetch_array().

mysql_fetch_array($result, MYSQL_ASSOC)

MYSQL_ASSOC - gives the fields their names rather than a number which represents their order (eg [0].[1],[2] etc) so that we can identify them more easily. This is the mechanism used to allow us to identify each column. We still however have multiple rows, so how do we handle that? The answer is to use another control structure called while which will continually process rows until no more exist

while (row exists) {
     do stuff
}

So if we add the result of the my sql_fetch_array() to a $row array, within a while loop we can output all the data in rows to HTML.


while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    echo($row["Artist"] . " ");
    echo($row["Title"] . " ");
    echo($row["Year"] . " ");
    echo($row["Label"] . " ");
    echo($row["No_of_tracks"] . . "<br>");
}


Upload the file and preview it. You should see all the CD information from the database, albeit not in the most user-friendly format. This can however be tidied up using standard HTML to create a table and putting each value in a different cell. See example and full code listing below.

Commenting the Code

Before leaving this section it's worth revisiting commenting and putting the comments in the code we have so far, before things get more complicated. Comments are text in the code that is there for the reader and is ignored totally by the PHP interpreter. There are many reasons why we should comment - my favourites are:

• Comments help me find areas in the code.
• If I return to code I developed after a period of time, the comments tell me what is going on.
• If I inherit code from someone, comments make things easier to understand the code, thus I feel I should do the same.

With this in mind let's look at how we insert comments in PHP code. You should have seen some comments in the examples above. Two forward slashes indicate that everything that follows that on a line is a comment and should be ignored.

// this is a comment on a single line

or

$myvar = "variable"; // this is a comment after some code on a line

We can also comment on multiple lines using a comment block. To open a comment block we use a forward slash and an asterisk '/*'. To close the block we reverse this, asterisk then forward slash '*/'. Thus as an example:

/* This is
is a comment
across multiple
lines */

You should go through the code in the cdbought.php file that you have and insert comments in the code (see the Full Code Listing below for examples). We will also use comments in the rest of the code that we will write in these workshops.

Full Code Listing

<html>
<head>
<title>CDS Bought</title>
</head>
<body>
<h4>Cds Bought</h4>
<?php 

/* set's the variables for MySQL connection */

$server = "localhost:3306"; // this is the server address and port
$username = "vworksusr"; // change this to your username
$password = "vworkspass"; // change this to your password

/* Connects to the MySQL server */

$link = @mysql_connect ($server, $username, $password)
or die (mysql_error());

/* Defines the Active Database for the Connection */

if (!@mysql_select_db("cdlibrary", $link)) {   
     echo "<p>There has been an error. This is the error message:</p>";
     echo "<p><strong>" . mysql_error() . "</strong></p>";
     echo "Please Contact Your Systems Administrator with the details";
}

/* Passes a Query to the Active Database */

$result = mysql_query("SELECT * FROM cd_releases", $link);
if (!$result) {
  echo("<p>Error performing query: " . mysql_error() . "</p>");
  exit();
}

/* Starts the table and creates headings */
?>
<table>
<tr>
<td><strong>Artist</strong></td>
<td><strong>Title</strong></td>
<td><strong>Year</strong></td>
<td><strong>Label</strong></td>
<td><strong>No of Tracks </strong></td>
</tr>
<?
/* Retrieves the rows from the query result set 
and puts them into a HTML table row */

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
     echo("<tr>\n<td>" . $row["Artist"] . "</td>");
    echo("<td>" . $row["Title"] . "</td>");
    echo("<td>" . $row["Year"] . "</td>");
    echo("<td>" . $row["Label"] . "</td>");
    echo("<td>" . $row["No_of_tracks"] . "</td>");
}

/* Closes the table */
?>
</table>
<? 

/* Closes Connection to the MySQL server */

mysql_close ($link);
?>

</body>
</html>

Lastest 10 Threads - view all

---

Post a Comment or Question

Name

Email

URL (optional)

Title of Comment

Comment
HTML Allowed:
a,code,strong,em

I would like to be e-mailed any response to my comment.

In this section

• Beginning PHP
• Passing data to PHP
• Database Connectivity
• Introducing Functions
• Updating Records
• A Whole Application
• Debugging

Related Reading

• PHP Manual

Related Books

Related Ads