Preparing a MySQL INSERT/UPDATE statement with DEFAULT values
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, abc2, 1, 2010-03-17 01:00:00, lalala3, 99, 2009-12-24 18:00:00, abc
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 DATABEGIN 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
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)