Preparing a MySQL INSERT/UPDATE statement with DEFAULT values
- by Raveren
Quoting MySQL INSERT manual - same goes for UPDATE:
Use the keyword DEFAULT to set a column explicitly to its default value. This makes it easier to write INSERT statements that assign values to all but a few columns, because it enables you to avoid writing an incomplete VALUES list that does not include a value for each column in the table. Otherwise, you would have to write out the list of column names corresponding to each value in the VALUES list.
So in short if I write
INSERT INTO table1 (column1,column2) values ('value1',DEFAULT);
A new row with column2 set as its default value - whatever it may be - is inserted.
However if I prepare and execute a statement in PHP:
$statement = $pdoObject->
prepare("INSERT INTO table1 (column1,column2) values (?,?)");
$statement->execute(array('value1','DEFAULT'));
The new row will contain 'DEFAULT' as its text value - if the column is able to store text values.
Now I have written an abstraction layer to PDO (I needed it) and to get around this issue am considering to introduce a
const DEFAULT_VALUE = "randomstring";
So I could execute statements like this:
$statement->execute(array('value1',mysql::DEFAULT_VALUE));
And then in method that does the binding I'd go through values that are sent to be bound and if some are equal to self::DEFAULT_VALUE, act accordingly.
I'm pretty sure there's a better way to do this. Has someone else encountered similar situations?
