CMS takes the work out of building interactive websites.

Create a website at HostPapa.com using Joomla, PostNuke or other popular software.

100% Green Energy Hosting from HostPapa.com
Installs instantly using the FANTASTICO script installer!!

Your site will have automatic features like member sign up and site search.

Features like message boards and chat are easily added.

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

 
Choose a Line to Edit:

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 login file and interface. To run the scripts on your PC you must have a localhost server installed along with PHP and the MySQL server.
Download birthdays_db.zip

MySQL Tutorial

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