fleshing out the TAFE database app

Sorry folks, I'm afraid I can't be with you in person again today as I'm due for some irradiation - but I'll put together some notes to keep you going...

Last week we built a bit more of our TAFE database application (current dump available from: http://bathurst-tafe.nsw.edu.au/~pshanks/icab4136a/fred2.sql). We creat pages that allowed us to add new students and subjects. Then we went on to start building an update page for our courses.

From memory we made a page that lists each course with a link next to each saying something imaginative like: Edit

HTML code something like:
<a href="courseedit.php?courseid=<?php echo $row['courseID'] ?>"<?php">">Edit</a>

So, what happens on the courseedit.php page?

Well, we need to retrieve the target course record for starters, which is going to need something like this...

<?php
$dbcnx = mysql_connect("localhost", "root", "");
mysql_select_db("fred");
$results = mysql_query("select * from courses where courseID = ".$_GET['courseID']);
$row = mysql_fetch_array($results)
?>

The magic happens because $_GET['courseID'] picks up the courseID that's being passed to the page in the URL.

From here it's a simple matter of plugging the values into a form so that people can edit them. We already built a similar form for adding new records, so let's cut and paste and edit slightly to get this:

<h1>Edit a course</h1>

<form method="post" action="courseedit2.php">

Course Name: <input type="text" name="coursename" value="<?php echo $row['course'] ?>" >

<input type="hidden" name="courseID" value="<?php echo $row['courseID'] ?>" >

<input type="submit" value="Update Course Info" >

</form>

Note how we've added a value to the textbox from the record we retrieved.

We've also added a new bit of data in a hidden field, the courseID for the record we're editing. This information is needed when we update the record on the courseedit2.php page, which is going to look a bit like this:

<?php

$dbcnx = mysql_connect("localhost", "root", "");

mysql_select_db("fred");

$sql = 'UPDATE `fred`.`courses` SET `course` = \''.mysql_real_escape_string($_POST['coursename']).'\' WHERE `courses`.`courseID` = '.mysql_real_escape_string($_POST['courseID']).' LIMIT 1;';

$results = mysql_query($sql);

if ($results == true) {

echo 'Updated course '.$_POST['coursename'];

} else {

echo 'Could not update course '.$_POST['coursename'];

}

?>

What's happening here?

Firstly, I picked up the update code by editing a course record in phpMyAdmin and looking at the generated sql.

I edited the code provided there so that it picked up values from the form on the corseedit.php page, getting the course from the textbox and the courseID from the hidden field - this courseID ensures that you just update the one, specific record in the table (WARING: if you enter SQL like this: update courses set course = 'newcoursename' it will replace every single record's course field with "newcoursename". Usually you don't want that sort of thing to happen)

Armed with this example it shouldn't be too hard for you to extend yourself to a set of pages to list all students with edit links and create a pair of edit pages for student records. The only trick will be pre-selecting the course they are currently enrolled in.

Here's how to approach this one...

As you loop through the courses when creating the options in the course select box, compare their courseID with the student.courseID, if they match add a bit of code to the option that says "selected". Assuming you have one recordset for the target student that returned a $studentrow record and a bunch of records for all the courses being held in $courseresults the code would look like:

while ($courserow = mysql_fetch_array($courseresults)) {

echo '<option value="'.$couserow['courseID'].'"';
if ($cousrerow['courseID'] == $studentrow['courseID']) {
echo ' selected ';
}
echo '>'.$courserow['course'];

}

Give each other some help getting this all together (along with a studentedit2.php page) and then try your hand at getting a subject edit suit together.

My fingers are aching now, so I'm going to sign off - good luck with your mission. I'm hoping to see you tomorrow or Thusday, so you can let me know how you got on.