PHP Database Access Modifying Data in an SQL Database

PHP Database Access

Modifying Data in an SQL Database

Problem

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


Solution

Example  Using PDO::exec()

         $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'");


Example  Preparing and executing a query

         $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'));


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 repreparing it. Reuses the same prepared query three times.

Example  Reusing a prepared statement

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