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

Z时代
2024-01-10
分类：问答

我一直在学习对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);
     }

为什么？有什么想法可以使它起作用吗？

也许有更好的解决方案？

回答：

找到了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')));