After the discussion earlier ( http://www.sitepoint.com/forums/showthread.php?993176-PHP-Function-of-the-Week-pg_insert-Moreover-a-lesson-on-PHP-s-largest-pitfall ) about PDO and ORMs, it reminded me of a tradeoff I had to make in my own ORM.
When inserting a record, sometimes you only want to supply a subset of fields and leave the rest to their default values.
Mysql makes this easy, you only supply the column names/data you wish to insert. However, if you prepare and reuse this query you’ll only every be able to insert those columns, so, later in the script if an insert happens with different columns you hit a bug – the second insert won’t work.
There are a few options here and I’d like to discuss with everyone which you think is best.
1) Don’t store the prepared query. Just prepare/execute every time and don’t worry about reusing the prepared $stmt.
Example (pseudocode):
//Take
$user = new stdClass;
$user->name = 'Tom';
$user->website = 'http://r.je';
//And on the fly generate:
$sql = 'INSERT INTO `user` (name, website) VALUES (:name, :website)';
$stmt = $db->prepare($sql);
$stmt->execute((array) $user);
//Then don't store or reuse $stmt, running through this entire process each time
Pros:
-Simplest method
-Doesn’t require any metadata about the table and the query can be easily generated on the fly
Cons:
-Doesn’t make the most of prepared statements so isn’t as efficient
2) Use SHOW COLUMNS to get a list of columns from the database and insert NULL where the data isn’t supplied
Example (pseudocode):
//Take
$user = new stdClass;
$user->name = 'Tom';
$user->website = 'http://r.je';
//run this:
$columns = $db->query('SHOW COLUMNS FROM `user`');
//And combine the two to generate
$sql = 'INSERT INTO `user` (id, name, email, website) VALUES (:id, :name, :email :website)';
//Then:
$stmt = $db->prepare($sql);
//And for this and every subsequent insert:
$data = array_fill_keys($columns, null);
$data = array_merge((array) $user, $data);
$stmt->execute($data);
Pros:
-Allows reuse of a single prepared statement to handle all inserts for a given table
-Never allows a PHP script to attempt to insert data into a column which doesn’t exist
Cons:
-Requires knowing table metadata before preparing the query.
-SHOW COLUMNS is slow ( http://bugs.mysql.com/bug.php?id=10210 ) and is almost certainly slower than preparing/executing a different query each time.
3) Require strongly typed objects that have properties for each column. e.g. a user object with a property for each column in the table
//Require a specific implementation to be passed in
class User {
public $name, $website, $id, $email;
}
public function save(User $user) {
//Use the structure of the user object to generate:
$sql = 'INSERT INTO `user` (id, name, email, website) VALUES (:id, :name, :email :website)';
$stmt = $db->prepare($sql);
//And for this and every subsequent insert:
$data = array_fill_keys($columns, null);
$data = array_merge((array) $user, $data);
$stmt->execute($data);
}
Pros:
-Allows reuse of a single prepared statement to handle all inserts for a given table
-Never allows a PHP script to attempt to insert data into a column which doesn’t exist*
Cons:
-Requires knowing table metadata before preparing the query.
-PHP must have the table metadata hardcoded somewhere which violates DRY (as the table schema needs to be repeated in PHP) and this requires updating every time the table structure is updated.
-Strictly speaking should require a different mapper save method for each type of object although realistically you just wouldn’t!
I chose the first, but it does feel like wasting the power of prepared statements by preparing then disregarding in this way. I’d be interested to hear everyone else’s thoughts on the matter.