PHP MySQL
Create & Test MySQL Database

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 4 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.

Note: If you have accessed this web page via a search engine, you should go back and start at the Beginning.
This tutorial is designed to be viewed and executed in sequence.
Learn to build your database right on your PC and Export it to your website.

Syntax

"UPDATE birthdays  SET
firstname='$ud_firstname' ,
lastname='$ud_lastname' ,
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	Donahue	June 4, 1956
2	Mark	Ambrose	March 27, 1971

 
Choose a Line to Edit:

This is a series of 3 scripts used to make changes to fields in the database.

The first shows the code for a form similar to the one displayed above. It queries the user to enter the line number of the record they want to change.

Copy & Save as: birthdays_update_form.php

<html><head><title>Birthdays Update Form</title>
</head>
<body>
<?
/* Change next two lines if using online */
$db="newdb";
$link = mysql_connect('localhost', '', '');
if (! $link)
die(mysql_error());
mysql_select_db($db , $link)
or die("Couldn't open $db: ".mysql_error());
$result = mysql_query( "SELECT * FROM birthdays" )
or die("SELECT Error: ".mysql_error());
$num_rows = mysql_num_rows($result);
print "There are $num_rows records.<P>";
print "<table width=600 border=1>\n";
while ($get_info = mysql_fetch_row($result)){
print "<tr>\n";
foreach ($get_info as $field)
print "\t<td>$field</td>\n";
print "</tr>\n";
}
print "</table>\n";
mysql_close($link);
?>
<br>
<form method="POST" action="birthdays_change_form.php">
<pre>
Enter Id Number to Edit: <input type="text" name="id" size="5">
<input type="submit" value="Submit"><input type="reset">
</pre>
</form>
</body>
</html>

The form shown above calls the next form when an id number is chosen.

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" ?>">

Copy & Save as: birthdays_change_form.php

<html><head><title>Change Record form</title>
<style type="text/css">
td {font-family: tahoma, arial, verdana; font-size: .875em }
</style>


</head>
<body>
<?
$id=$_POST['id'];
$db="newdb";
$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());

$query=" SELECT * FROM birthdays WHERE id='$id'";
$result=mysql_query($query);
$num=mysql_num_rows($result);

$i=0;
while ($i < $num) {
$firstname=mysql_result($result,$i,"firstname");
$lastname=mysql_result($result,$i,"lastname");
$birthday=mysql_result($result,$i,"birthday");
?>
<table width="600" cellpadding="10" cellspacing="0" border="2">
<tr align="center" valign="top">
<td align="center" colspan="1" rowspan="1" bgcolor="#64b1ff">
<h3>Edit and Submit</h3>
<form action="birthdays_change_record.php" method="post">
<input type="hidden" name="ud_id" value="<? echo "$id" ?>">
FirstName:<input type="text" name="ud_firstname" value="<? echo "$firstname"?>"><br>
LastName: <input type="text" name="ud_lastname" value="<? echo "$lastname"?>"><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 form shown above accepts the changes and calls the processing form birthdays_change_record.php

The Processing Script

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.

Copy & Save as: birthdays_change_record.php

MySQL Tutorial

To extend your knowledge of MySQL study the Docs and Tutorials at the official MySQL website. MYSQL.com

PHP MySQLCreate & Test MySQL Database