How to insert DEFAULT when the variable in BindParam is null

Question

I'm using PDO prepared statement. I want to BIND a variable BUT if the variable is NULL it have to INSERT in MYSQL the DEFAULT VALUE of the field.

I'm trying with IFNULL(:User_Login__Is_Active, DEFAULT), And I tried also: COALESCE(:User_Login__Is_Active, DEFAULT), Same error:

PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax;

How can you do that?

Look at this example:

$stmt = $this->pdo->prepare('INSERT INTO user_login
                                    ( User_Login__ID,
                                      User_Login__Is_Active,
                                      User_Login__Created_Date )
                               VALUES ( 
                                      :User_Login__ID,
                                      IFNULL(:User_Login__Is_Active, DEFAULT),
                                      :User_Login__Created_Date )');


$stmt->bindParam(':User_Login__ID', $this->User_Login__ID, PDO::PARAM_INT);
$stmt->bindParam(':User_Login__Is_Active', $this->User_Login__Is_Active, PDO::PARAM_STR, 100);
$stmt->bindParam(':User_Login__Created_Date', $this->User_Login__Created_Date, PDO::PARAM_STR, 100);


$this->User_Login__Is_Active = null;

Answer 1

The keyword DEFAULT can't be used inside an expression, it can only replace the entire expression.

However, the function DEFAULT() can be used anywhere.

So replace DEFAULT in your example with DEFAULT(User_Login__Is_Active).