如何在 PHP 中动态绑定 mysqli bind_param 参数？

Question

我一直在学习为我的 sql 查询使用准备好的和绑定的语句，到目前为止我已经得出了这个，它工作正常但是当涉及多个参数或不需要参数时它根本不是动态的，

 public function get_result($sql,$parameter)
{
    # create a prepared statement
    $stmt = $this->mysqli->prepare($sql);

    # bind parameters for markers
    # but this is not dynamic enough...
    $stmt->bind_param("s", $parameter);

    # execute query
    $stmt->execute();

    # these lines of code below return one dimentional array, similar to mysqli::fetch_assoc()
    $meta = $stmt->result_metadata();

    while ($field = $meta->fetch_field()) {
        $var = $field->name;
        $$var = null;
        $parameters[$field->name] = &$$var;
    }

    call_user_func_array(array($stmt, 'bind_result'), $parameters);

    while($stmt->fetch())
    {
        return $parameters;
        //print_r($parameters);
    }


    # close statement
    $stmt->close();
}

这就是我调用对象类的方式，

 $mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$output = new search($mysqli);

有时候不需要传入任何参数，

 $sql = "
SELECT *
FROM root_contacts_cfm
";

print_r($output->get_result($sql));

有时我只需要一个参数，

 $sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
ORDER BY cnt_id DESC
";

print_r($output->get_result($sql,'1'));

有时我只需要一个以上的参数，

 $sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
AND root_contacts_cfm.cnt_firstname = ?
ORDER BY cnt_id DESC
";

print_r($output->get_result($sql,'1','Tk'));

所以，我认为这条线对于上面的动态任务来说不够动态，

 $stmt->bind_param("s", $parameter);

要动态构建 bind_param，我在其他在线帖子中找到了这个。

 call_user_func_array(array(&$stmt, 'bind_params'), $array_of_params);

我试图从 php.net 修改一些代码，但我一无所获，

 if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
{
    $refs = array();
    foreach($arr as $key => $value)
        $array_of_param[$key] = &$arr[$key];
    call_user_func_array(array(&$stmt, 'bind_params'), $array_of_params);
}

为什么？有什么想法可以让它发挥作用吗？

或者也许有更好的解决方案？

原文由 Run 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

找到了 mysqli 的答案：

 public function get_result($sql,$types = null,$params = null)
    {
        # create a prepared statement
        $stmt = $this->mysqli->prepare($sql);

        # bind parameters for markers
        # but this is not dynamic enough...
        //$stmt->bind_param("s", $parameter);

        if($types&&$params)
        {
            $bind_names[] = $types;
            for ($i=0; $i<count($params);$i++)
            {
                $bind_name = 'bind' . $i;
                $$bind_name = $params[$i];
                $bind_names[] = &$$bind_name;
            }
            $return = call_user_func_array(array($stmt,'bind_param'),$bind_names);
        }

        # execute query
        $stmt->execute();

        # these lines of code below return one dimentional array, similar to mysqli::fetch_assoc()
        $meta = $stmt->result_metadata();

        while ($field = $meta->fetch_field()) {
            $var = $field->name;
            $$var = null;
            $parameters[$field->name] = &$$var;
        }

        call_user_func_array(array($stmt, 'bind_result'), $parameters);

        while($stmt->fetch())
        {
            return $parameters;
            //print_r($parameters);
        }

        # the commented lines below will return values but not arrays
        # bind result variables
        //$stmt->bind_result($id);

        # fetch value
        //$stmt->fetch();

        # return the value
        //return $id;

        # close statement
        $stmt->close();
    }

然后：

 $mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$output = new search($mysqli);

$sql = "
SELECT *
FROM root_contacts_cfm
ORDER BY cnt_id DESC
";
print_r($output->get_result($sql));

$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
ORDER BY cnt_id DESC
";

print_r($output->get_result($sql,'s',array('1')));

$sql = "
SELECT *
FROM root_contacts_cfm
WHERE root_contacts_cfm.cnt_id = ?
AND root_contacts_cfm.cnt_firstname = ?
ORDER BY cnt_id DESC
";

print_r($output->get_result($sql, 'ss',array('1','Tk')));

mysqli 在这方面太蹩脚了。我想我应该迁移到 PDO！

原文由 Run 发布，翻译遵循 CC BY-SA 2.5 许可协议

Answer 2

使用 PHP 5.6，您可以在 解包运算符（ ...$var ）的帮助下轻松完成此操作，并使用 get_result() insted of bind_result()

 public function get_custom_result($sql,$types = null,$params = null) {
    $stmt = $this->mysqli->prepare($sql);
    $stmt->bind_param($types, ...$params);

    if(!$stmt->execute()) return false;
    return $stmt->get_result();

}

例子：

 $mysqli = new database(DB_HOST,DB_USER,DB_PASS,DB_NAME);
$output = new search($mysqli);

$sql = "SELECT * FROM root_contacts_cfm WHERE root_contacts_cfm.cnt_id = ?
        AND root_contacts_cfm.cnt_firstname = ?
        ORDER BY cnt_id DESC";

$res = $output->get_custom_result($sql, 'ss',array('1','Tk'));
while($row = res->fetch_assoc()){
   echo $row['fieldName'] .'<br>';
}

原文由 rray 发布，翻译遵循 CC BY-SA 4.0 许可协议