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