Recipe 10.6. Modifying Data in an SQL Database


10.6.1. Problem

You want to add, remove, or change data in an SQL database.

10.6.2. Solution

Use PDO::exec( ) to send an INSERT, DELETE, or UPDATE command, as shown in Example 10-14.

Using PDO::exec( )

<?php $db->exec("INSERT INTO family (id,name) VALUES (1,'Vito')"); $db->exec("DELETE FROM family WHERE name LIKE 'Fredo'"); $db->exec("UPDATE family SET is_naive = 1 WHERE name LIKE 'Kay'"); ?>

You can also prepare a query with PDO::prepare( ) and execute it with PDOStatement::execute( ), as shown in Example 10-15.

Preparing and executing a query

<?php $st = $db->prepare('INSERT INTO family (id,name) VALUES (?,?)'); $st->execute(array(1,'Vito')); $st = $db->prepare('DELETE FROM family WHERE name LIKE ?'); $st->execute(array('Fredo')); $st = $db->prepare('UPDATE family SET is_naive = ? WHERE name LIKE ?'); $st->execute(array(1,'Kay'); ?>

10.6.3. Discussion

The exec( ) method sends to the database whatever it's passed. For INSERT, UPDATE, and DELETE queries, it returns the number of rows affected by the query.

The prepare( ) and execute( ) methods are especially useful for queries that you want to execute multiple times. Once you've prepared a query, you can execute it with new values without re-preparing it. Example 10-16 reuses the same prepared query three times.

Reusing a prepared statement

<?php $st = $db->prepare('DELETE FROM family WHERE name LIKE ?'); $st->execute(array('Fredo')); $st->execute(array('Sonny')); $st->execute(array('Luca Brasi')); ?>

10.6.4. See Also

Recipe 10.7 for information on repeating queries; documentation on PDO::exec( ) at http://www.php.net/PDO::exec, on PDO::prepare( ) at http://www.php.net/PDO::prepare, and on PDOStatement::execute( ) at http://www.php.net/PDOStatement::execute.




PHP Cookbook, 2nd Edition
PHP Cookbook: Solutions and Examples for PHP Programmers
ISBN: 0596101015
EAN: 2147483647
Year: 2006
Pages: 445

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net