mysqli bind_param中变长参数列表的最佳实践

我想回答我自己的问题。我只是想改进这个案子。 这是我的进步。

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

好吧,这有什么关系?这很重要,因为我想有一个很好的模式 bind_param 以及可变长度参数列表。

习惯用法:mysqli中的可变长度准备语句

在mysqli中处理可变长度参数列表和准备好的语句的习惯用法。

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

可以减少处理所需所有类型的数组,并对不允许的类型引发异常。更现代的PHP支持数组销毁 ...$array 和 [$var1, $var2] = $array .

当我通过一个 $actionStr = 'xyz'

这个例子是根据你的命令 is_numeric is_string 两者兼得, '123' 和 123

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

支票 如果你想先 123年