PHP Exercise 4

PHP And MySQL Functions

Now we are ready to do some PHP and mySQL functions to look up data from our pet_store database that we created in the phpMyAdmin tutorial.
First we need to go over how PHP connects to a mySQL database. Take a look at the following code and notice how the mysql_connect() and mysql_select_db() fucntions are used.

// Performs the same as shell > mysql -u joe -p

$dbcnx = mysql_connect("localhost", "user_name","password") or die("Could not connect to the database server.");

// Performs the same operation as mysql > use pet_store

mysql_select_db("database_name",$dbcnx) or die("Can not select database");

Remember to replace user_name, password, and database_name with the username and password that has access to your database. In my example I gave in the phpMyAdmin tutorial, I would use the following:

// Performs the same as shell > mysql -u joe -p

$dbcnx = mysql_connect("localhost", "joe","joe") or die("Could not connect to the database server.");

// Performs the same operation as mysql > use pet_store

mysql_select_db("pet_store",$dbcnx) or die("Can not select database");

· Let me decode this for you so you can better understand how we did this. I assign a variable called dbcnx (Again - all variables in PHP start with the $ sign). I then used that reference and passed it to the mysql_select_db() function. I will show you how to implement these two functions in the next example.

If you get an error such as "Could not connect to the database server" or "Can not select database", then something is wrong with your username, password or database name. Maybe the username you supplied doesn't have permissions to access the database that you want to use.
The next thing we will probably want to do is lookup information in the animals table. Let's make a .html file and a .php file that will allow to search for a specific breed of dog.
Create a file called breed_lookup.html and make sure it's in the /var/www/html directory (as always):

<html>

<head>

<title>Input form for Breed Lookup</title>

</head>

<body>

<form action="breed_lookup.php" method="GET">

Dog Breed: <input type="text" name="breed_input"><br>

<input type="submit">

</form>

</body>

</html>

Next create a file called breed_lookup.php. The // indicate a comment. Also note that in the SELECT statement we need the " " around $_GET[breed] so that our query will find a result. Since you can't have quotes in PHP, PHP syntax calls for you put \" instead of " and then call the function stripslashes() to remove them.

<html>

<head>

<title>Breed Lookup Search Results</title>

</head>

<body>

<?php

// Connect to the mySQL server and select our database

$dbcnx = mysql_connect("localhost", "joe","joe") or die("Could not connect to the database server.");

mysql_select_db("pet_store",$dbcnx) or die("Can not select database");

// Now let's do our mySQL query to lookup the information

$query = "SELECT * FROM animals WHERE breed=\"$_GET[breed_input]\" ";

// We needed the slashes (\) to go with the PHP syntax, but

// to actually complete the query we need to get rid of them.

$query = stripslashes($query);

// Actually run the query

$result = mysql_query($query) or die(mysql_error());

// Return how many fields we selected with our *

$number_cols = mysql_num_fields($result);

// Set up the initial table and header rows

echo "<table border=\"1\" cellspacing=\"0\" cellpadding=\"5\">

<tr><td>Id</td><td>Species</td><td>Breed</td><td>Quantity</td><td>Price</td></tr>";

// This next line creates an array called $row which will

// contain all of the items in a row returned by the mySQL

// query

while ($row = mysql_fetch_row($result))

{

echo "<tr>";

for ($i=0;$i<$number_cols;$i++)

{

echo "<td>$row[$i]</td>";

}

echo "</tr>";

}

</body>

</html>

A sample output from a search for Black Lab is shown in Figure 6.3

Figure 6.3

In the SELECT * part of the code, we can replace the * with field names if you'd like. * Will select all fields from the rows it finds matches in. So for example if we don't care to display the ID number in our database to the world, then we should probably use this statement instead:

$query = "SELECT species,breed,quantity,price FROM animals WHERE breed=\"$_GET[breed_input]\" ";

When you make this change, remember to remove the following from your code or the data will not correspond to the column title above it:

<td>Id</td>

Basically you can do ANY mySQL function discussed in the mySQL manual (http://www.mysql.com) in this way. Anything you would type at a mysql > prompt, you can put in place of the SELECT statement. Just remember to put the \ in front of any quotation marks that would normally appear in a mySQL query.
Notice that unless you enter in the search criteria EXACTLY as it appears in the database, the search will not work correctly.

On Your Own Exercise 4

Create a HTML form and a .php file to UPDATE the quantity field for one of the items in your animals table. The HTML form should require the user to input the record number and new quantity to updated. The .php file should display all the OLD information from the table frist. Then it should display the NEW row in the database after the quantity has been updated. A sample out is shown below. You MUST comment your code to receive full credit!

Hint: Use the POST method in the method="" part of the form and variable array $_POST[] to access the user input.

Hint: This will require you to do three sets of $query/$result lines. The first one will simply display the old information (Do not UPDATE the information before displaying the first table). The second query will update the record. The third query is the same as the first query.

Hint: To find out the correct mySQL syntax for updating an item, go to phpMyAdmin and browse your animals table. Click on the edit link beside one of the rows and update the quantity field with a new number. The mySQL syntax will be displayed near the top of the following page.

On Your Own Exercise 4 - Sample Answer