Best practise for a variable-length argument lists in mysqli bind_param

Question

I'm looking for the best approach / pattern / idiom for handling a variable-length argument for the mysqli bind_param method.

I've created a variadic function in PHP that is similar to mysqli_stmt::bind_param. Thus, the first required argument expects an string, the other arguments are optional, but the string length of the first argument must be equal to the amount of optional arguments.

The function definition:

function bind_param_test($types, ...$args){
    $typesArr = str_split($types);

    foreach($args as $i => $n) {

        switch ($typesArr[$i]) {
            case 'd':
                echo "Decimal: " . $n . '<br>';
                break;
            case 's':
                echo "String: " . $n . '<br>';
                break;
            default:
                break;
        }
    }
}

Now I'm trying to use it.

// ------------- CASE 1 ---------------------------
// In this case I have 5 arguments. So I can do this: 
$a = 10;
$b = 20;
$c = 'hello';
$d = 'cheese';
$e = 500;

if($a && $b && $c && $d && $e){
    bind_param_test('ddssd', $a, $b, $c, $d, $e);
}

/* OUTPUT:
Decimal: 10
Decimal: 20
String: hello
String: cheese
Decimal: 500
 */

echo '<hr>';

Please see case 2 and 3 below.

// ------------- CASE 2 ---------------------------
// I'm using 4 variables

$e = null;

if($a && $b && $c && $d && !$e){
    bind_param_test('ddss', $a, $b, $c, $d);
}

/* OUTPUT:
Decimal: 10
Decimal: 20
String: hello
String: cheese
 */

echo '<hr>';
// ------------- CASE 3 ---------------------------
// 3 variables
$d = null;
$e = null;

if($a && $b && $c && !$d && !$e){
    bind_param_test('dds', $a, $b, $c);
}

/* OUTPUT:
Decimal: 10
Decimal: 20
String: hello  */

echo '<hr>';

Case 2 and 3 are hard-coded, and thus not DRY. I want to make it DRY in case 4. Please see case 4.

Case 4: pattern to handle mysqli bind_param by array

// ------------- CASE 4 ---------------------------
// I want to have a more generic approach.

$argumentList = array(
    'a' => 10,
    'b' => 20,
    'c' => null,
    'd' => null,
    'e' => null,
);
$argumentList = array_filter($argumentList);

$actionStr = '';
foreach($argumentList as $k => $v){

    if(is_numeric($v)){
        $actionStr .= 'd';
    }

    if(is_string($v)){
        $actionStr .= 's';
    }
}

$varList = array_values($argumentList);
$param_arr = array();
$param_arr[] = $actionStr;
$param_arr = (array_merge($param_arr, $varList));
call_user_func_array('bind_param_test', $param_arr);

/* OUTPUT:
Decimal: 10
Decimal: 20  */

The question: can you find a better approaches then case 4? I'm asking because I think that case 4 is mysterious. I'm afraid that it might astonish other developers.

You could reduce your last 5 lines to `doSomething($actionStr, ...$argumentList);` — Nigel Ren, Jan 23 '19 at 10:46
What do you intend with `array_filter`? Should not only `null` but also `'', 0, '0', false, array()` be removed? — Pinke Helga, Jan 23 '19 at 10:50
@Quasimodo'sclone If it's null, then I dont want the item to be in the argument list. — Julian, Jan 23 '19 at 10:59
Then you have at least to specify that in a callback function. By default it works filtering everything which evaluates to false as listed in my comment above. — Pinke Helga, Jan 23 '19 at 11:05
@Quasimodo'sclone hmmmm I would like to know if there is room for improvement in case 4, and not in the callback function. — Julian, Jan 23 '19 at 11:08

Julian · Answer 1 · 2019-01-23T13:44:06.383

I would like to answer my own question. I just wanted to improve the case. Here is my improvement.

$argumentList = array(
    'a' => 10,
    'b' => 20,
    'c' => null,
    'd' => null,
    'e' => null,
);

$argumentList = array_filter($argumentList);

$mysqli_param_types = '';
foreach($argumentList as $k => $v){

    if(is_numeric($v)){
        $actionStr .= 'd';
    }

    if(is_string($v)){
        $actionStr .= 's';
    }
}

// instead of array_values() and array_merge()
// its more clear to iterate over argumentList (even if its twice)
$mysqli_reference_params[] =& $actionStr;
foreach($argumentList as &$var) {
    $mysqli_reference_params[] =& $var;
}

call_user_func_array('bind_param_test',  $mysqli_reference_params);

/* OUTPUT:
Decimal: 10
Decimal: 20  */

Ok why does this matter? It matters because I wanted to have a pattern that works well with bind_param and an variable length argument list.

Idiom: variable length prepared statement in mysqli

Idiom that handles variable length argument list in mysqli in combination with prepared statements. See here a real code idiom.

$sql = "    SELECT  p.`productID`,
                    p.`productCode`,
                    p.`productName`,
                    p.`productDescription`,
                    p.`productPrice`,
                    p.`productRating`,
                    p.`productDateTime`
            FROM `products` as p
            WHERE `p`.`productID` IS NOT NULL ";

        if($searchCode){
            $sql .= "AND p.`productCode` = ? ";
        }
        if($searchName){
            $sql .= "AND p.`productName` = ? ";
        }
        if($searchDescription) {
            $sql .= "AND p.`productDescription` = ? ";
        }
        if($searchPrice) {
            $sql .= "AND p.`productPrice` = ? ";
        }
        if($searchRating) {
            $sql .= "AND p.`productRating` = ? ";
        }
        if($searchDateTime) {
            $sql .= "AND p.`productDateTime` = ? ";
        }

        // Create mysqli_stmt
        $statement = $mysqli->prepare($sql);
        if ($statement instanceof \mysqli_stmt === false) {
            return null;
        }

        // Handle search variables through bind_param()
        $bindParamVarList = array(
            'productCode' => $searchCode,
            'productName' => $searchName,
            'productDescription' => $searchDescription,
            'productPrice' => $searchPrice,
            'productRating' => $searchRating,
            'productDateTime' => $searchDateTime,
        );
        $bindParamVarList = array_filter($bindParamVarList);

        if($bindParamVarList){
            $types = '';
            foreach($bindParamVarList as &$v){

                if(is_numeric($v)){
                    $types .= 'd';
                    $v = (float)$v;
                    continue;
                }

                if(is_string($v)){
                    $types .= 's';
                    continue;
                }
            }

            // call_user_func_array needs references and not values
            $mysqli_reference_params = array();
            $mysqli_reference_params[] =& $types;
            foreach($bindParamVarList as &$bindParamVar) {
                $mysqli_reference_params[] =& $bindParamVar;
            }

            call_user_func_array(array($statement,  'bind_param'),  $mysqli_reference_params);
        }

        $statement->execute();
        $statement->store_result();
        $amount = $statement->num_rows;

        $statement->bind_result($productID,
                                $productCode,
                                $productName,
                                $productDescription,
                                $productPrice,
                                $productRating,
                                $productDateTime
        );

        $products = array();
        $productsSet = array();
        while($statement->fetch()){
            $product = array();
            $product['productID'] = $productID;
            $product['productCode'] = $productCode;
            $product['productName'] = $productName;
            $product['productDescription'] = $productDescription;
            $product['productPrice'] = $productPrice;
            $product['productRating'] = $productRating;
            $product['productDateTime'] = $productDateTime;

            $products[] = $product;
        }

        // JavaScript is only able to work with indexed Array's
        $productsSet[] = $products;
        $productsSet[] = $amount;

Pinke Helga · Answer 2 · 2019-01-23T11:42:53.910

You could reduce the array handling all type you want and throw an exception on disallowed types. Further more modern PHP supports array destruction ...$array and [$var1, $var2] = $array.

You also should throw an exception when I pass a $actionStr = 'xyz'.

This example is according to your order is_numeric, is_string which handles both, '123' and 123, as numeric:

// ------------- CASE 4 ---------------------------

$argumentList = array(
  'a' => 'ten',
  'b' => 20,
  'c' => '0',
  'd' => null,
  'e' => 0,
//  'e' => false,   // throws Exception
//  'f' => [1,2,3], // throws Exception
);


[$argumentList, $actionStr] = array_reduce
(
  $argumentList,
  function(array $aggr, $v) : array
  {

    if(null === $v)
      return $aggr;

    if(is_numeric($v))
    {
      $aggr[0][] = $v;
      $aggr[1]  .= 'd';
      return $aggr;
    }

    if(is_string($v))
    {
      $aggr[0][] = $v;
      $aggr[1]  .= 's';
      return $aggr;
    }

    throw new InvalidArgumentException('Type ' . gettype($v) . ' not allowed.');

  },
  [[], '']
);

doSomething($actionStr, ...$argumentList);

Check is_string first if you want '123' to be handled as string instead of numeric.

Best practise for a variable-length argument lists in mysqli bind_param

2 Answers2