Sunday, October 17, 2010

Reading the Database from PHP

You remember our "mailorder.html" file which had the shoe information hard coded. In the real world, we never work that way, always storing and retrieving information from a database. In order to get that done, we will have to dip our toes into PHP and MySQL. We have our database already set up at Go Daddy so that should make this exercise go faster. I am going to convert parts of the HTML file to PHP. Information that's going to become dynamic is item name, item number, item description, price, and item picture.

Let's take a look at the parts of the HTML file that we are going to convert:

</head>
<body>
<p><img src="jimmychoos.jpg" alt="Jimmy Choo Sandals" /></p>
<div class="itemorder">
<h2>Strappy Evening Sandals</h2>
<p>$275.00</p>
<p>Elegance and pain defined. Classic special-occasion heel crafted in 
metallic or suede leather. Cross-over straps at toe and double ankle strap
with gold metal self-fastening oval buckle. Suede lining and leather sole. 3 1/2" 
self-covered high heels that are sure hell to walk on.  Recommended use is for 
sitting down and not really for walking.  Made in the USA. Catalog/cssbakery.com only.
</p>

Name enclosed within h2's, price and description of the sandals enclosed within p tags are all hardcoded. The img tag has the name of the image file hardcoded.

itemname
imagepath
description

Our database table "primary_data" does not have a field for item name so I'm going to add that as "itemname". The phpMyAdmin interface makes this easy. Click on the name of the table, in my case, "shoe_primary_data" and choose "Structure" tab from above which shows all the fields of your table. Then to add a new field, just specify where it'll go in the table next to the "Add" option at the end of the table. Hit the Go button to add the new field. Repeat the same process with field names, imagepath and description.

Once you have the new field established, then we will have to set its value. For "itemname" field, we want to enter "Strappy Evening Shoes". We are again going to do this from the Godaddy interface but this time we are going to have to give it as a MySQL statement:

UPDATE shoe_primary_data SET itemname="Strappy Evening Shoes"

You enter the above SQL query from the SQL tab. For a complete MySQL reference, please see.

Since we only have one kind of shoe in our database so far, this statement will not cause problems but if we were to have several different types of shoes then it's important to write our SQL statement as follows:

UPDATE shoe_primary_data SET itemname="Strappy Evening Shoes" WHERE itemnum="10100"

which will only update the item names of those shoes where the item number happens to be 10100. Otherwise, you would set every shoe in your database to "Strappy Evening Shoes".

Here's the code that connects to the MySQL database from PHP. The mysqli_connect() API call returns an object that keeps track of the database connection. If mysqli_connect() runs into a problem and can't connect, then the $db variable will get set to false.

</head>
<body>
<?php
// make a connection to the mysql database server
$db = mysqli_connect("womenshoes.db.6787492.hostedresource.com","womenshoes", 
"mypasswordwhichstartswithaV", "womenshoes");
if ($db) {

With the connection established, we read from the database using a SQL SELECT statement. We limit the query to only records that have an item number of 10100:

//building a query string to send to the database, this will be the search string
  //Give me all the fields (star) from the shoe_primary_data table that have an item 
  //number of 10100
  $query = "SELECT * FROM shoe_primary_data WHERE itemnum='10100' ";

  //executes the search in the database using our query that we built
  //result object holds the results of the query
  $result = mysqli_query($db, $query);
  if ($result) {
     echo "Problem talking to a database";     
  }
  else die('Problem querying a database');
}
else die("Problem connecting to a database");

Here we check to see if the number of rows returned is exactly equal to 1. If there are zero rows, then we didn't find the item. If there are more than 1 rows, then the database is corrupted somehow, since this table should have a single row for each unique item number.

//returns the number of records that the query retrieved 
$count=mysqli_num_rows($result);

// we are only expecting to find one record for the item number we queried for
if ($count == 1) {
  //row is the record and we are getting it as an array
  //the parameter tells the API function to return the record as an associative array
  //ass array means the indices into the array are the field names of the table
  //indices are keys into the array
  //so instead of accessing the fields using row sub 0, row sub 1, row['description']
  $row=mysqli_fetch_array($result, MYSQLI_ASSOC);

  //printr will dump any variable whether it's an array, object or simple string
  print_r($row);
} else die("error error");
?>

With the database side of things settled, we can write our PHP code that's going to replace parts of the HTML.

<!-- php snippet is going to get totally replaced by the string from the database 
     so we need quotes outside the angle brackets
-->
<p><img src = "<?=$row['imagepath']?>" alt="Jimmy Choo Sandals" /></p>
<div class="itemorder">
<h2><?=$row['itemname']?></h2>
<p>$<?php  printf("%01.2f", $row['price']); ?><span class="floatr">Item #: 
<?php printf("%d",$row['itemnum']); ?></span></p>

<p class="description"><?php  printf("%s", $row['description']); ?></p>

I will use short tags to echo out the path name of the image (imagepath in our shoe_primary_data table). This PHP snippet will be totally replaced by the string from the database so it needs quotes around it to become the src file for the HTML img tag.

The imagepath field provides one of the keys we can use to access the PHP row associative array. Notice we are doing PHP right in the middle of HTML.

Then we are indexing the row array a second time with itemname field to get the name of the shoes. This should return the "Strappy Evening Shoes".

SHORT TAGS IN PHP

Let me just say a few things about short tags before we move on. When echoing out PHP variables, you can use the "short tag" form, so instead of writing <?php echo $x; ?>, you can just write <?=$x?>. The short tag form with the equal sign tells PHP to do an echo of the expression that follows. If you need to do a printf instead of echo, for example to format a floating point number, then you still have to use the long form, e.g. <?php printf("%02f",$x); ?>, but in cases where you just outputing the contents of the variable without formatting, then the short tag form works well and saves you some typing.

<p>$<?php  printf("%01.2f", $row['price']); ?><span class="floatr">Item #: 
<?php printf("%d",$row['itemnum']); ?></span></p>

<!- above line can be re-written as below: -->
<p>$<?php  printf("%01.2f", $row['price']); ?><span class="floatr">Item #: 
<?=$row['itemnum'])?></span></p>

CAN YOU EXPLAIN %01.2f?

In the PHP statement, printf("%01.2f", $row['price']); .2 means to put two decimal places. f is for floats. Let's say the value is 0.75. In that case, you don't want .75 so the zero one says to pad it with leading zeros for one decimal position before the period if it needs it.