Throughout this tutorial, I've been loading the SQL statement into a variable ($sql) before firing the query at the database with mysql_query(). This is useful for debugging. If something goes wrong, you can always echo the SQL to the screen to examine it for mistakes.
We already know how to get data into the database. Now let's try modifying records that are already in the database. Editing data combines two elements we've already seen: displaying data on the screen and sending data back to the database via form input. However, editing is slightly different in that we have to show the appropriate data in the form.
First, let's recycle the code from Lesson 1 to display the employee names on our page. But this time through, we're going to populate our form with employee information. It should look a little like this:
<html>
<body>
<?php
$db = mysql_connect("localhost", "root");
mysql_select_db("mydb",$db);
if ($id) {
// query the DB
$sql = "SELECT * FROM employees WHERE id=$id";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
?>
<form method="post" action="<?php echo $PHP_SELF?>">
<input type=hidden name="id" value="<?php echo $myrow["id"] ?>">
First name:<input type="Text" name="first" value="<?php echo $myrow["first"] ?>"><br>
Last name:<input type="Text" name="last" value="<?php echo $myrow["last"] ?>"><br>
Address:<input type="Text" name="address" value="<?php echo $myrow["address"] ?>"><br>
Position:<input type="Text" name="position" value="<?php echo $myrow["position"] ?>"><br>
<input type="Submit" name="submit" value="Enter information">
</form>
<?php
} else {
// display list of employees
$result = mysql_query("SELECT * FROM employees",$db);
while ($myrow = mysql_fetch_array($result)) {
printf("<a href=\"%s?id=%s\">%s %s</a><br>\n", $PHP_SELF, $myrow["id"], $myrow["first"], $myrow["last"]);
}
}
?>
</body>
</html>
We just echoed the field information into the value attribute of the each element, which was fairly easy. Let's build on this a little more. We will add the ability to send the edited code back to the database. Again, we're going to use the Submit button to test whether we need to process the form input. Also note the slightly different SQL statement we use.
<html>
<body>
<?php
$db = mysql_connect("localhost", "root");
mysql_select_db("mydb",$db);
if ($id) {
if ($submit) {
$sql = "UPDATE employees SET first='$first',last='$last',address='$address',position='$position' WHERE id=$id";
$result = mysql_query($sql);
echo "Thank you! Information updated.\n";
} else {
// query the DB
$sql = "SELECT * FROM employees WHERE id=$id";
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
?>
<form method="post" action="<?php echo $PHP_SELF?>">
<input type=hidden name="id" value="<?php echo $myrow["id"] ?>">
First name:<input type="Text" name="first" value="<?php echo $myrow["first"] ?>"><br>
Last name:<input type="Text" name="last" value="<?php echo $myrow["last"] ?>"><br>
Address:<input type="Text" name="address" value="<?php echo $myrow["address"] ?>"><br>
Position:<input type="Text" name="position" value="<?php echo $myrow["position"] ?>"><br>
<input type="Submit" name="submit" value="Enter information">
</form>
<?php
}
} else {
// display list of employees
$result = mysql_query("SELECT * FROM employees",$db);
while ($myrow = mysql_fetch_array($result)) {
printf("<a href=\"%s?id=%s\">%s %s</a><br>\n", $PHP_SELF, $myrow["id"], $myrow["first"], $myrow["last"]);
}
}
?>
</body>
</html>
And that's that. We've managed to combine most of the features we've seen into one script. You can also see how we've used an if() statement inside another if() statement to check for multiple conditions.
It's time to put it all together and make one killer script.
