How to specify "default" in a parameterized PHP PDO insert?

Question

At the moment, I came up with a workaround, but I'm curious how future links are ...

If the mysql table column is defined as NOT NULL but has a default value, this default value will be inserted if the column name is not specified in the insert statement, OR if you specify the DEFAULT keyword as the value. If you use NULL as the value in the NOT NULL column, even if the column has a default value, it will try to insert NULL and throw an error.

But is there a way to specify the DEFAULT keyword as a value in a parameterized INSERT statement? I don’t want to just omit the column from the insert statement because I want to use the same statement with multiple datasets, some of which really have data for this column.

Answer 1

Of course, there is no way to use the parameters for the task.

However, lowering the field is not a difficult or unusual task to refuse such an opportunity.

Quite the contrary, at a certain level you will see that each request is more dynamic, more or less. And you need to learn how to tame them. This is again not a big problem. Specifically for an insert or update request that requests automation even under normal circumstances.

A simple helper function that takes an array with data and an array with resolved field names, returning a query with placeholders and an array with data for binding, will easily fix your problem.

Answer 2

If you need an INSERT statement that treats NULL as the default value for a column, here is the solution:

I created a table:

CREATE TABLE `foo` ( `x` INT DEFAULT '768' ) 

Then I checked a couple of prepared INSERT statements with PDO:

 $stmt = $pdo->prepare("INSERT INTO foo (x) VALUES (COALESCE(?, DEFAULT(x)))"); $stmt->execute( [ 42 ] ); // inserts a real value $stmt->execute( [ NULL ] ); // inserts the column default value 

I confirmed the test:

 mysql> select * from foo; +------+ | x | +------+ | 42 | | 768 | +------+ 

Tested with PHP 5.5.12 and MySQL 5.6.17.