mysqli_prepare 与 PDO

2021-12-25 00:00:00 php mysqli

背景

我正在尝试编写函数查询

I'm trying to write a function query

query('type', 'parameters', 'bind_types')

我可以调用它来进行简单的查询.所有的mySQL查询都在函数

which I can call to make simple queries. All the mySQL queries are in the function 

grab_sql()

所有的绑定都发生在函数bind()

All the binding takes place in the function bind()

call_user_func_array 需要引用才能正常运行，所以我写了 ref_arr 来适应.

call_user_func_array needs references to function correctly so I wrote ref_arr to accomodate.

问题是我没有得到我需要的结果 - 它们发布在结果下方.我认为问题在于结果的绑定，正如我在这部分猜测的那样.

Problem is that I'm not getting the results back that I need - they are posted below under results. I think the issue is in the binding of the reuslts as I kind of guessed on that part.

研究

  • 关于预处理语句的信息此处
  • 有关call_user_func_array 的信息此处
  • 关于call_user_func_array 引用必要性的信息这里
  • Info on prepared statements here
  • Info on call_user_func_array here
  • Info on the neccessity of references for call_user_func_array here

问题:如何修改此代码以正确获得正确的结果?

QUESTION: How can I modify this code to correctly get the correct results?

代码

  function ref_arr(&$arr)
    { 
    $refs = array(); 
    foreach($arr as $key => $value) 
      {  
      $refs[$key] = &$arr[$key];
      } 
    return $refs;}      

  public function bind($query, $input_param, $btypes)
    {   
    $a="test_var1";$b="test_var2";
    $output_arr=array($a,$b);
    $input_ref = $this->ref_arr($input_param);
    $output_ref = $this->ref_arr($output_arr);
    if($statement=mysqli_prepare(one::$db, $query)) 
      {
      array_unshift($input_ref, $statement, $btypes);
      call_user_func_array("mysqli_stmt_bind_param", $input_ref);
      mysqli_stmt_execute($statement);
      array_unshift($output_ref, $statement);
      call_user_func_array("mysqli_stmt_bind_result", $output_ref);
      mysqli_stmt_fetch($statement);
      var_dump($output_ref);  
      mysqli_stmt_close($statement);
      }
    }
  public function grab($type)
    {
    switch($type) 
      {
      case "validate_user":
        $query="SELECT email,pass FROM cr WHERE email=? AND pass=?";
        break;
      case "another_query_type":
        break;
      }
    return $query;
    }
  public function query($qtype, $arg_arr, $btypes)
    {
    return self::bind(self::grab_sql($qtype), $arg_arr, $btypes);
    }
  }

测试用例

<?php
  require_once 'p0.php'; 
  $db_ = new database();
  $db_->query('validate_user',array('joe@gmail.com','Password'), 'ss');
?>

结果

值结果似乎是(最后两个值)&NULL 和 &NULL.

The values result appears to be(last two values) &NULL and &NULL. 

array(3) { [0]=> object(mysqli_stmt)#2 (9) {  
    ["affected_rows"]=> int(-1) ["insert_id"]=> int(0) ["num_rows"]=> int(0) ["param_count"]=> int(2) ["field_count"]=> int(2) ["errno"]=> int(0) ["error"]=> string(0) "" ["sqlstate"]=> string(5) "00000" ["id"]=> int(1) } [1]=> &NULL [2]=> &NULL 
}

推荐答案

是的，在 Mysqli 中编写通用的 bind-this-array-into-a-query 是一个皇家 PITA.当我编写 Zend Framework 的 mysqli 适配器时，我最终让它工作了，但这需要大量的工作.欢迎您查看代码.我看到了一个主要区别，这是我如何做参考的:

Yes, writing a generic bind-this-array-into-a-query in Mysqli is a royal PITA. I eventually got it to work when I was coding Zend Framework's mysqli adapter, but it took a lot of work. You're welcome to take a look at the code. I see one chief difference, here's how I did the refs:

$stmtParams = array();
foreach ($params as $k => &$value) {
    $stmtParams[$k] = &$value;
}
call_user_func_array(
    array($this->_stmt, 'bind_param'), // mysqli OO callback
    $stmtParams
);

这与您的略有不同.我想知道在您的代码中，引用运算符 & 是否比数组索引 [] 运算符绑定得更紧密.

This is slightly different than yours. I wonder if in your code the ref operator & binds more tightly than the array index [] operator.

注意我还必须在 foreach 和赋值中使用 ref 运算符.我一直不明白为什么，但这是唯一可行的方法.PHP 引用非常神秘且难以理解.

Note I also had to use the ref operator both in the foreach and in the assignment. I never quite understood why, but this was the only way it would work. PHP refs are pretty mysterious and hard to understand.

如果您坚持使用启用了 Mysqli 但未启用 PDO 的环境，这可能不是一个可行的建议，但您确实应该考虑改用 PDO.PDO 会为您处理很多工作；您可以简单地将一组值传递给 PDOStatement::execute() 用于带参数的准备好的查询.对我来说，将 PDO 用于这种特殊用途比使用 mysqli 要容易得多.

This may not be a viable suggestion if you're stuck with an environment that has Mysqli enabled but not PDO, but you should really consider using PDO instead. PDO takes care of a lot of that work for you; you can simply pass an array of values to PDOStatement::execute() for a prepared query with parameters. For me, it was far easier to use PDO for this particular use than mysqli.

$pdoStmt->execute( array('joe@gmail.com','Password') );  // it's that easy

PS:我希望你 不是以明文形式存储密码.

相关文章