preparing a mysql insert update statement with default values using -'php,mysql,pdo,prepared-statement,default-value'

preparing a mysql insert update statement with default values  using -'php,mysql,pdo,prepared-statement,default-value'

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 (?,?)");

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:


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?

asked Sep 8, 2015 by rajesh
0 votes

4 Answers

0 votes
The only "workaround" I know for this is to use Coalesce() and Default(fieldname) E.g. $pdo = new PDO("mysql:host=localhost;dbname=test", 'localonly', 'localonly'); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->exec(" CREATE TEMPORARY TABLE foo ( id int auto_increment, x int NOT NULL DEFAULT 99, y DATETIME NOT NULL DEFAULT '2010-03-17 01:00:00', z varchar(64) NOT NULL DEFAULT 'abc', primary key(id) ) "); $stmt = $pdo->prepare(' INSERT INTO foo (x,y,z) VALUES ( Coalesce(:x, Default(x)), Coalesce(:y, Default(y)), Coalesce(:z, Default(z)) ) '); $stmt->bindParam(':x', $x); $stmt->bindParam(':y', $y); $stmt->bindParam(':z', $z); $testdata = array( array(null, null, null), array(1, null, 'lalala'), array(null, '2009-12-24 18:00:00', null) ); foreach($testdata as $row) { list($x,$y,$z) = $row; $stmt->execute(); } unset($stmt); foreach( $pdo->query('SELECT id,x,y,z FROM foo', PDO::FETCH_NUM) as $row) { echo join(', ', $row), "\n"; } prints 1, 99, 2010-03-17 01:00:00, abc 2, 1, 2010-03-17 01:00:00, lalala 3, 99, 2009-12-24 18:00:00, abc
answered Sep 8, 2015 by rajesh
0 votes
I tried replying to VolkerK answer, but couldnt find how. :( I'm kinda new to all this. Anyway, I created a mysql function to use in conjuction with his COALESCE idea CREATE FUNCTION NULLDEFAULT(colname VARCHAR(64), tablename VARCHAR(64), dbname VARCHAR(64)) RETURNS longtext DETERMINISTIC READS SQL DATA BEGIN DECLARE retval longtext; SELECT COLUMN_DEFAULT INTO retval FROM information_schema.COLUMNS WHERE TABLE_NAME = tablename AND COLUMN_NAME = colname AND TABLE_SCHEMA = dbname; RETURN retval; END You would use it like this: $stmt = $pdo->prepare(" INSERT INTO foo (x,y,z) VALUES ( Coalesce(:x, NULLDEFAULT('x', 'foo', 'database')), Coalesce(:y, NULLDEFAULT('y', 'foo', 'database')), Coalesce(:z, NULLDEFAULT('z', 'foo', 'database')) ) "); That will return null if the column has no default value, and won't trigger the "Column has no default value" Error. Of course you could modify it to not require the database parameter
answered Sep 8, 2015 by rajesh
0 votes
i think that it is writing the String 'DEFAULT ' because it is escaped by pdo so there are parametres for bindvalue where you can specify the type of the value given so you can send a null with no quotes and it will be PDO::PARAM_NULL; and then default values will be put , but i'm not sure if there are similar parameters when binding with execute if(is_int($param)){$pdoParam = PDO::PARAM_INT;} elseif(is_bool($param)){$pdoParam = PDO::PARAM_BOOL;} elseif(is_null($param)){ $pdoParam = PDO::PARAM_NULL;} elseif(is_string($param)){$pdoParam = PDO::PARAM_STR;} else{$pdoParam = FALSE;} $this->_query->bindValue($k,$param,$pdoParam);
answered Sep 8, 2015 by rajesh
0 votes
Try changing this: $statement = $pdoObject-> prepare("INSERT INTO table1 (column1,column2) values (?,?)"); $statement->execute(array('value1','DEFAULT')); To this: $statement = $pdoObject-> prepare("INSERT INTO table1 (column1,column2) values (?,DEFAULT)"); $statement->execute(array('value1')); It seems to me that your original code will give you this: INSERT INTO table1 (column1,column2) values ('value1','DEFAULT') My code should give you this: INSERT INTO table1 (column1,column2) values ('value1',DEFAULT)
answered Sep 8, 2015 by rajesh