Page 444 - Beginning PHP 5.3

Part III: Using PHP in Practice
Updating Records

As you saw in Chapter 12, you can alter the data within an existing table row by using an SQL UPDATE
statement:
mysql > UPDATE fruit SET name = ‘grapefruit’, color = ‘yellow’ WHERE id = 2;

Query OK, 1 row affected (0.29 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql > SELECT * from fruit;
+----+------------+--------+
| id | name | color |
+----+------------+--------+
| 1 | banana | yellow |
| 2 | grapefruit | yellow |
| 3 | plum | purple |
+----+------------+--------+
3 rows in set (0.00 sec)

As with inserting new records, updating records via your PHP script is simply a case of using
PDO::query() if you ’ re passing literal values in the UPDATE statement, or PDO::prepare() with
placeholders if you ’ re passing variable values. For example, the following script changes the email
address field in the “ Derek Winter ” record that was added in the previous section:

< ?php
$dsn = “mysql:dbname=mydatabase”;
$username = “root”;
$password = “mypass”;

try {
$conn = new PDO( $dsn, $username, $password );
$conn- > setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
} catch ( PDOException $e ) {
echo “Connection failed: “ . $e- > getMessage();
}

$id = 8;
$newEmailAddress = “derek.winter@example.com”;

$sql = “UPDATE members SET emailAddress = :emailAddress WHERE id = :id”;

try {
$st = $conn- > prepare( $sql );
$st- > bindValue( “:id”, $id, PDO::PARAM_INT );
$st- > bindValue( “:emailAddress”, $newEmailAddress, PDO::PARAM_STR );
$st- > execute();
} catch ( PDOException $e ) {
echo “Query failed: “ . $e- > getMessage();
}

? >

406

9/21/09 9:14:03 AM
c14.indd 406 9/21/09 9:14:03 AM
c14.indd 406