10.6. Modifying Data in a SQL Database
Problem
You want to add, remove, or change data in a SQL database.
Solution
With PEAR DB, use DB::query( )
to send an INSERT
,
DELETE
, or UPDATE
command:
$dbh->query("INSERT INTO family (id,name) VALUES (1,'Vito')"); $dbh->query("DELETE FROM family WHERE name LIKE 'Fredo'"); $dbh->query("UPDATE family SET is_naive = 1 WHERE name LIKE 'Kay'");
You can also prepare a query with DB::prepare( )
and execute
it with DB::execute( )
:
$prh = $dbh->prepare('INSERT INTO family (id,name) VALUES (?,?)'); $dbh->execute($prh,array(1,'Vito')); $prh = $dbh->prepare('DELETE FROM family WHERE name LIKE ?'); $dbh->execute($prh,array('Fredo')); $prh = $dbh->prepare('UPDATE family SET is_naive = ? WHERE name LIKE ?'); $dbh->execute($prh,array(1,'Kay');
Discussion
The query( )
method sends
to the database whatever it’s passed, so it can be
used for queries that retrieve data or queries that modify data.
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:
$prh = $dbh->prepare('DELETE FROM family WHERE name LIKE ?'); $dbh->execute($prh,array('Fredo')); $dbh->execute($prh,array('Sonny')); $dbh->execute($prh,array('Luca Brasi'));
See Also
Recipe 10.4 for connecting to a SQL database;
Recipe 10.5 for querying a SQL database;
Recipe 10.8 discusses prepare( )
and execute( )
in detail;
documentation on DB::query( )
Get PHP Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.