How can I write less code with bindParam?

Question

I am using PDO to insert data into a table. The problem is, there's a lot of columns, meaning the query is very long.

This is the code I have currently:

$stmt = $con->prepare("INSERT INTO table (crips, biscuits, chocolate, cakes, smarties, yogurts, apples, oranges) VALUES (:crisps, :biscuits, :chocolate, :cakes, :smarties, :yogurts, :apples, :oranges)");
$stmt->bindParam(':crisps', $crisps);
$stmt->bindParam(':biscuits', $biscuits);
$stmt->bindParam(':chocolate', $chocolate);
$stmt->bindParam(':cakes', $cakes);
$stmt->bindParam(':smarties', $smarties);
$stmt->bindParam(':yogurts', $yogurts);
$stmt->bindParam(':apples', $apples);
$stmt->bindParam(':oranges', $oranges);
$stmt->execute();

Is there an easier way, rather than having a different line of code for every value?

Is something like this possible?

foreach(value) {
   $stmt->bindParam(':value', $value);
}

Answer 1

You could use the ? identifier and feed the values in order:

$stmt = $con->prepare("
  INSERT INTO table 
    (crips, biscuits, chocolate, cakes, smarties, yogurts, apples, oranges) 
  VALUES 
    (?, ?, ?, ?, ?, ?, ?, ?)
");

$stmt->execute([$crisps, $biscuits, $chocolate, $cakes, $smarties, $yogurts, $apples, $oranges]);

It saves quite a lot of code already, :named parameters have their uses but you can't use them in combination with ?.

---

You can use a loop but that requires a predefined array to loop through in the first place.

For named parameters create an array like so:

$list = [
  ':crisps' => $crisps,
  ':biscuits' => $biscuits,
  # etc
];

foreach($list as $k => $v){
  $stmt->bindValue($k, $v);
}

$stmt->execute();

And this for the ? indexed parameters:

$list = [
  $crisps,
  $biscuits,
  #etc
]

foreach($list as $k => $v){
  $stmt->bindValue($k+1, $v);
}

$stmt->execute();

Please note that I've changed the method from bindParam() to bindValue() as in your code example there is no benefit to use that method.

Then there is the question of using bindValue() at all because that method only aditional feature is that it is able to cast a value to a specific type, using the 3th parameter something you're not doing anyways.

So in essence, you can avoid that foreach loop all-together by feeding $list in the execute() method directly:

$stmt->execute($list);

If you really want to use bindParam() then just add the reference symbol before the variable name:

$list = [
  ':cakes' => &$cakes
];

Answer 2

Well your foreach can be used, but you need to build that array, which still takes lines of code
-- Mark Baker.

This is very true.

---

NOTE: Please read here as to why I'm using BindValue rather than BindParam. Thanks to Xorifelse for the reminder.

The Codesee, If you really care that much about lines of code, then I would suggest you construct yourself a database interaction class which you can use to do these things. Once the class is written, it will save you code layout repetition.

Your idea about using foreach is not far off, and could be used to make some repetition savings, for instance you can construct an array (whereby the reference var name is the key) to closely associated each variable with its respective SQL placeholder.

so:

$save['crisps'] = $crisps;
$save['biscuits'] = $buiscuits;
...

This would replace your stand-alone variables ($crisps, etc.) with a collection (array) of variables, with the array key being the reference name.

Then

foreach($save as $key=>$value){ 
    $stmt->bindValue(":".$key, $value);
}
unset($key,$value);

NOTE: I have not put in any safety cleaning or checking routines here, as that's somewhat out of scope but I recommend doing so (a preg_replace or similar) to ensure invalid characters are stripped beforehand from the key value.

Example:

$key = preg_replace("/[^a-z]/i","",$key);

---

Are you sure I need a predefined array as such? Can it be simplified anymore, as $v is always going to be the same as $k, just with a $ in front of it (if you get what I mean)?
-- Codesee

The problem with this is as your variables are not collected into an array or object, how is the code meant to know which variables you want to add to the INSERT SQL? You would still need a line of code for each one.

Answer 3

Xorifelse already mede this topic clear, so I can make a very small addition only. in PHP you can always find an odd function that can help you to do something extravagant.

So if you prefer to use named placeholders, you can keep with them but still use a shortcut with execute():

$stmt = $con->prepare("INSERT INTO table (crips, biscuits, chocolate, cakes, smarties, yogurts, apples, oranges) VALUES (:crisps, :biscuits, :chocolate, :cakes, :smarties, :yogurts, :apples, :oranges)");
$stmt->execute(compact('crisps', 'biscuits', 'chocolate', 'cakes', 'smarties', 'yogurts', 'apples', 'oranges'));

But personally I would prefer positional placeholders.