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

crash and burn

After a disastrous attempt to move and run our mySQL server on the bathurst-tafe server we ended up with little time for actual learning - my appologies all.

We did get to write a very basic page to list the students enrolled in our Cert IV web course (courseID 2)...

<?php
$dbconnection = mysql_connect("localhost","root","");
mysql_select_db("fred");
$sql = 'SELECT * FROM `students` WHERE courseID = 2 LIMIT 0, 30 ';
$results = mysql_query($sql);
while ($row = mysql_fetch_array($results)) {
echo $row['name'].'<br />';
}
?>

Image: 'untitled'
www.flickr.com/photos/62674889@N00/46266892

coding in php

This week we're looking at how to extract data from our database and display it in the web browser.

To do this we need to have some way of programming the web server's computer - and we'll be using php, a server-side scripting language.

Rather than write it all down here, I've uploaded the first four chapters of a real live book on the subject: Build Your Own Database Driven Website Using PHP & MySQL. Grab a copy and work through chapters 3 and 4 (ch. 1 and 2 are interesting too, but we know that stuff already :-).

This week's challenges:

1. Create a web page that displays all the records we have in the courses table we created last week.
2. List the subjects for course number 1 (Cert IV general)

Next week we're going to create a form that lets us query the database for specific data.

Image: 'Will code for food'
www.flickr.com/photos/67523311@N00/114420037

joining stuff back together

Being selective

Now that we've split up our data into student, course and subject tables we can start looking at how to extract information from them by putting them all back together again :-P

We do this with our tried and true select statement - listing the fields (now for each table we're interested in), the tables, and how they are related to each other.

For example, to list all the students and which courses they are enrolled in we would enter:

SELECT name, course
FROM `students`, courses
WHERE students.courseID = courses.courseID

Try to list all the courses and their subjects with a similar query.

Really being selective

To restrict the results to just the student called 'John' we add to the where clause using AND...

SELECT name, course
FROM `students`, courses
WHERE students.courseID = courses.courseID
and name = 'John'

Try this with your earlier subjects query, restricting it to just show the subjects for 'Cert IV General'

Restricting rows on a key field

We can also restrict rows using numeric values (for a primary or secondary key, for example). So we could find all students studying the Cert IV web course using this query:

SELECT name, course
FROM `students`, courses
WHERE students.courseID = courses.courseID
and courses.courseID = 2

note: since there is a courseID in both the students and the courses table we need to tell the database engine which one we're using in the query. We do this by adding the table name to it: courses.courseID.

Try building a query that lists all the subjects for courseID = 1 (the cert IV general)

Pulling out all stops

Have a crack at listing all the courses, subjects and students from all three tables. (Hint: you will need to join the subjects and students tables and the courses and students tables). See the comments on this post for the answer.

Exercise your new-found knowledge by reading through and trying the examples in our course notes on joins (pages 11 to 14).

There's another way of connecting tables, using the Select .... from ... join ... using ... syntax, which is explained in these exercises on sqlzoo about joining tables, and this slightly more advanced joining tables lesson both are highly recommended.

Next week we take a little break from learning new SQL statements.

We're going to learn some new PHP statements instead :-)

see you then...

Image: 'cadillac ranch'
www.flickr.com/photos/42737578@N00/23249259