Logic to NOT insert a value to column X in a prepared statement

Question

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

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.

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.

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 />

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...^^

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?

score 3 · Accepted Answer · edited Aug 30 '19 at 13:11

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

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

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

@GrumpyCrouton you're right; as long as `$productDataInput` only had those 3 values in it you could go to a named parameter form of the query and it would work fine. — Nick, Aug 30 '19 at 13:02

score -2 · Answer 2 · answered Aug 30 '19 at 12:51

-2

You forgot to bindparam

$insertion = $connection->prepare("INSERT INTO products_tbl(product_name, product_manufacturer, product_category)  VALUES(:prod_name , :prod_manufacturer, :prod_category)");

$insertion->bindParam(':prod_name', $var);
$insertion->bindParam(':prod_manufacturer', $var2);
$insertion->bindParam(':prod_category', $var3);
$insertion->->execute();

Now just change the $var, var2, var3 to the vars you wish

answered Aug 30 '19 at 12:51

Otávio Barreto

1,536
3
16
35

1

OP says sometimes the query shouldn't receive 3 values, or am I misreading that? – GrumpyCrouton Aug 30 '19 at 12:54
he updated the question I will look again – Otávio Barreto Aug 30 '19 at 12:54
You don't have to `bindParam` if you `bindValue` – Nick Aug 30 '19 at 13:05
yeah, it was before he updates the question he showed the code without `bindvalue` or `bindParam` – Otávio Barreto Aug 30 '19 at 13:07

Logic to NOT insert a value to column X in a prepared statement

2 Answers2

Linked