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.

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

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.