Why is emulation of a prepared statement terminated with Null?

Question

From the PHP manual it says that:

PDO :: ATTR_EMULATE_PREPARES Enables or disables emulation of prepared statements. Some drivers do not support their own prepared statements or have limited support for them. Use this parameter to force PDO to always imitate prepared statements (if TRUE) or try to use your own statements (if FALSE). He will always return to the prepared operator emulation if the driver cannot successfully prepare the current request. Requires a bool.

I have a query that looks something like this:

$type = PDO::PARAM_INT; if($_POST['code'] == "") $type = PDO::PARAM_NULL; $stmt = $dbh->prepare("UPDATE Product SET code=? WHERE id=?"); $stmt->bindValue(1, $_POST['code'], $type); $stmt->bindValue(2, $_SESSION['id'], PDO::PARAM_INT); $stmt->execute(); 

I understand that without setting the next statement, I would get '0' instead of NULL (when $_POST['code'] == "" ) in my database using the code above. Why is this so?

 $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); 

UPDATE:

Although disabling emulation works for me, I would prefer to use it because, like the OP in this question , basically all my requests are executed only once per script execution. Therefore, the availability of prepared instructions does not give me any advantages and creates unnecessary calls to the database, as shown in the general query log below:

 Emulation Disabled 22 Connect user@localhost on Database 22 Prepare UPDATE Product SET code=? WHERE id=? 22 Execute UPDATE Product SET code='abc' WHERE id='123' 22 Close stmt 22 Quit Emulation Enabled 22 Connect user@localhost on Database 22 Query UPDATE Product SET code='abc' WHERE id='123' 22 Quit 

Any help on resolving the NULL problem is welcome.

Answer 1

Try binding null to PDO::PARAM_NULL .

 $type = PDO::PARAM_INT; if($_POST['code'] == "") { $_POST['code'] = null; $type = PDO::PARAM_NULL; } $stmt = $dbh->prepare("UPDATE Product SET code=? WHERE id=?"); $stmt->bindValue(1, $_POST['code'], $type); $stmt->bindValue(2, $_SESSION['id'], PDO::PARAM_INT); $stmt->execute(); 

Actually you do not need to use PDO::PARAM_NULL , the code below also inserts null .

 $stmt->bindValue(1, $_POST['code'] ? $_POST['code'] : null, PDO::PARAM_INT);