[back]

Updating Data

Once data has been entered into a table, you can use a PHP script to udate any individual field. To start with, we will use the display page that we already have set up with two additions.

Part 1 - update1.php

<?php
$username="mills";
$password="123";
$database="class";

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT * FROM students";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
echo "<b><center>Database Output</center></b><br><br>";
echo "<p>"
?>

<table width=600 border=1>
<tr>
<th width=50>record ID</th>
<th width=250>first name</th>
<th width=250>last name</th>
<th width=50>grad year</th>
<th width=100>student number</th>
</tr>

<?php
$i=0;
while ($i < $num) {$id=mysql_result($result,$i,"id");
$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$grad_year=mysql_result($result,$i,"grad_year");
$student_no=mysql_result($result,$i,"student_no");
?>

<tr>
<td><? echo "$id"; ?></td>
<td><? echo "$first"; ?></td>
<td><? echo "$last"; ?></td>
<td><? echo "$grad_year"; ?></td>
<td><? echo "$student_no"; ?></td>
</tr>

<?php
++$i;
}
echo "</table>";

?>

<form action=update2.php method="post">
select the id number of the record you want to update: <input type=text name="id">
<input type=submit>
</form>

Explanation:

We first need to identify the record to change. This is the same PHP code that we used before, only this time we need to get the record ID and we will also add a form to pass the id number to the second part.

while ($i < $num) {$id=mysql_result($result,$i,"id");
$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$grad_year=mysql_result($result,$i,"grad_year");
$student_no=mysql_result($result,$i,"student_no");
?>
<tr>
<td><? echo "$id"; ?></td>
<td><? echo "$first"; ?></td>
<td><? echo "$last"; ?></td>
<td><? echo "$grad_year"; ?></td>
<td><? echo "$student_no"; ?></td>
</tr>

and

<form action=update2.php method="post">
select the id number of the record you want to update: <input type=text name="id">
<input type=submit>
</form>

Part 2: update2.php

<?php
$username="mills";
$password="123";
$database="class";

$id=$_POST['id'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT * FROM students WHERE id='$id'";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

$first=$_POST['first'];
$last=$_POST['last'];
$grad_year=$_POST['grad_year'];
$student_no=$_POST['student_no'];

$i=0;
while ($i < $num) {
$first=mysql_result($result,$i,"first");
$last=mysql_result($result,$i,"last");
$grad_year=mysql_result($result,$i,"grad_year");
$student_no=mysql_result($result,$i,"student_no");

?>

<form action="update3.php" method="post">
<input type="hidden" name="ud_id" value="<? echo "$id"; ?>">
First Name: <input type="text" name="ud_first" value="<? echo "$first"; ?>"><br>
Last Name: <input type="text" name="ud_last" value="<? echo "$last"; ?>"><br>
Graduation Year: <input type="text" name="ud_grad_year" value="<? echo "$grad_year"; ?>"><br>
Student Number: <input type="text" name="ud_student_no" value="<? echo "$student_no"; ?>"><br>
<input type="Submit" value="Update">
</form>

<?
++$i;
}
?>

Explanation:

Again, we passed the id number from the form in the first page to our second page:

$query="SELECT * FROM students WHERE id='$id'";

We will also set up a form, but this time instead of having empty boxes, we will display the variable. Notice that the "value=" contains the PHP code that displays a variable.

We assign this a new name - "ud_first", which will be passed to the next page.
First Name: <input type="text" name="ud_first" value="<? echo "$first"?>"><br>

Note: Because we do not want the id changed, it is passed as a hidden value:
<input type="hidden" name="ud_id" value="<? echo "$id"; ?>">

This info will then be passed to update3.php.
<form action="update3.php">

Part 3: update3.php

<?php
$username="mills";
$password="123";
$database="class";

$ud_id=$_POST['ud_id'];

$ud_first=$_POST['ud_first'];

$ud_last=$_POST['ud_last'];

$ud_grad_year=$_POST['ud_grad_year'];

$ud_student_no=$_POST['ud_student_no'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="UPDATE students SET first='$ud_first', last='$ud_last', grad_year='$ud_grad_year', student_no='$ud_student_no' WHERE id='$ud_id'";

@mysql_select_db($database) or die( "Unable to select database");
mysql_query($query);
echo "Record Updated";
mysql_close();
?>

<a href="update1.php">Return to update page</a>

Explanation:

We are now using the same form that we originally used to put info into our table. The only difference is that our $query now is UPDATE instead of INSERT.

The last line is just a simple HTML tag that will take the viewer back to the start or wherever else you want to go.

Create and save each of the above pages.