SYST28043 - Using MySQL Databases

Resources for this Lesson

Database Concepts Overview

Before we define what a database is, we should start from the bottom of what is commonly called the data hierarchy. The data hierarchy shows the different components that make up a database. You will encounter these compnents repeatedly throughout most of your courses that deal with programming, database development and implementation, and even systems analysis.

It's easier to explain how the data hierarchy works when you start from the bottom.

Fields

If you examine the image to the left, you'll see a number of fields at the bottom of the screen. A field is a piece of information that describes an entity. For example, as a student entity, fields that describe you would be first name, last name, student number, the name of the program you're enrolled in, the semester you're in, etc. Each field is one piece of information. You can't make a field that describes more than one thing. For example, you can't have a "grades" field because you have more than one grade. However, you could have a field for the grade you received in a specific course, such as your grade for PROG10082.

A field doesn't always have to have a value. Some fields can be empty. For example, if you register at the college but haven't been accepted yet, the value for the field that defines the term you're in would be empty, because you haven't started school yet. On the other hand, some fields must have a value. For example, every student must have a student number, and you wouldn't be allowed to have information in a database on a student if the student had no student number.

Records

A group of fields that are related and describe one element in an entity make up a record. For example, the fields student number, first name, last name, address, phone number, and program code could all be the fields in a student's record. Each record describes one element, or one item. For example, a student record describes one specific student, and an inventory record describes one specific item in inventory.

Tables

A table is a group of related records that describe a particular entity. For example, a group of student records would describe all the students in the college, and would probably go in the Student table. A group of customer records would go in the Customers table, because they all pertain to the company's customers.

A table always has a specific structure, and each record in a table must follow that structure. For example, the structure of the Students table could look like this:

Table Name: Students
Field DescriptionData TypeSizeComments
student numberString9primary key
first nameString50 
last nameString50 
program codeString6foreign key - Programs
current g.p.a.Floatn/a 

In this simplified example, each record in the table contains five fields. Most of the fields are Strings whose field size is identified by the number of characters. The one numeric field Float has n/a as its size because non-string values are usually of a fixed size (for example, an integer is Java is 8 bytes).

A table may or may not contain records. For example, a table that is created and opened for the first time will probably be empty, or a table that records a day's transactions in a store will be empty at the start of the day when the store opens. In addition, with many tables the programmer can't possibly know how many records will be in the table at any given time. Most tables will allow a user to add and delete records as needed, so the number of records in a table is dynamic and changes. As a programmer for a database, you would need to take all these things into account.

Databases

A database is a collection of tables, and those tables are usually related in some way. For example, a small, independent coffee shop might have a database with tables called Suppliers, Inventory, PriceLists, and Employees. The Suppliers table will contain records for each of the companies the coffee shop uses to purchase supplies, utilities such as hydro and water, and raw materials (such as coffee beans, flour for pastries, etc). The Inventory table would contain records for each of the items, products, and raw materials the coffee shop has in inventory at any given time. A typical Inventory record might contain fields for the item identification (we'll talk more about this in a moment), item description, quanitity on hand, re-order point (how many items you gets down to before you must re-order more of this item), and supplier idenfier (so you know where to find the supplier information for this product in the Suppliers table).

Primary Key Fields

Each table must have a special field called the primary key. A proper table in a relational database must not contain any duplicate records. For example, what if you had two customers both named Fred Smith, and one of the Freds phoned and wanted information about their account balance. You could probably ask for some kind of identifying information like an address (what if they were father and son and lived in the same house?) or a birth day (what if they were room mates with the same name who happened to have the same birthday?) but, however unlikely it is that these things would be the same, it's still possible. As a programmer, NEVER assume that just because something is unlikely, it will never happen! What if the two Fred Smiths were actually the same person who somehow got added twice to the same Customers table? This is also possible.

To avoid duplicate records, and to ensure that two records which appear similar are in fact different, we use primary key fields. A primary key is a special field that uniquely identifies each record. For example, no student in the college has the same student number as any other student, so that is used as the primary key in the student table. Books have an ISBN (international standard book number) which is unique for every book that is published by a certain publisher, so that could be used as a primary key in a Books table.

When you're designing some tables, it might not be obvious what the primary key should be. In a lot of these cases, you may need to make one up. This is how things like Product I.D.'s and Supplier I.D.'s get created. In one of your systems analysis courses, you'll probably spend a bit of time learning about how to make up a good format for a primary key field or I.D. field.

Sometimes you'll have a field that is actually a primary key from another table. This is called a foreign key and is used as a reference to the other table. For example, in your coffee shop's inventory table, each record might contain a Supplier ID field. When you need to get the supplier contact information because you've run out of a product, you look up the inventory item's Supplier ID in the Suppliers table to find out who this supplier is. We do this to avoid redundant data - it would be a waste of space to store all the supplier's information with each inventory item!

Exercises

See how well you understood the concepts you've read so far!

  1. Besides Students, what other tables do you think would exist in a database for Sheridan College?
  2. What structure would a table called Mp3s that you would use to keep track of all your MP3 files?
  3. Make up three examples of records with field values for the Mp3s table in #2.

Starting with MySql

SQL stands for Structured Query Language and is probably the most popular tool used to create and manage databases today! Programs like Microsoft Access, Paradox, and FoxPro can allow you to use SQL to create databases, tables, fields, and to populate records with field values. You can even do these things in Java. When working with PHP and XHTML forms, you can use SQL to store your form data in database tables, modify or delete those records, or even look up information that was entered previously. We use the MySql server for this, because it runs on our laptops, however, the SQL you learn here (and you'll learn much more of it next year in the database courses) you can use in Microsoft Access, or with Oracle, or anywhere else you have access to SQL.

This quick tutorial will allow us to set up some tables and data that we can use with PHP and XHTML forms. This is by no means a complete lesson on how to use SQL (you will take two or three courses on that next year!) but it's just enough to allow you to do simple things with PHP and XHTML forms.

Creating a Database

First, we need to set up a database:

  1. Go to localhost and log in using the user name and password you set up in the lesson where we installed XAMPP.
  2. In the tools menu, click on phpMyAdmin.
  3. Log in using the root account, and the password you gave the database root account in the lesson where we installed XAMPP.

    In the main PHP Admin screen, the left side will show a list of the databases already on your SQL server. You will see databases with names like mysql and performance_schema. You shouldn't touch these databases, as they're needed by the database server and web server.

    main screen in phpmyadmin

    Across the top you'll see various tabs or buttons that allow you to view and configure items on the server. Click on the Databases tab so we can add a new database and put some data in it.

    First we'll create a new database (make sure you're in the Databases area):

  4. Underneath the Databases heading on the main part of the page, you should see the "Create Database" field.
  5. In the text field, type Media.
  6. [optional]In the "Collation" combo box, locate and select "latin_general_ci".
  7. create new db

  8. Click the Create button.

Once you've created the media database, you'll next be asked to add the first table to the database.

Creating a Table

The next step is to create a table for our database. First we're going to create the table, then add columns by creating a schema.

  1. On the screen that appeared after creating the Media database, you should see fields that allow you to enter information for a new database table.
  2. In the Name: field, type Cds.
  3. In the Number of fields: box, type 4.
  4. Click the GO button.
  5. create cds table

    On the next screen you'll see a table of fields where you can enter your column schema. We are going to use this to create the following table structure:

    Table Name: Cds
    Field DescriptionData TypeSizeComments
    idintegern/aprimary key
    titleString50 
    artistString50 
    pricefloatn/a 

  6. In the first row, under the Field column, type id.
  7. Under the Type column, select "int" from the combo box.
  8. In the Index field, select "Primary". This makes ID the primary key field.
  9. Scroll horizontally to the "A_I" column check the empty box so that it's checked. "A_I" means "Auto Increment. This means the ID field will increment itself for each new record added.

Once you've set up the first field, your screen should look similar to the one below:

adding a field

Now, create the remaining three fields in the same way:

When you're finished, your page should look like this one:

done adding fields

When finished, click the "Save" button.

If everything was successful, you'll briefly see a screen with the progress and then a message that the table was created successfully. The next screen shows the list of tables in the Media database, and you'll see there's just the one we created a few moments ago.

table creation successful

To see the structure of the table, click on "Structure" under the "Action" heading.

viewing structure of completed table

Adding a Guest User

When you first installed and setup XAMPP, you set the root account password for your MySql Server. This is not the account you want your web site visitors to use when they connect to your database! The root account has complete access to your database server; this is too much access for your visitors. Instead, you should create a guest account with limited access that would be more appropriate for your web site visitors.

Go back to the main PHPMyAdmin page (click the Home button at the top-left of your admin page or click the "localhost" breadcrumb at the top of the page).

  1. Select the "Users" tab at the top of the page.
  2. This shows you the User table containing the current user accounts. Under this table, click the link "Add User".
  3. Fill in the login information. You'll need to make up a guest a ccount name (the example below used "userGuest", and a password.
  4. For the "Host" field, select "Local".
  5. Scroll down to the "Global Privileges" section in the sub-section called "Data", check off SELECT, INSERT, UPDATE, and DELETE. Nothing else should be checked.
  6. [optional]Scroll down and set the limits for this account.
  7. After checking your screen with the image below, click the "Add User" button in the bottom-right corner of the screen.
  8. add a guest user

    add a guest user

  9. For this tutorial, we won't worry about the other settings, but further readings in the references listed at the top of this page will give you more information about users and security that you will need for more complex programs.

NOTE This guest user account actually has even more permissions than I'd usually give the temporary guest. Normally the only permission I'd give is the SELECT permission, which would allow the person to provide their user name and password and log in. Guests normally wouldn't need to be able to UPDATE, DELETE, or INSERT. Only authenticated users would be able to have those permissions. However, since we're going to use this account to learn how to perform updates, deletions, and insertions, we'll need these permissions on this account.

Database Access with PHP

Now that we have a database with a single table, we'd like to create an XHTML form that allows us to add records to the table. To do this, we need to create the XHTML form that allows the user to enter the data, then we need to write the PHP program that accesses the database and saves the data to the proper tables.

First, we need an XHTML form. Open up a new XHTML file in your editor and create the following form:

Make sure you set the form attributes method="post" and action="addRecord.php". We'll write the addRecord.php code in a moment.

Now open up a new PHP file in your editor. At the top of the page, under the <?php statement but above the echo "<!DOCTYPE.. element, add three variables for your host name, guest account name, and guest account password. For example:

<?php
$user="userGuest";
$passwd="tunafish414";
$hostname="localhost";

echo "<!DOCTYPE 
...

Connecting to the Database Server

In any programming or scripting language, you will need to establish a connection with your database or database server. In PHP this is done with the mysql_connect() function:

$dblink = mysql_connect($hostname, $user, $passwd)
	or die ("Error: No connection to MySQL server\n");

In this example, we create a connection to the database server on the given hostname (for us this will be "localhost") for a particular user and password. The mysql_connect() function takes the host name, user name, and password as arguments and it returns a reference to the database connection. This connection reference is stored in the variable $dblink.

The second line of the statement is a condition that prints an error message if the database connection cannot be made successfully.

Selecting a Database

As you saw earlier, there is more than one database on the database server. When you establish a connection to a database server, you must make sure you're program is accessing the correct database. We do this in PHP using the mysql_select_db() function:

mysql_select_db($dbname, $dblink)
	or die ("Error: MySQL database not selected\n");

The mysql_select_db() function accepts a string database name as the first argument, and the database connection as the second argument. The function will set the current database to $dbname, and it will use the connection referenced by the $dblink variable.

As with the previous example, the second line will display an error message if there is a problem selecting the database to use.

Run a Query

To run a select query on our database, we use the PHP function mysql_query(). The mysqu_query() function accepts an argument that contains an SQL query string, and an argument for the connection over which this statement should execute:

$result = mysql_query($sql, $dblink)
	or die ("SQL query failed: $sql<br />".mysql_error());

In this example, we run the query we defined in the $sql variable earlier through the database connection we created. If the query fails, we display a message and the query itself, plus the results of the mysql_error() function. This function returns any error message that came from the database when it tried to execute your query.

If the SQL statement is a SELECT query, the $results variable will contain the set of records returned from this query. If the SQL statement is an INSERT, UPDATE, or DELETE command, the $result variable will contain the true if the query was successful and false if the query was unsuccessful.

Inserting Records

Now that we know how to connect to the database and execute commands, let's try inserting a record based on the user input on the form.

In your PHP file, place the code inside the <body> tag to connect to your database and set the current database to Media:

$dblink = mysql_connect($hostname, $user, $passwd)
	or die ("Error:  No connection made.\n");
mysql_select_db("Media", $dblink)
	or die ("Error: MySQL database not selected\n");

The next step is to retrieve the data from the form and use it in an INSERT query that we can then send to the database. On the form, we had three text input fields for the title, artist, and price. Note that we don't have a field for the id, because that's an auto-number field and will be filled automatically.

Your form's method attribute was set to "POST". In this case, you can access the form data in the PHP file by using the $_POST[] associative array. This array contains elements where they key is the input field's name attribute value, and the value of the element is the contents or value of the input field. For example, in our program, the element keys will be "title", "artist", and "price". Note that this currently only works with an input element's name attribute, not the id attribute.

You might use client-side validation to check that your fields have values before they are sent to the PHP program, but it's still a good idea to check your fields to make sure they have something in them. You can use the isset() method to check for form field values. The isset() method accepts a variable and returns true if that variable does not contain a null value. For example, isset($_POST["title"]) returns true if the user typed something in the title field. If the user typed nothing, then the isset() method returns false.

It's common to use isset() with the conditional operator to check for and retrieve form values:

$cdTitle = (isset($_POST["title"])) ? $_POST["title"] : "";

The above statement checks the title input from the form. If it was set, then the form's title data is stored in the $cdTitle variable. Otherwise, a null-string is stored in the $cdTitle variable.

Before the code where you connect to and select the database, add three statements like the one above to check for and retrieve the cd title, artist, and price from the form.

Now that we have our data and we have a database connection, we need to construct and execute the INSERT query. The SQL INSERT statement has the general form:

INSERT INTO tableName (field1, field2, field3, ...) 
     VALUES (fieldValue1, fieldValue2, fieldValue3...);

For example, to insert a record into our Cds table, I might type:

INSERT INTO Cds (title, artist, price) VALUES ("Bought the Farm", "Captain Tractor", 12.99);

The field names in the first set of brackets have to match or correspond to the values in the second set of brackets. In other words, the title field corresponds to the value "Bought the Farm", the artist field will get "Captain Tractor", and price will get 12.99.

Your next step is to build a query like the one above, using your form data. You can subsitute single-quotes for the double quotes in the SQL statement, since your whole string will already be enclosed in double-quotes:

$sql = "INSERT INTO Cds (title, artist, price) VALUES ('$cdTitle', '$cdArtist', $cdPrice);";

Note that we don't put quotes around price, because it's numeric.

Once you create your SQL query string, execute it using the mysql_query() function:

$result = mysql_query($sql, $dblink)
	or die ("SQL query failed: $sql<br />".mysql_error());

The $result variable should contain the value true if your query was successful, so you can now add an if statement that prints a confirmation message on the screen:

if ($result) {
	echo <<<SUCCESS
	<h3>Record Added Successfully</h3>
	<p>Your record was sucessfully added to the database.</p>
	<hr>
	<p><a href="index.html">Add another record</a></p>
SUCCESS;
} else {
	echo "<p><b>Error.</b>  Please see your database administrator.</p>\n";
}

The entire code for both the main form and the PHP script are shown below:

The form: index.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html  xmlns="http://www.w3.org/1999/xhtml">
<head>
	<title> Add CDs to Database </title>
	<meta name="generator" content="editplus" />
	<meta name="author" content="Kaluha" />
	<script type="text/javascript">
	<!--
		function validateFields() {
			err = true;
			title = document.getElementById("title");
			price = document.getElementById("price");
			if (title.value == "")
			{
				alert("CD Title must contain a value.");
				err = false;
			} else if (price.value < 0)
			{
				alert("CD Price can't be a negative value.");
				err = false;
			}
			return err;
		}
	// -->
	</script>
	<style type="text/css">
		<!--
		body { font-family: Verdana,Arial,sans-serif; }
		label { font-weight: bold; }
		-->
	</style>
</head>

<body>
	<h3>Add Cds to the Database</h3>
	
	<form method="POST" action="addRecord.php" onSubmit="return validateFields();">
		<p><label for="title">Title:</label>
		<input type="text" id="title" name="title"><br />
		<label for="artist">Artist:</label>
		<input type="text" id="artist" name="artist"><br />
		<label for="price">Price:</label>
		<input type="text" id="price" name="price"></p>
		<p><input type="submit" value="Save" />
		<input type="reset" value="Clear" /></p>

	</form>
</body>
</html>

The PHP file: addRecord.php

<?php
$user="userGuest";
$passwd="tunafish414";
$hostname="localhost";

echo <<<HEADER
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html  xmlns="http://www.w3.org/1999/xhtml">
<head>
<title> Record Added </title>
<meta name="author" content="Kaluha" />
</head>
<body>
HEADER;
	
$dblink = mysql_connect($hostname, $user, $passwd)
	or die ("Error:  No connection made.\n");
mysql_select_db("Media", $dblink)
	or die ("Error: MySQL database not selected\n");
$cdTitle = (isset($_POST["title"])) ? $_POST["title"] : "";
$cdArtist = (isset($_POST["artist"])) ? $_POST["artist"] : "";
$cdPrice = (isset($_POST["price"])) ? $_POST["price"] : 0;

$sql = "INSERT INTO Cds (title, artist, price) Values ('$cdTitle',  '$cdArtist', $cdPrice);";
$result = mysql_query($sql, $dblink)
	or die ("SQL query failed: $sql<br />".mysql_error());

if ($result) {
	echo <<<SUCCESS
	<h3>Record Added Successfully</h3>
	<p>Your record was sucessfully added to the database.</p>
	<hr>
	<p><a href="index.html">Add another record</a></p>
SUCCESS;
} else {
	echo "<p><b>Error.</b>  Please see your database administrator.</p>\n";
}
echo "</body>\n";
echo "</html>";

?>

Displaying Records on a Web Page

Now that we've had an opportunity to create a database with a single table and add records to it, we can design a PHP program that views records from that table. To do this, we need to connect to the database server, select the database want to use, create an SQL SELECT query, and display the results returned by the query.

To start, set up a new PHP file in your editor and add some variables at the top of your code, above the <!DOCTYPE statements:

$hostname = "localhost";
$user = "userGuest";	 // put your user guest account here
$password = "tunafish";		// put your guest account password here
$dbname = "Media";
$sql = "SELECT * FROM Cds;"

The $sql string above contains a SELECT query that retrieves all of the data from the Cds table. The * means "all fields". Since we didn't say which records we wanted, all the records will be returned (if there are any at all).

Next, add the code to connect to the database server and select the Media database:

# Establish a connection to the MySQL database
$dblink = mysql_connect($hostname, $user, $passwd)
	or die ("Error: No connection to MySQL server\n");
mysql_select_db($dbname,$dblink)
	or die ("Error: MySQL database not selected\n");

Now we can execute our query. Add the code to execute the query stored in $sql and put the results in a variable called $results:

$result = mysql_query($sql, $dblink)
	or die ("SQL query failed: $sql<br />".mysql_error());

Getting Query Results

Recall that if your SQL statement is INSERT, UPDATE, or DELETE, the $result variable contains a boolean (true if the query succeeded; false if it was unsuccessful). If your query is a SELECT statement, the $result variable references the set of records returned by the query.

To see how many records your query returned, you can use the mysql_num_rows($result) function. This function accepts a query result and returns an integer representing the number of rows in the result set.

# Determine how many records are in the result set
$rows = mysql_num_rows($result);

Try this out by adding this statement to your code and testing your page on your web server.

To access the individual rows of the result set, you need to use a function such as mysql_fetch_array(). This function accepts a resource, such as a result set, as an argument and returns a single associative array. This array's keys are the field names (as defined by your table(s) or query) and each element contains that field's value. As the mysql_fetch_array() function executes, a pointer is moved ahead to the next row. Each time you execute the function, the next row is returned as an associative array and the pointer moves ahead. If your pointer moves past the last row to the end of the result set, the function will return the boolean value false the next time it's invoked. This function will also return false if you attempt to use it the first time on an empty result set.

Example:

$myRecord = mysql_fetch_array($result);
echo "Title: ".$myRecord["title"]." by ".$myRecord["artist"]."<br />\n";

Displaying the Results

We will display the results in a table, but if there were no records returned by our query we should display a message. You can use an if statement to test $rows - if it's 0, display a message, otherwise, display the table:

if ($rows <= 0) {
	echo "<p>Sorry, no records to display!</p>\n";
} else {
	# display the table of records here
	....
}

The rest of our code will go inside the else block. We'll start by beginning our table code:

echo "<p><table border='1' cellpadding='2' cellspacing='0'>\n";
echo "<tr><th>ID</th><th>Title</th>\n";
echo "<th>Artist</th><th>Price</th></tr>\n";

Next, we want to use a loop to display the records. Since you know the number of rows, you can use a for-loop. Alternatively, you can use a while loop:

while ($record = mysql_fetch_array($result))
{
	# print the id
	# print the title
	# print the artist
	# print the price
}

In the while condition, we invoke the mysql_fetch_array() function, which stores the resulting record in the $record variable. If the function returns false, the while loop terminates.

We want to retrieve each field value and place it in an HTML table cell, so we have to use an echo statement to display the HTML tags and concatenate the field values:

echo "<tr><td>".$record["id"]."</td>";
echo "<td>".$record["title"]."</td>";
echo "<td>".$record["artist"]."</td>";
echo "<td>".$record["price"]."</td></tr>\n";

After the loop, write the echo statements to close the table tags and display the number of records printed:

echo "</table></p>\n";
echo "<p>$rows records displayed.</p>\n";

If you load the page into your browser, you should see the table of records from your Cds table of your Media database.

Viewing Selected Records

Sometimes you may need to design a page that allows the user to view only certain records. For example, we're going to set up a page that allows the user to search for Cds that fall between a minimum and maximum price range:

search form

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html  xmlns="http://www.w3.org/1999/xhtml">
<head>
	<title> Search by Price </title>
	<meta name="author" content="Wendi Jollymore" />
	<script type="text/javascript">
	<!--
		function validateFields() {
			err = true;
			min = parseFloat(document.getElementById("min").value);
			max = parseFloat(document.getElementById("max").value);
			if (min < 0)
			{
				alert("You must enter a minimum value.");
				err = false;
			} else if (max < 0)
			{
				alert("You must enter a maximum value.");
				err = false;
			} else if (min >= max)
			{
				alert("Minimum value must be less than maximum value.");
				err = false;
			}
			return err;
		}
	// -->
	</script>
	<style type="text/css">
		<!--
		body { font-family: Verdana,Arial,sans-serif; }
		label { font-weight: bold; }
		-->
	</style>
</head>

<body>
    <h2>Search by Price Range<>
	<form method="post" action="search.php" onSubmit="return validateFields();">
		<p><label for="min">Minimum Price:</label>
		<input type="text" id="min" name="min" /><br />
		<label for="max">Maximum Price:</label>
		<input type="text" id="max" name="max" /></p>

		<p><input type="submit" value="Search" />    
		<input type="reset" value="Clear" /></p>

	</form>
</body>
</html>

Next, start a new PHP file called search.php and enter the following code:

<?php
$user="userGuest";
$passwd="tunafish414";
$hostname="localhost";
$dbname="Media";

echo <<<HEADER
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html  xmlns="http://www.w3.org/1999/xhtml">
<head>
<title> Search Results </title>
<meta name="author" content="Kaluha" />
</head>
<body>
HEADER;

echo "</body>\n";
echo "</html>";

?>

We now need to connect to our database and execute the SELECT query. We still want to retrieve all fields, but we also want to filter our results so that we only get records where the price is greater than or equal to the minimum and less than or equal to the maximum. For this, we add a WHERE clause to our query:

$sql = "SELECT * FROM Cds WHERE price >= $min AND price <= $max";
Preventing Injection Attacks
Read SQL Injection Prevention. This is an important part of developing PHP/MySQL web applications. Note that the mysql_real_escape_string() is older. At some point you'll want to read about the MySQL Improved Extension and the mysqli_real_escape_string() function.

In the <body> tags of your page, write the code to:

  1. connect to the database server
  2. select the Media database
  3. retrieve the min and max values from the form
  4. make up the SELECT query and store it in the variable $sql
  5. execute the query
  6. get the number of rows returned
  7. if rows were returned, display the results in a table on the screen (similar to the previous program)
  8. if there were no rows returned, display a "No matching records found." message on the page with a link to the search page so user has the option of searching again

Exercise

Design an XHTML form and write the necessary PHP code that asks the user to search for a CD with a certain title or artist.


Code for Max/Min Price Exercise:

<?php
$user="userGuest";
$passwd="tunafish414";
$hostname="localhost";
$dbname="Media";

echo <<<HEADER
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html  xmlns="http://www.w3.org/1999/xhtml">
<head>
<title> Search Results </title>
<meta name="author" content="Kaluha" />
</head>
<body>
HEADER;

$dblink = mysql_connect($hostname, $user, $passwd)
	or die ("Error:  No connection made.\n");
mysql_select_db($dbname, $dblink)
	or die ("Error: MySQL database not selected\n");

$min = isset($_POST["min"]) ? $_POST["min"] : 0;
$max = isset($_POST["max"]) ? $_POST["max"] : 0;

$sql = "SELECT * FROM Cds WHERE price >= $min AND price <= $max";

$result = mysql_query($sql, $dblink)
	or die ("SQL query failed: $sql<br />".mysql_error());
$rows = mysql_num_rows($result);

if ($rows > 0) {
	echo "<h3>View Results</h3>\n";
	echo "<p><table border='1' cellpadding='2' cellspacing='0'>\n";
	echo "<tr><th>ID</th><th>Title</th>\n";
	echo "<th>Artist</th><th>Price</th></tr>\n";

	while ($record = mysql_fetch_array($result))
	{
		echo "<tr><td>".$record["id"]."</td>";
		echo "<td>".$record["title"]."</td>";
		echo "<td>".$record["artist"]."</td>";
		echo "<td>".$record["price"]."</td></tr>\n";
	}
	echo "</table></p>\n";
	echo "<p>$rows records displayed.</p>\n";

} else {
	echo "<p>No matching records found.  <a href='search.html'>Search Again?</a></p>\n";
}
echo "</body>\n";
echo "</html>";

?>