Updating Records

The SQL UPDATE Statement is used to update exisiting database records. The SQL UPDATE Statement is shown below:

UPDATE TableName SET FieldName = value WHERE criteria 

The following represents a user record whose telphone number will be updated in the Personnel Database table. Clicking on the update button calls a PHP rountine that executes an SQL UPDATE statement to update this record in the database table.

Original Record

First Name: 
Last Name: 
Telephone: 
Email: 
AutoNum:  (Not visible to the user) 

In addition to the form controls shown above, the page also includes a hidden textbox named "AutoNum" with a value equal to AutoNum field of the database table. This is a primary key field used to uniquely identify each record as described in the previous section on Deleting Records. The following code demonstrates how the page works:

<?php
	
if (isset($_POST['submitb'])) 
    {
    
    $UserNum = $_POST['AutoNum'];
    $NewTelephone = $_POST['Telephone'];
		
     //Establish a connection to the Database
   //Reference the mysqli_connection.php file created above

     include('mysqli_connection.php');


    //Issue SQL UPDATE Statement

	

     $sqlUpdate = "UPDATE Personnel SET PhoneNumber = '$NewTelephone' WHERE AutoNum = $UserNum";

     $rsUpdate = mysqli_query($conn,$sqlUpdate);	
	
	 if (mysqli_affected_rows($conn) == 1)
	  {
	
	    echo "Record successfully updated!";
	
	  }
	
     mysql_close($conn);
		
    }
	
?>

After the "Update Record" button is clicked, a connection is established with the Database. Next, an SQL UPDATE statement is issued to update the record in the Personnel table with an AutoNum field value equal to the value of the AutoNum hidden textbox. The SQL statement is then executed. The results of the mysqli_query() function are assigned to the $rsUpdate variable. The last step is to verify that the record update was a success and display a confirmation message. The mysqli_affected_rows() function is used to determine the number of rows in a result or the number of rows affected by the mysqli_query() statement. Since a single record is being updated, if the result of mysqli_affected_rows() is equal to 1, the record was updated successfully. Finally, the connection to the database connection is closed.

TOP | NEXT: Transactions