Get PHP MySQL Tools
at
Just Host
PHP MySQL
Interactive Website Design
Updating the Data
The process of updating or editing a record or row of a table makes use of the trusty mysql_query() function.
In the syntax shown below we see that the UPDATE procedure requires the SET and WHERE definitions to pinpoint the changes. The statement below would require that all 3 fields of the specified row be passed to the processing script.
Here we also realize the value of including the automatically incremented id field to specify a unique identifier for each row of the birthdays table.
"UPDATE birthdays SET name='$ud_name' ,birthday='$ud_birthday' WHERE id='$ud_id'"The Editing Query Form
The ideal query form used for editing records, should display the existing data and allow the user to pick a line number for editing purposes. The code for displaying data from the previous lesson could be used for this purpose.
A simple query form might look like the one shown below:
| 1 | Peggy | June4 |
| 2 | Mark | Mar27 |
The Change Form
The form which allows the user to make changes should display existing information in input boxes for editing. The code for accomplishing this is shown below.
Once again we make use of the mysql_query and mysql_num_rows functions. Then we create a while loop, insert a basic html form inside and use PHP echo statements in the value field to display existing data.
We also make use of a new function mysql_result(), which separates each field into resource identifier, index and value.
We'll use a hidden input text box to pass the id value to the processing form as ud_id
<input type="hidden" name="ud_id" value="<? echo "$id" ?>">
<html><head><title></title></head>
<body>
<?
$id=$_POST['id'];
$db="mydatabase";
$link = mysql_connect("localhost");
if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link)
or die("Couldn't open $db: ".mysql_error());
$result=mysql_query(" SELECT * FROM birthdays WHERE id='$id'");
$num=mysql_num_rows($result);
$i=0;
while ($i < $num) {
$name=mysql_result($result,$i,"name");
$birthday=mysql_result($result,$i,"birthday");
?>
<table width="300" cellpadding="3" cellspacing="0" border="2">
<tr align="center" valign="top">
<td align="center" colspan="1" rowspan="1" bgcolor="#5FACD6">
<form action="change_birthdays.php" method="post">
<input type="hidden" name="ud_id" value="<? echo "$id" ?>">
Name: <input type="text" name="ud_name" value="<? echo "$name"?>"><br>
Birthday: <input type="text" name="ud_birthday" value="<? echo "$birthday"?>"><br>
<input type="Submit" value="Update">
</form>
</td></tr></table>
<?
++$i;
}
?>
</body>
</html>
The Processing Script (change_birthdays.php)
The user makes changes to the desired field or fields and clicks the Submit button to pass the information to the processing script. The PHP script change_birthdays.php isn't really much more than the first line of code we presented at the top of the page.
The SET option is used to assign the values received from the previous form to the fields name and birthday WHERE the ud_id string matches the id field.
<html><head><title></title></head>
<body>
<?
$ud_id=$_POST['ud_id'];
$ud_name=$_POST['ud_name'];
$ud_birthday=$_POST['ud_birthday'];
$db="mydatabase";
$link = mysql_connect("localhost");
if (! $link)
die("Couldn't connect to MySQL");
mysql_select_db($db , $link)
or die("Couldn't open $db: ".mysql_error());
mysql_query(" UPDATE birthdays SET name='$ud_name' ,birthday='$ud_birthday' WHERE id='$ud_id'");
echo "Record Updated";
mysql_close($link);
?>
</body>
</html>
Download the Scripts
The Birthdays Database management files can be downloaded in a zip file. The package contains an integrated db management system, with a simple interface. This Instruction file is included in the download.
Download birthdays_db.zip
MySQL Tutorial
To extend your knowledge of MySQL study the Docs and Tutorials at the official MySQL website. MYSQL.com
