PHP MySQL - How to insert default value in a prepared statement

Question

Let's say I have a prepared statement that looks like this:

$insertSql = "";
$insertSql .= "INSERT INTO table";
$insertSql .= "(tag,month_interval,month_interval_lastupdated,date_due,date_due_lastupdated,date_completion,date_completion_lastupdated,";
$insertSql .= "activeNotification,date_lastmodified) ";
$insertSql .= "VALUES (?,?,NOW(),?,NOW(),?,NOW(),?,NOW())";

But sometimes some of those question marks would not be set.

Is there a keyword I can substitute in the prepared statement that tells MySQL to insert the default?

Something like:

if($stmt = $mysqli->prepare($insertSql)) {
   if(!isset($tag)) {
      // code to find first question mark and replace w/ default value
      }
      $stmt->bind_param('sisss',$tag,$month_interval,$date_due,$date_completion);
      $stmt->execute();
}

The default value for the date fields is '1000-01-01 00:00:00' and the default value for month_interval is 0, the rest of the fields have a default of NULL.

Answer 1

I had the same issue, so I adapted this answer https://stackoverflow.com/a/13867665/1251063 to php prepared statement:

if(!($stmt = $this->conn->prepare("INSERT INTO mytable (myfield) VALUES (IFNULL(?,DEFAULT(myfield)))"))){
    throw new Exception("Prepare failed: (" . $this->conn->errno . ") " . $this->conn->error);
}

if(!($stmt->bind_param("s",$myfield))) { //$myfield is a variable that can be null, if so the default value for such field will be inserted
    throw new Exception("Bind_param failed: (" . $this->conn->errno . ") " . $this->conn->error);
}

if(!$stmt->execute()) {
    throw new Exception("Execute failed: (" . $stmt->errno . ") " . $stmt->error);
}