UPDATE statement
In this lesson we are going to write a PHP script that updates records in MySQL database using the UPDATE statement.
Parameter of UPDATE command:
- UPDATE – Performs an update MySQL query
- SET – Updates (assigns new value to) columns in the selected table rows.
- WHERE – Limits which rows are affected
We can execute any sql query like insert, update, delete, select etc. using the mysql_query() function.
In this lesson we need to three pages namely the list.php, updateform.php and updateprocess.phpHere is the code(list.php):
<?php//including the database connection file include("mysql_connect.php");mysql_select_db("my_db");$result = mysql_query("SELECT * FROM employee_record ");echo "<table border='1'><tr> <th>ID</th> <th>Firstname</th> <th>Lastname</th> <th>Position</th> <th>Age</th> <th>Salary</th> <th>Email</th> <th>Action</th> </tr>";while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td>".$row['id']."</td>"; echo "<td>".$row['f_name']."</td>"; echo "<td>".$row['l_name']."</td>"; echo "<td>".$row['position']."</td>"; echo "<td>".$row['age']."</td>"; echo "<td>".$row['salary']."</td>"; echo "<td>".$row['email']."</td>"; echo "<td><a href=\"updateform.php?id=$row[id]\">Edit</a> </td>"; } echo "</table>"; ?>
In the above example it will select all the data stored in our employee_reecord table. Name this file as list.php.
The function of the code below is to get the id of the selected row and pass the value to updateform.php.
echo “<td><a href=\”updateform.php?id=$row[id]\”>Edit</a> </td>”;
Now, let’s create the updateform.php.
Here is the code:
<?phpinclude('mysql_connect.php');$query = "SELECT * FROM employee_record where id=$_GET[id]";$result = mysql_query($query);while($rows = mysql_fetch_array($result)) { $id = $rows['id']; $f_name = $rows['f_name']; $l_name = $rows['l_name']; $position = $rows['position']; $age = $rows['age']; $salary = $rows['salary']; $email = $rows['email']; ?> <html> <br><br> <form action='updateprocess.php?id=<?php echo $id?>' method='POST'> ID <input type='text' name='id' size='20' value='<?php echo $id?>'><br><br> Firstname <input type='text' name='f_name' size='20' value='<?php echo $f_name?>'><br><br> Lastname <input type='text' name='l_name' size='20' value='<?php echo $l_name?>'><br><br> Position <input type='text' name='position' size='20' value='<?php echo $position?>'><br><br> Age <input type='text' name='age' size='20' value='<?php echo $age?>'><br><br> Salary <input type='text' name='salary' size='20' value='<?php echo $salary?>'><br><br> email <input type='text' name='email' size='20' value='<?php echo $email?>'><br><br> <input type='submit' name='submit' value='Modify'> </form> </html>
In the updateform.php, first we have included the mysql_connect.php which is the file we need to establish a connection in our database.
Then we have declared $query variable that will store the sql command. In our query, we have selected the record of the employee where his/her id is equivalent to the id selected in list.php.
Next we have declared a $result variable that will handle the result set returned by our query.
Next, we use the mysql_fetch_array() function to fetch a row from a recordset as an array.
Then, we place the mysql_fetch_array() function within the conditional statement of the while loop. It means that the while loop will continue to execute as long as there a row to fetch.
Then we have created a HTML form that will display the record of the employee whose id where selected in the list.php.
The record that is being displayed in this page will be the record that we are going to update.
This page will submit the record of the employee (id, f_name, l_name, position, age, salary, email) to the page processupdate.php when the submit button is clicked.
Now we are going to write our updateprocess.php.
Here is the code:
<?phpinclude('mysql_connect.php'); $f_name=$_POST['f_name']; $l_name = $_POST['l_name']; $position = $_POST['position']; $age = $_POST['age']; $salary = $_POST['salary']; $email = $_POST['email']; $id = $_GET['id'];$sql="UPDATE employee_record set f_name='$f_name',l_name='$l_name', position='$position', age='$age', salary='$salary', email='$email' where id='".$id."'";mysql_query($sql) or die('Error');echo "<br>Data Updated!";?> <a href='list.php'>List</a>
In the updateprocess.php, first we have included the mysql_connect.php which is the file we need to establish a connection in our database.
Then, we have declared variables to store the value data that will be used to update the record.
Next, we will execute the update statement to update the record. It will display Data Updated! saying that we have successfully updated the record in the database. The die clause will execute when the connection fails.
Finally, we have created a link back to the list.php.
