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.

I hope you don't think your first code block as a *prepared statement*, as the prepared statement bit is your second code block. Is it a requirement to produce your sql in 5 parts? Also, what is the default value? — Ekin, Feb 19 '16 at 23:37
The default value for the dates is '1000-01-01 00:00:00' and the default value for month_interval is 0, the rest has a default of NULL. — Kellen Stuart, Feb 20 '16 at 03:05

score 3 · Answer 1 · edited May 23 '17 at 12:25

3

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);
}

edited May 23 '17 at 12:25

Community

1
1

answered Jul 05 '16 at 20:15

Stefano Giacone

2,016
3
27
50

This looks like what I was looking for. Let me give this a try and I'll accept if it works. Essentially that `DEFAULT(field)` function is what I was looking for. I think the `IFNULL` part can be done on the php end. – Kellen Stuart Jul 05 '16 at 21:30
In fact, I can do `INSERT INTO table SET date=DEFAULT;` – Kellen Stuart Jul 05 '16 at 21:53

score 2 · Answer 2 · answered Feb 19 '16 at 23:47

2

Just a thought, if you have a condition when you do not know all the values, you should prepare the statement to do not even add those values.

Also in some cases (for example on autoincrement fields) you can just insert a NULL which will trigger the default value to be inserted, BUT do be carefull if the field definition does allow NULL's inserting a NULL will insert a NULL.

answered Feb 19 '16 at 23:47

Emil Borconi

3,326
2
24
40

What about the case where the value is something like `2015-01-01 23:32:23` and the user inputs `nothing` to indicate that the value is either `null` or `default`. You still want it to be in the prepared statement – Kellen Stuart Jul 05 '16 at 21:27
for me this is even more important because I have scripts running calculations on the dates in the database. If the value ever pops out as `NULL` that is a big big problem. – Kellen Stuart Jul 05 '16 at 21:33
I strongly recommend against this for portability reasons, it doesn't work for all versions, my server version is 5.6.37 and it doesn't work – Accountant م Oct 17 '17 at 23:32

fusion3k · Accepted Answer · 2016-02-20T01:23:49.673

Your question addresses an interesting subject, in my opinion.

By a rapid search, I don't have found any predefined solution, but I will continue search in the next days.

I have elaborate a solution, but it is a bit twisted and not totally clean.

First of all, I redefine the query with placeholders:

$query = "
    INSERT INTO table
                (
                    tag,
                    month_interval,
                    month_interval_lastupdated,
                    date_due,
                    date_due_lastupdated,
                    date_completion,
                    date_completion_lastupdated,
                    activeNotification,
                    date_lastmodified
                )
         VALUES (<tag>,<month_interval>,NOW(),<date_due>,NOW(),<date_completion>,NOW(),<activeNotification>,NOW())
";

In this example, I use <> to surround placeholder, but you can choose your preferred placeholder, making sure that they do not confuse with others elements of the query.

Then, I init an associative array with keys as placeholders names and values to be bind, and a variable ($types) with the complete set of bind types:

$values = array( 'tag'=>$tag, 'month_interval'=>$month_interval, 'date_due'=>$date_due, 'date_completion'=>$date_completion, 'activeNotification'=>$activeNotification );
$types = 'sisss';

Then, the most important line. I init an array in this strange way:

$bind = array( Null, '' );

The index 0 is set to Null, because is reserved for the stmt object (created below); the index 1 is an empty string that will be filled with necessary bind types.

Now I perform a foreach loop through all elements of array $values:

$i = 0;
foreach( $values as $key => $val )
{
    if( is_null( $val ) )
    {
        $query = str_replace( '<'.$key.'>', 'DEFAULT', $query );
    }
    else
    {
        $query    = str_replace( '<'.$key.'>', '?', $query );
        $bind[1] .= $types[$i];
        $bind[]   = &$val;
    }
    $i++;
}

In that loop, if the value is null I replace the corresponding query's placeholder with mySQL keyword DEFAULT. Otherwise, I replace placeholder with a ?, I add the corresponding $types substring to $bind[1], and I append the value (by reference) to $bind array.

At this point, the real query is ready to go, and I can prepare it:

$stmt = $db->prepare( $query ) or die( $stmt->error );

and, if there are not default values (count($bind)>2), I can bind it:

if( count($bind)>2 )
{
    $bind[0] = $stmt;
    call_user_func_array( 'mysqli_stmt_bind_param', $bind );
}

As said before, I set $bind[0] to stmt object returned by ->prepare, then I use call_user_func_array to perform mysqli_stmt_bind_param. I can't directly call ->bind_param because I have a variable arguments number.

At the end of this eccentric process, finally I can execute the query:

$stmt->execute() or die( $stmt->error );

I have tested this code, and it works.

The main problem reside in the is_null( $val ): using an array, I can't use isset as test, because every element of an array is even set. The code works if all the field with placeholder that allows Null value has Null ad default value.

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

3 Answers3

Related