在准备好的语句中不向X列插入值的逻辑 [英] Logic to NOT insert a value to column X in a prepared statement

问题描述

因此，我有带有列X，Y，Z的表A.

So I have table A with Columns X,Y,Z.

Y，Z列的DEFAULT值为"diverse". 当php脚本传递用户输入时，应该有可能在inputobject中包含3个值，其中Y列和Z列的两个值为NULL.

Columns Y,Z have a DEFAULT value of "diverse". When the php script delivers the userinput, it shall be possible that of the 3 values contained in the inputobject, the two for column Y and Z are NULL.

我想创建一些PHP逻辑来评估输入并执行准备好的PDO查询，如果相应的输入为空字符串，则Y和Z列完全不受影响，因此mysql可以将它们设置为DEFAULT值.

I want to create some PHP logic which evaluates the input and executes a prepared PDO query, where column Y and Z are NOT affected at all if the respective input is empty string, so they can be set to DEFAULT value by mysql.

当前，我的PDO准备语句如下:

Currently, my PDO prepared statement looks like this:

  $insertion = $connection->prepare("INSERT INTO products_tbl(product_name, product_manufacturer, product_category)
                                     VALUES(?,?,?)
                                     ");

和我尝试建立以控制实际插入的逻辑如下:

and the logic I tried to build controlling the actual Insertion looks like this:

$insertion->bindValue(1, $productDataInput["productNameInput"]);

  if($productDataInput["productManufacturerInput"] !== NULL){
     $insertion->bindValue(2, $productDataInput["productManufacturerInput"]);
  }

  if($productDataInput["productCategoryInput"] !== NULL){
     $insertion->bindValue(3, $productDataInput["productCategoryInput"]);
  }

在这里，我遇到以下错误:

Here, I get the following error:

PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in <b>D:\foundationtests\src\assets\php\addProducts.php</b> on line <b>38</b><br />

因此，我猜这种用3个值插入的查询准备查询的方式，但是仅接收1个或2个值，是行不通的. 但是，对于准备好的语句而言，我还很陌生，我真的不知道如何在不编写超级冗余代码的情况下解决该问题，在这里我将为值2或3或两者均为空的每个用例创建定制的准备好的语句.这样的解决方案也无法真正很好地"扩展，因此我想学习其他更有效，更整洁的方法... ^^

So I guess this way of preparing a query taking 3 values for insertion, but then receiving only 1 or 2 values, doesnt work. However, Im pretty new to prepared statements and I don't really know how to tackle this problem without writing super redundant code, where I would create custom prepared statements for each usecase where either value 2 or 3 or both of them are empty. Such solutions also don't really scale "well" so I would like to learn other, more efficient and neat ways...^^

例如，我了解到DEFAULT()能够触发要设置为列的默认值吗?有什么方法可以在准备好的PDO语句中动态插入DEFAULT?

For example, I learned about DEFAULT() being able to trigger the default value to be set to a column? Is there some way to dynamically insert DEFAULT in a prepared PDO statement?

推荐答案

您可以使用

You can use DEFAULT() to insert the default value for a column, putting it in a test with IFNULL:

$insertion = $connection->prepare("INSERT INTO products_tbl(product_name, product_manufacturer, product_category)
                                   VALUES(?,
                                          IFNULL(?, DEFAULT(product_manufacturer)),
                                          IFNULL(?, DEFAULT(product_category))
                                          )
                                   ");

然后，当需要默认值时，可以将NULL传递给bindValue，即可以删除if测试:

And then you can pass NULL to bindValue when you want the default value i.e. you can remove your if tests:

$insertion->bindValue(1, $productDataInput["productNameInput"]);
$insertion->bindValue(2, $productDataInput["productManufacturerInput"]);
$insertion->bindValue(3, $productDataInput["productCategoryInput"]);

如果查询中使用的三个值是$productDataInput中的 only 值，则可以使用命名参数来进一步简化此操作

If the three values used in the query are the only values in $productDataInput, you could simplify this further using named parameters to

$insertion = $connection->prepare("INSERT INTO products_tbl(product_name, product_manufacturer, product_category)
                                   VALUES(:productNameInput,
                                          IFNULL(:productManufacturerInput, DEFAULT(product_manufacturer)),
                                          IFNULL(:productCategoryInput, DEFAULT(product_category))
                                          )
                                   ");
$insertion->execute($productDataInput);

这篇关于在准备好的语句中不向X列插入值的逻辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！