CODEWITHSUNDEEP
phppdoinsertdefault-value
Samuele
Samuele

Reputation: 510

How to insert DEFAULT when the variable in BindParam is null

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;

Upvotes: 5

Views: 3353

Answers (1)

aaz
aaz

Reputation: 5196

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).

Upvotes: 8

Related Questions