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

Question 1

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:foundationtestssrcassetsphpaddProducts.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?

Question 2

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

Question 3

Colons are required in the SQL statement, to indicate which identifiers are placeholders.

Colons in the execute() or bindParam() calls are optional. The documentation specifies them, but the implementation is clever enough to figure out what you mean if you leave them out (what else could you mean?).

Question 4

I don't know much about PDO, but my feeling is there is something wrong with the way you bind the parameters. However, the easiest way to tell for sure is to see the actual query.

According to the docs, you should be able to see the generated query as it went to SQL in $stmt->queryString. It's not possible to see right now because you are binding the parameters to the statement after you are outputting $stmt.

Do a print_r() after you bind the parameters (or maybe even after execution of the query, I don't know). You should get the real query string, and get to the bottom of the problem.

Question 5

EDIT 07/2015 (question has been edited since original answer but underlying principles are the same)

Never SELECT * in a production environment, it will only come back to bite you in weird, unpredictable and seemingly unrelated ways. By specifying the columns you want, you will ensure that column ordering, data-type, constraint and all sorts of other elements won't cause you problems in the long run.

This answer is still mostly valid so I'll leave it here as-is, but the main take-away is: use PDO, it does 98% of the things you'll ever need with a much cleaner and more succinct API over the same back end. If you need a more complex RDBMS-specific API then you'll already understand the problems you have and why you need mysqli etc instead.

SELECT * doesn't work very well with MySQLi prepared statements. It's one of the major reasons I recommend PDO instead - that and the ridiculous requirement to bind variable references instead of values to the parameters.

$stmt->bind_result($row);

This is not binding the result row to a variable, it would just be binding a single column. And because you have used SELECT *, it doesn't do what you want it to.

If you do want to use MySQLi over PDO (which, as I say, I would recommend) there are a few good examples of how to SELECT * in the comments like this one on the bind_result() manual page.

Or you can just specify the columns you want to retrieve:

$sql_con = new mysqli('db', 'username', 'password', 'database');

if($stmt = $sql_con->prepare("SELECT name, countryCode FROM Country WHERE countryCode = ?")) {

   $stmt->bind_param("s", $country_code); 
   $stmt->execute(); 
   $stmt->bind_result($name, $countryCode);

   while ($stmt->fetch()) {
     // Because $name and $countryCode are passed by reference, their value
     // changes on every iteration to reflect the current row
     echo "<pre>";
     echo "name: $namen";
     echo "countryCode: $countryCoden";
     echo "</pre>";
   }

   $stmt->close();

EDIT based on your new code, you should be doing this:

// $date1 will be int(2010), $date2 will be int(1980) because you didn't
// quote the strings!
//$date1 = 2012-01-01;
//$date2 = 2012-01-31;

// Connect to DB
$sql_con = new mysqli('db', 'username', 'password', 'database');

// Check for connection errors here!

// The query we want to execute
$sql = "
  SELECT eventLogID
  FROM Country
  WHERE countryCode = ?
  AND date BETWEEN ? AND ?
";

// Attempt to prepare the query
if ($stmt = $sql_con->prepare($sql)) {

  // Pass the parameters
  $date1 = '2012-01-01';
  $date2 = '2012-01-31';
  $stmt->bind_param("sss", $country_code, $date1, $date2); 

  // Execute the query
  $stmt->execute();
  if (!$stmt->errno) {
    // Handle error here
  }

  // Pass a variable to hold the result
  // Remember you are binding a *column*, not a row
  $stmt->bind_result($eventLogID);

  // Loop the results and fetch into an array
  $logIds = array();
  while ($stmt->fetch()) {
    $logIds[] = $eventLogID;
  }

  // Tidy up
  $stmt->close();
  $sql_con->close();

  // Do something with the results
  print_r($logIds);

} else {
  // Handle error here
}

Question 6

Create your table with an ~~identity~~ autoincrement column:

CREATE TABLE table_name
(
   id INTEGER AUTO_INCREMENT PRIMARY KEY,
   -- other columns to follow
)

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

Answers

php,pdo,prepared-statement