Thursday, October 21, 2010

Looping through Multiple MySQL Records with PHP

Back to Godaddy to add a record to our database table, shoe_secondary_data. There are several ways to add the new record. I tried the phpMyAdmin interface where you fill in the blanks from their insert function. Same item number for the strappy Jimmy Choo shoes, size 7W in gold of which we have 3 in our inventory. Leave the first field empty (NULL), it's self incrementing :

When you hit return, the system formulates a MySQL query and updates the database with your changes (the color should be gold here):

Browsing our shoe_secondary_data table contents:

So given that database table, we are now going to update the hardcoded HTML in our PHP file by pulling in the data from shoes_secondary_data. I commented all the steps I took in the program so it should be easy to follow. I could have deleted the HTML but kept it so that you can compare the old and the new:

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

  //executes the search in the database using our query that we built
  //result object holds the results of the query
  //this query gives us the records for the  secondary table (shoe_secondary_data)
  $result2 = mysqli_query($db, $query);
  if ($result2) {
     echo "You know how talk to a database";     
  }
  else die('You dont know how to query a database');

/* We are going to loop through the shoe_secondary_data results and construct
   an array of arrays which you can think of as the set of records returned from 
   the database
*/   
$count=mysqli_num_rows($result2);
$nested_array=array();
for ($i=0; $i<$count; $i++) {
   //empty brackets means append to the array
   $nested_array[]=mysqli_fetch_array($result2, MYSQLI_ASSOC);
}
//debugging 
print_r($nested_array);

// Create an array for each Drop Down Select box:
$size_array = array();
$width_array = array();
$color_array = array();
// Now loop through the records and construct the size, width,
// and color arrays.
foreach($nested_array as $rec) {
  // Get the values from this record:
  $size = $rec['size'];
  $width = $rec['width'];
  $color = $rec['color'];
  // We use PHP associative arrays to create unique lists of values. 
  // 
  // Here's how this works for "size" for example: we may have many records 
  // that have size 6, but we only want one "6" in the size_array.  We 
  // accomplish that by using $size as the key into the array.  
  // 
  // So when we encounter the first record with a 6, we index into the array with
  // a key of 6, thus creating a new entry in the array: $size_array[6], and
  // we set the value of that entry to 6.  Now when we see another record that
  // also has size 6, we again index into the array using a key of 6 
  // ($size_array[6]) and set the value again to 6 (so we overwrite value 6
  // with 6, so it really doesn't change).
  // 
  $size_array[$size] = $size;
  $width_array[$width] = $width;
  $color_array[$color] = $color;
}
//debugging 
print_r($size_array);
print_r($width_array);
print_r($color_array);

<form action="cbmailorder.php" method="post">
<p>select size

   <!-- Loop through all sizes, generating an option tag for each one. -->
   <select name="size"> 
<?php foreach($size_array as $size) {
        echo "<option value='$size'>$size</option>\n";
      }
?>
<!-- HTML here just got replaced above by PHP/no longer hardcoded
<option value="5 H">5 H</option> 
<option value="6">6</option> 
<option value="6 H">6 H</option> 
<option value="7" selected="1">7</option> 
<option value="7 H">7 H</option> 
<option value="8">8</option> 
<option value="8 H">8 H</option> 
<option value="9">9</option> 
<option value="9 H">9 H</option> 
<option value="10">10</option> 
-->
</select>
</p>


<p>select width   <select name="width"> 
<!-- Loop through all widths, generating an option tag for each one. -->
<?php foreach($width_array as $width) {
        echo "<option value='$width'>$width</option>\n";
      }
?>
<!-- HTML for the width got replaced by PHP statements above
<option value="N">N</option> <option value="B" selected="1"
>B</option> <option value="W">W</option> 
-->
</select></p>


<p>select color<select name="color"> 
<!-- Loop through all colors, generating an option tag for each one. -->
<?php foreach($color_array as $color) {
        echo "<option value='$color'>$color</option>\n";
      }
?> 
<!--  HTML for the colors got replaced by PHP statements above
<option value="gold">gold</option> <option value="platinum" selected="1">platinum</option> 
<option value="black suede">black suede</option> <option value="navy suede">navy</option> 
-->
</select>
</p>  
?>

PHP DEBUG: print_r($nested_array);

A look at the $nested_array contents put together in the for loop. This view comes from View>Page Source rather than the direct output of print_r($nested_array); which is not formatted.

Array
(
[0] => Array
(
[id] => 1
[itemnum] => 10100
[size] => 7
[width] => M
[color] => platinum
[inventory] => 7
)

[1] => Array
(
[id] => 2
[itemnum] => 10100
[size] => 6 1/2
[width] => M
[color] => black suede
[inventory] => 5
)

[2] => Array
(
[id] => 3
[itemnum] => 10100
[size] => 7
[width] => W
[color] => platinum
[inventory] => 4
)

[3] => Array
(
[id] => 4
[itemnum] => 10100
[size] => 8
[width] => M
[color] => navy suede
[inventory] => 2
)

[4] => Array
(
[id] => 5
[itemnum] => 10100
[size] => 7
[width] => W
[color] => Gold
[inventory] => 3
)

Our PHP file which has replaced the original HTML is almost done. Try the pull down fields to see the differences between the two different versions.