PHP MySQL-如何在准备好的语句中插入默认值 [英] PHP MySQL - How to insert default value in a prepared statement

问题描述

假设我有一个准备好的陈述，如下所示:

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.

在准备好的语句中是否有可以替换的关键字可以告诉MySQL插入默认值?

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

类似的东西:

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

日期字段的默认值为'1000-01-01 00:00:00'，月份字段的默认值为0，其余字段的默认值为NULL.

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.

推荐答案

我认为您的问题涉及一个有趣的主题.

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.

然后，我用键作为占位符名称和要绑定的值，以及一个具有完整绑定类型集的变量($types)来初始化一个关联数组:

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, '' );

将索引0设置为Null，因为它是为stmt对象保留的(在下面创建)；索引1是一个空字符串，将填充必要的绑定类型.

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.

现在，我对数组$values的所有元素执行foreach循环:

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

在该循环中，如果该值为null，则将相应查询的占位符替换为mySQL关键字DEFAULT.否则，我将占位符替换为?，将相应的$types子字符串添加到$bind[1]，然后将值(通过引用)附加到$bind数组.

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

，并且，如果没有默认值(count($bind)>2)，我可以将其绑定:

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

如前所述，我将$bind[0]设置为->prepare返回的stmt对象，然后使用call_user_func_array执行mysqli_stmt_bind_param.我不能直接调用->bind_param，因为我有一个可变的参数编号.

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.

主要问题在于is_null( $val ):使用数组，我不能使用isset作为测试，因为数组的每个元素都已设置.如果所有带有占位符且允许Null值的字段都具有Null广告默认值，则该代码有效.

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-如何在准备好的语句中插入默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！