Updating Records

The SQL UPDATE Statement is used to change or update exisiting database records.

The SQL UPDATE Statement is shown below:

UPDATE TableName SET (FieldName1=value1 [,FieldName2=value2]...) WHERE criteria

The following represents a user record that will be updated from a Personnel Database table. Modifying any field associated with the record and clicking the Update Record button calls a PHP rountine that executes an SQL UPATE statement to UPDATE this record in the database table.

First Name:
Last Name:
Telephone:
Email:       

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 field is used to uniquely identify each record. The following code demonstrates how the page works:

DirectorySearch.php 

<?php
	
if ($_POST['submitb']=="Update Record") 
    {
    
    $new_fname = $_POST['FName'];
    $new_lname= $_POST['LName'];
    $new_telephone = $_POST['Telephone'];
    $new_email = $_POST['Email'];
		
    $conn = odbc_connect('Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\path\to\database.mdb','','');
    $sqlUpdate = "UPDATE Personnel SET (FName = '$new_fname', LName = '$new_lname', Telephone = '$new_telephone',   
    Email = '$new_email') WHERE AutoNum =" . $_POST['AutoNum'];
    $rsUpdate = odbc_exec($conn,$sqlUpdate);	
	
	 if(odbc_num_rows($rsUpdate) == 1)
	  {
	
	    echo "Record successfully updated!";
	
	  }
	
     odbc_close($conn);
		
    }
	
?>

After the "Update Record" button is clicked, the current form field values are assigned to scalar variables. This step is not necessary, however, it does simplify coding of the SQL UPDATE statement. Next, a connection is established with the Access Database. Following the database connection, 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 odbc_exec() function are assigned to the $rsDelete variable. The last step is to verify that the record update was a success and display a confirmation message. The odbc_num_rows() function is used to determine the number of rows in an ODBC result or the number of rows affected by the odbc_exec() statement. Since a single record is being updated, if the result of odbc_num_rows() is equal to 1, the record was updated successfully. Finally, the connection to the database connection is closed.