使用call_user_func_array()动态构建准备好的语句 [英] Dynamically build a prepared statement with call_user_func_array()

问题描述

我需要根据用户输入动态构建SQL语句和参数. sql语句的长度和参数的数量根据用户输入而变化.我正在尝试使用本教程并将其应用于我的代码.这是代码:

I need to dynamically build up the SQL statement and the parameters based on user input. The length of the sql statement and the number of parameters changes based on user input. I am trying to use this tutorial and apply it to my code. Here is the code:

$query = "SELECT p.*, s.* 
        FROM product p 
        INNER JOIN product_shop ps 
        ON ps.p_id = p.p_id 
        INNER JOIN shop s 
        ON s.s_id = ps.s_id 
        WHERE s.country = ?";
        $a_params[] = $place['country'];
        $a_param_type[] = "s";
    // prepare and bind

    $param_type = '';

    foreach ($place as $key => $value) {
        if ($key === "country") {
            continue;
        }
        $query .= " and s.$key = ?";
        $a_params[] = $value;
        $a_param_type[] = "s";
    }
    /* Prepare statement */
    $stmt = $conn->prepare($query);
    if($stmt === false) {
        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn->errno . ' ' . $conn->error, E_USER_ERROR);
    }

    $a_params[] =  $a_param_type;

    /* use call_user_func_array, as $stmt->bind_param('s', $param); does not accept params array */
    call_user_func_array(array($stmt, 'bind_param'), $a_params);

    /* Execute statement */
    $stmt->execute();   
    $meta = $stmt->result_metadata();

我知道$place['country']将始终被填充. sql语句正确.是:

I know $place['country'] will always be populated. The sql statement is correct. It is:

"SELECT p.*, s.* \n        FROM product p \n        INNER JOIN product_shop ps \n        ON ps.p_id = p.p_id \n        INNER JOIN shop s \n        ON s.s_id = ps.s_id \n        WHERE s.country = ? and s.suburb = ? and s.city = ? and s.province = ?"

不要管"\ n"字符，它们对sql语句没有影响.

Don't mind the "\n" chars, they have no effect on the sql statement.

在:

call_user_func_array(array($stmt, 'bind_param'), $a_params);

$a_params的值是:

0:"New Zealand"
1:"Grey Lynn"
2:"Auckland"
3:"Auckland"
4:array(4)
0:"s"
1:"s"
2:"s"
3:"s"

在:

$meta = $stmt->result_metadata();

$ meta的值变为:

the value of $meta becomes:

current_field:0
field_count:13
lengths:null
num_rows:0
type:1

意味着没有从数据库中选择任何行.我已经在数据库上手动执行了此sql，它返回了行.我的代码有什么问题，使它没有从数据库返回任何行?

Meaning that no rows were selected from the database. I have executed this sql on the database manually and it returns rows. What is wrong with my code, that makes it return no rows from the database?

编辑:我看到了此答案表示将"ssss"放在$params的开头，所以我这样做了，并在$stmt对象中得到了此错误:

I saw that this answer says to put the "ssss" at the start of the $params so I did that and got this error in the $stmt object:

errno:2031
error:"No data supplied for parameters in prepared statement"
error_list:array(1)
propertyNode.hasAttribute is not a function

推荐答案

我不知道您尝试了什么方法，但是我将尝试回答:

I don't understand what ways you've tried, but I will try to answer:

根据 bind_param manual :

bind_param的第一个参数是字符串，例如'ssss'.

first argument of bind_param is a string, like 'ssss'.

second和其他参数-是要插入查询中的值.

second and other arguments - are values to be inserted into a query.

因此，您的$a_params数组应为不

0:"New Zealand"
1:"Grey Lynn"
2:"Auckland"
3:"Auckland"
4:array(4)
0:"s"
1:"s"
2:"s"
3:"s"

但是:

0:"ssss"
1:"New Zealand"
2:"Grey Lynn"
3:"Auckland"
4:"Auckland"

看到了吗?所有值都是字符串.占位符的类型是第一个.

See? All values are strings. And placeholders' types are the first one.

还应考虑到$a_params中参数的顺序必须与bind_param中参数的顺序相同.这意味着$a_params喜欢

Also take into consideration that order of arguments in $a_params must be the same as order of parameters in bind_param. This means that, i.e., $a_params like

0:"New Zealand"
1:"Grey Lynn"
2:"Auckland"
3:"Auckland"
4:"ssss"

是错误的.因为$a_params的第一个元素将是bind_param的第一个参数，在这种情况下，它不是"ssss"字符串.

is wrong. Because first element of $a_params will be the first argument of bind_param and in this case it's not a "ssss" string.

因此，这意味着在用值填充$a_params后，应将占位符的字符串添加到$a_params的开头，例如array_unshift:

So, this means that after you filled $a_params with values, placeholders' string should be added to the beginning of $a_params, with array_unshift for example:

// make $a_param_type a string
$str_param_type = implode('', $a_param_type);

// add this string as a first element of array
array_unshift($a_params, $str_param_type);

// try to call
call_user_func_array(array($stmt, 'bind_param'), $a_params);

万一这行不通，您可以参考您提供的答案的一部分，其中$a_params通过引用传递给另一个数组$tmp，在您的情况下，您可以尝试以下操作:

In case this didn't work, you can refer to a part of answer you provided, where values of $a_params are passed by reference to another array $tmp, in your case you can try something like:

// make $a_param_type a string
$str_param_type = implode('', $a_param_type);

// add this string as a first element of array
array_unshift($a_params, $str_param_type);

$tmp = array();
foreach ($a_params as $key => $value) {
    // each value of tmp is a reference to `$a_params` values
    $tmp[$key] = &$a_params[$key];  
}

// try to call, note - with $tmp, not with $a_params
call_user_func_array(array($stmt, 'bind_param'), $tmp);

这篇关于使用call_user_func_array()动态构建准备好的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！