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.

putting the C back in CRUD

today we're going to look at how to create new records.

But first, an update for the database (now with new, improved test data) and a little exercise based on last week's lesson.

To update your database, download fred2.sql and import it into your development environment.

Now, I'd like you to create 2 new pages:

choosesubject.php which lists all the subjects in the database and lets the user pick one before calling...

showstudents.php - a page that lists all the students enrolled in that subject.

You can see the comments for my solution to this problem, but do have a good try at it first (hint: you will need to do a join in your query to list the student records for the subject's course).

And now, on to creating new records...

Believe it or not, this is easier than our previous tasks. Again we need 2 pages, one to get the information and another to insert it into the database.

For example, let's look at how we could add a new course to the database. We need a form that gets the new course name (addcourse.php) and then submits it (addcourse2.php).

Here's how addcourse might look:

<h1>Add a new course</h1>
<form method="post" action="addcourse2.php">
Course Name: <input type="text" name="coursename">
<input type="submit" value="Create Course" >
</form>

once it's sent to addcourse2.php we just need to extract the value in our coursename text box and plug it into the right bit of SQL.

To find out what that SQL should look like I recommend a quick visit to phpmyadmin.

1. Select the courses table and click on the 'insert' tab.
2. Enter a test record (type 'test' into the course name field) and click 'go'
3. have a look at the code that's produced. Something like...
   INSERT INTO `fred`.`courses` (
   `courseID` ,
   `course`
   )
   VALUES (
   NULL , 'test'
   );
4. Note the syntax... Insert into [table] ([field list]) values ([value list]);
   
5. Click on the link: 'Create PHP Code' and copy the line:
   $sql = 'INSERT INTO `fred`.`courses` (`courseID`, `course`) VALUES (NULL, \'test\');';
   
6. Plug that line into a typical mysql_query function to get something that looks like this code for your addcourse2.php file:
   

<?php
$dbcnx = mysql_connect("localhost", "root", "");
mysql_select_db("fred");
$sql = 'INSERT INTO `fred`.`courses` (`courseID`, `course`) VALUES (NULL, \''.mysql_real_escape_string($_POST['coursename']).'\');';
$results = mysql_query($sql);
if ($results == true) {
echo 'Created new course '.$_POST['coursename'];
} else {
echo 'Could not create new course '.$_POST['coursename'];
}
?>

easy, yes?

for next week, try making a pair of pages for adding new students. These will be very like the two above except that you need to get a courseID for the student as well as their name. It's best to limit the user's choices to existing courses only by using a select box with a dropdown list.

Image: 'Letter C, in Neon (Crazy California)'
http://www.flickr.com/photos/66606673@N00/429154325