Dynamically build a prepared statement with call_user_func_array()
<p>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 <a href="http://www.pontikis.net/blog/dynamically-bind_param-array-mysqli" rel="nofollow noreferrer">this tutorial</a> and apply it to my code. Here is the code:</p>

<pre><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 =&gt; $value) {
        if ($key === "country") {
            continue;
        }
        $query .= " and s.$key = ?";
        $a_params[] = $value;
        $a_param_type[] = "s";
    }
    /* Prepare statement */
    $stmt = $conn-&gt;prepare($query);
    if($stmt === false) {
        trigger_error('Wrong SQL: ' . $sql . ' Error: ' . $conn-&gt;errno . ' ' . $conn-&gt;error, E_USER_ERROR);
    }

    $a_params[] =  $a_param_type;

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

    /* Execute statement */
    $stmt-&gt;execute();   
    $meta = $stmt-&gt;result_metadata();
</code></pre>

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

<pre><code>"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 = ?"
</code></pre>

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

<p>In:</p>

<pre><code>call_user_func_array(array($stmt, 'bind_param'), $a_params);
</code></pre>

<p>the value of <code>$a_params</code> is:</p>

<pre><code>0:"New Zealand"
1:"Grey Lynn"
2:"Auckland"
3:"Auckland"
4:array(4)
0:"s"
1:"s"
2:"s"
3:"s"
</code></pre>

<p>In:</p>

<pre><code>$meta = $stmt-&gt;result_metadata();
</code></pre>

<p>the value of $meta becomes:</p>

<pre><code>current_field:0
field_count:13
lengths:null
num_rows:0
type:1
</code></pre>

<p>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?</p>

<p><strong>EDIT:</strong> I saw that <a href="https://stackoverflow.com/a/24713481/3935156">this answer</a> says to put the "ssss" at the start of the <code>$params</code> so I did that and got this error in the <code>$stmt</code> object:</p>

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