# Creating comma-separated '?' placeholders for SQL query parameters

I have come up with a code that takes the total number of an array and creates ? for each, then separates them by a comma; leaving me a list like the one below:

?, ?, ?, ?, ?...


I am using this for my sql queries with prepared statements, instead of having to create the prepared statement each time, I want to just have an easily accessible class that does the heavy work.

So far I have a for loop code which I came up with to get the comma-separated question marks. First I create an array of [0] => ? ... based on the total number of the original array and then implode the value and add the comma between each element.

<?php
$arr = array( 'name' => 'James Bond', 'age' => 60, 'hobbies' => 'Saving England', 'country' => 'England', 'language' => 'English', 'children' => 'too many to count' ); for($i = 0; $i < count($arr); $i++) {$question[$i] = '?'; } print_r( implode(', ',$question) );
// outputs: ?, ?, ?, ?, ?, ?
?>


Now my question is, is there a more efficient way of achieving this, such as a one-liner that just takes the total array number and creates the number automatically?

The ? array can be filled using array_fill:

implode(', ', array_fill(0, sizeof($arr), '?'))  • That's a beautiful one-liner... Thank you very much – Sam Oct 4 '17 at 13:06 Xiaoy312's suggestion is one of the common methods used to generate comma-separated placeholder strings. It generates an array containing ? valued elements, then joins them into a string using commas as glue. Another common method is: rtrim(str_repeat('?,',sizeof($arr)),',');


or the equivalent:

substr(str_repeat('?,',sizeof($arr)),0,-1);  My above methods directly generate a string (no temporary array) then merely trims the last character (comma) off the end. All three mentioned methods will use 3 functions and no conditionals. All, in my opinion, are equally elegant and in all reasonable cases the performance will seem equal to the end user. In my few simple tests on 3v4l.org, I found that the rtrim(str_repeat(sizeof())) method is slightly faster than Xiaoy's implode(array_fill(),sizeof()) method and my substr(str_repeat(sizeof()) method. I mean you could go with: implode(',',array_map(function(){return '?';},$arr)); but it seems less elegant and is less efficient.

rtrim(array_reduce($arr,function($carry){return $carry.'?,';}),','); is surprisingly efficient but like array_map() it is less compact/elegant than other fill/repeat methods. I don't have any formal benchmark results to post. Like all speed tests, the only relevant information for your project(s) is performance in your exact environment. Always do your own benchmarks, but be careful not to waste time micro-optimizing. As an aside, I would like to suggest some micro-optimizations that I use as a matter habit... When you are writing for loops, always cache the count() by setting it to a variable in the first expression. This will ensure that you aren't calling count() on every iteration. for($i = 0, $count = count($arr); $i <$count; $i++)  When the result of pre-incrementing and post-incrementing is the same, I use pre-incrementing as the default because I read that it is ever-so-slightly faster. for($i = 0, $count = count($arr); $i <$count; ++$i)  When you are assigning array keys starting from 0, you can let php index the array for you. $question[] = '?';


When you want to loop through all elements in an array, you can use foreach() to avoid counting the array size as well as declaring and incrementing a "counter".

foreach($arr as$v){
$question[]='?'; }  • Very good answer... Thank you very much, I appreciate the time. Your considerations are good insight. – Sam Oct 4 '17 at 13:09 • @YourCommonSense that is an unhelpful comment. You know you have my ear/respect, so why not say something educational? Oct 11 '17 at 11:02 • @YourCommonSense what do you mean? – Sam Oct 11 '17 at 11:16 • @Samuel I mean that none of these considerations are of even smallest value but rather harmful. All this rant on which method is faster do a severe harm. You should never benchmark such pointless code parts like it is suggested here. For such a trifle one-time string operation all methods are equally faster. Give it a thought and you will lose a lot of your valuable time for nothing. So just unsee it. Syntax sugar suggestions at the bottom are good, but only in terms of a nicer code, but not in terms of performance at all. Oct 11 '17 at 11:32 • I am confident that I have nothing to apologize for in my post. The question asked for efficient one-liners, I endeavored to provide an assortment of viable options and stated that the speed impacts will go unnoticed. I am neither deceiving anyone nor doing any harm. When settling in on why I consistently use certain snippets when I write code, I prefer to have a rational reason(s) -- sometimes micro-optomization breaks the tie. Oct 11 '17 at 13:08 Given your starting array, which appears to be a field-value map, I find it odd that you want to go with question-mark-style query parameters instead of named placeholders. You might find named placeholders more useful should you ever need to do some debugging around your queries. This can also simplify your SQL preparation. For example, one might typically build field and/or parameter strings directly from an input array like you have shown. You can then also work directly with such an array when executing the prepared statement. For example in PDO: $paramArray = [];
foreach($inputArray as$key => $value) {$paramArray[':' . $key] =$value;
}
$fieldString = '' . implode(',' array_keys($inputArray)) . '';
$paramString = implode(',', array_keys($paramArray));
$sql = " INSERT INTO table ({$fieldString})
VALUES ({$paramString}) ";$stmt = $pdo->prepare($sql);
$stmt->execute($paramArray);

• for the automatically generated query it doesn't make difference which placeholder type to use, whereas positional placeholders are less error prone, for example they will go all right if you have a space in the field name. In the end you have more complex code that is less convenient. I doubt it could be called a simplification. Oct 11 '17 at 10:25