pulling it all together

finally, finally we got some traction using php to retreive records...

we kicked off by building a little page to display all the subjects in our database (if you need the tables and data, download http://bathurst-tafe.nsw.edu.au/~pshanks/fred.sql and run it using the phpmyadmin import dialog) . To reproduce our efforts, create a page called showsubjects.php and include the following code in the body area...

<?php

$dbcnx = mysql_connect("localhost", "root", "");
mysql_select_db("fred");
$results = mysql_query("select * from subjects where courseID = ".$_POST['coursenumber']);
while ($row = mysql_fetch_array($results)) {
echo $row['subjectID'].' - '.$row['subject'].'<br />';
}
?>

Now, add a where clause to the sql statement so that you're diplaying just the Cert IV general subjects (hint: the courseID = 1)... I've put the solution in the comments for this post.

Getting the subjects for any particular course is fine if you don't mind editing the sql, but we can do better by creating a page with a form on it that passes a value to our showsubjects.php page.

Create a new page called choosecouse.php with the following code in it:

<form method="post" action="showsubjects.php">
Which course?
<input type="text" name="coursenumber" >
<input type="submit">
</form>

This form will post the contents of the text box called 'coursenumber' to the showsubjects.php page when the submit button is clicked.

To get showsubjects.php to use this information we need to update our sql statement to read the $_POST variable that is passed to it...

<?php

$dbcnx = mysql_connect("localhost", "root", "");
mysql_select_db("fred");
$results = mysql_query("select * from subjects where courseID = ".$_POST['coursenumber']);
while ($row = mysql_fetch_array($results)) {
echo $row['subjectID'].' - '.$row['subject'].'<br />';
}
?>

All well and good, so long as our user knows and can remember the courseID they're after (which is unfair). A better solution is to restrict their input to just the course nubmers available. Instead of a text box we can put a drop down select list on the choosecourse.php page...

<form method="post" action="showsubjects.php">
<select name="coursenumber" >
<option value="1">Cert IV general
<option value="2">Cert IV web
<option value="3">Cert II I.T.
</select>
<input type="submit" />
</form>

Nearly done, but what if someone adds a new course to the database. Should we make our customer pay for a web developer to update the choosecourse.php page?

Certainly not! We could use php to select all the records in the courses table and dynamically create a select list. Hava a crack at it (the solution is in the comments for this post).




Image: 'harder! Pull!'
http://www.flickr.com/photos/44124421254@N01/184140074