PHP and SQL Server - Updating Data
To amend a record, the SQL 'update' statement needs to be used as follows.
UPDATE table_name SET column_name='value' WHERE where_condition
As with the 'select' and 'insert' statements, a comma can be used where more than one field needs to be updated.
UPDATE table_name SET column_name1='value1', column_name2='value2', column_name3='value3' WHERE where_condition
The 'where' condition might be the 'id' of a particular record for example. The example below uses the same 'person' table from the previous examples for selecting and inserting data. It updates the 'lastname' and 'title' for the record with an 'id' of 5.
<?php // Connect to the database. require_once('database-connect.php'); // Query parameters. $lastName = "Bloggs"; $title = "Mrs"; $id = 5; try { // Prepare the query. $results = $connect->prepare("UPDATE person SET lastname = ?, title = ? WHERE id = ?"); // Bind the parameters. $results->bindParam(1, $lastName); $results->bindParam(2, $title); $results->bindParam(3, $id); // Execute the query. $results->execute(); } catch(Exception $e) { // If query fails, display an error and exit. echo "Error updating person information."; exit; } // Display a message saying person updated successfully. echo "Person updated successfully."; ?>
The contents of the 'person' table, following the update, can now be seen below.
id | firstname | lastname | title | dob |
1 | Bob | Smith | Mr | 1980-01-20 |
2 | George | Jones | Mr | 1997-12-15 |
3 | Fred | Bloggs | Mr | 1975-05-07 |
4 | Alan | White | Mr | 1989-03-20 |
5 | Fiona | Bloggs | Mrs | 1985-05-19 |