Friday, October 22, 2010

Passing a Query Parameter to PHP


You and I were still working on the PHP/Database mini project. Our database, named shoes, had two tables. First one more generic information about a particular type of shoe and the second table listed each shoe item with its size, color, width, and how many there are in the inventory. Today I want to add a new class of shoes to our database and then query it via the URL to display two different items from the database. Let's add a new shoe to shoe_primary_data table via phpMyAdmin. The master table will now show "Birkenstock Arizona Sandals" in addition to the "Strappy Evening Shoes".

phpMyAdmin Insert

The Arizonas have an item number of 12000. Using that number, I inserted new records into "shoe_secondary_data" table for different sizes and colors for the new sandals.

phpMyAdmin Insert

Browsing the shoe secondary table:

phpMyAdmin Insert

Here's how our PHP file going to change. All of my explanations are within comments in the program:

//
// In URLs anything to the right of a question mark is called the query string.
// That's the place where you can plug in certain values that act like parameters
// to the page. In the query string if you have multiple parameters, they must
// be separated by ampersands (&).  So passing someones age and first name in a 
// query string would look like http://www.example.com?age=12&name=Joe
//
// When your browser goes to a URL that includes such a query string - whether by 
// clicking a link, or manually entering it - the server side software can look
// at the values you passed in the query string to decide what to do.
//
// In the case of a PHP web application, the PHP interpreter will read the query
// string and where it finds name value pairs such as age=12 name=Joe, it will
// copy these into a "Super Global" array variable called $_GET.  Your program
// can then access these values by indexing into the array, using the parameter
// name as the key, like this:  $_GET['age'], or $_GET['name'].
//
// In our case we are passsing in the item number of a shoe, and we'll use 
// it to look up the item in our database.
//
// URL to go to), all the parts of the URL to the right 
$itemnumber=$_GET["itemnum"];

// make a connection to the mysql database server
$db = mysqli_connect("womenshoes.db.6787492.hostedresource.com",
"womenshoes", "yourpasswordtothedatabase", "womenshoes");
if ($db) {
  //SHOE_PRIMARY_DATA TABLE
  //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 itemnum
  $query = "SELECT * FROM shoe_primary_data WHERE itemnum='$itemnumber' ";

  //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  first table (shoe_primary_data)
  $result = mysqli_query($db, $query);
  if ($result) {
     echo " ";     
  }
  else die('Problem querying a database');

 //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 itemnum
  $query = "SELECT * FROM shoe_secondary_data WHERE itemnum='$itemnumber' ";

  //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);
 

To run, enter from the URL either:

http://topossibilities.com/mailorder/mailorder.php?itemnum=12000

or

http://topossibilities.com/mailorder/mailorder.php?itemnum=10100

So far we have a page that can display different types of shoes, but you have to enter the different item numbers in the URL each time. So for the next post, we'll add another page that lists all the different types of shoes in the database. When you click on a shoe from this list, it will bring up the details page that we already have. That's the next post.



Post a Comment