使用call_user_func_array（）动态构建预准备语句

Question

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

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

"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 = ? and s.suburb = ? and s.city = ? and s.province = ?"

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

In:

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

the value of $a_params is:

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

In:

$meta = $stmt->result_metadata();

the value of $meta becomes:

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

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?

EDIT: 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

Answer 1

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

according to bind_param manual:

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

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

So, your $a_params array should be not

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

But:

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

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

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"

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.

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

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