如何在 PHP 中动态绑定 mysqli bind_param 参数? [英] How to bind mysqli bind_param arguments dynamically in PHP?

问题描述

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

I have been learning to use prepared and bound statements for my sql queries, and I have come out with this so far, it works ok but it is not dynamic at all when comes to multiple parameters or when there no parameter needed,

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();
    }

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

This is how I call the object classes,

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

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

Sometimes I don't need to pass in any parameters,

$sql = "
SELECT *
FROM root_contacts_cfm
";

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

有时我只需要一个参数，

Sometimes I need only one parameters,

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

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

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

Sometimes I need only more than one parameters,

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

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

So, I believe that this line is not dynamic enough for the dynamic tasks above,

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

为了动态构建 bind_param，我在网上的其他帖子上找到了这个.

To build a bind_param dynamically, I have found this on other posts online.

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

我尝试修改 php.net 但我一无所获，

And I tried to modify some code from php.net but I am getting nowhere,

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

     }

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

Why? Any ideas how I can make it work?

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

Or maybe there are better solutions?

推荐答案

找到了 mysqli 的答案:

found the answer for 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！

mysqli is so lame when comes to this. I think I should be migrating to PDO!

这篇关于如何在 PHP 中动态绑定 mysqli bind_param 参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！