Getting call_user_func_array() to work with prepared sql statements

Question

I have to build up a sql statement and the params from a $place object that has a variable number of properties. When I use prepared sql statements by building the sql statement and params the long and bad practice way it works (returns all the rows from the database that it should):

<?

function buildSQLWhereClause($query, $conn, $place) {
    if ($place['suburb']){
        if($place['city'] && $place['province'] && $place['country']) {
            $query .= "s.country = ? and 
                    s.province = ? and
                    s.city = ? and 
                    s.suburb = ?";

            // prepare and bind
            $stmt = $conn->prepare($query);

            $stmt->bind_param("ssss", $place['country'], $place['province'], $place['city'], $place['suburb']);
        } else if ($place['province'] && $place['country']) {
            $query .= "s.country = ? and 
                    s.province = ? and
                    s.suburb = ?";

            // prepare and bind
            $stmt = $conn->prepare($query);

            $stmt->bind_param("sss", $place['country'], $place['province'], $place['suburb']);
        } else if ($place['city'] && $place['province']) {
            $query .= "s.province = ? and
                    s.city = ? and 
                    s.suburb = ?";

            // prepare and bind
            $stmt = $conn->prepare($query);

            $stmt->bind_param("sss", $place['province'], $place['city'], $place['suburb']);
        } else if ($place['city'] && $place['country']) {
            $query .= "s.country = ? and
                    s.city = ? and 
                    s.suburb = ?";

            // prepare and bind
            $stmt = $conn->prepare($query);

            $stmt->bind_param("sss", $place['country'], $place['city'], $place['suburb']);
        } else if ($place['city']) {
            $query .= "s.city = ? and 
                    s.suburb = ?";

            // prepare and bind
            $stmt = $conn->prepare($query);

            $stmt->bind_param("ss", $place['city'], $place['suburb']);
        } else if ($place['province']) {
            $query .= "s.province = ? and 
                    s.suburb = ?";

            // prepare and bind
            $stmt = $conn->prepare($query);

            $stmt->bind_param("ss", $place['province'], $place['suburb']);
        } else if ($place['country']) {
            $query .= "s.country = ? and 
                    s.suburb = ?";

            // prepare and bind
            $stmt = $conn->prepare($query);

            $stmt->bind_param("ss", $place['country'], $place['suburb']);
        } else {
            $query .= "s.suburb = ?";

            // prepare and bind
            $stmt = $conn->prepare($query);

            $stmt->bind_param("s", $place['suburb']);
        }
//////////////////////////// NO SUBURB ///////////////////////////////////////////////////        
    } else if ($place['city']) {
        if ($place['province'] && $place['country']) {
            $query .= "s.country = ? and 
                    s.province = ? and
                    s.city = ?";

            // prepare and bind
            $stmt = $conn->prepare($query);

            $stmt->bind_param("sss", $place['country'], $place['province'], $place['city']);
        } else if ($place['province']) {
            $query .= "s.province = ? and
                    s.city = ?";

            // prepare and bind
            $stmt = $conn->prepare($query);

            $stmt->bind_param("ss", $place['province'], $place['city']); 
        } else if ($place['country']) {
            $query .= "s.country = ? and
                    s.city = ?";

            // prepare and bind
            $stmt = $conn->prepare($query);

            $stmt->bind_param("ss", $place['country'], $place['city']); 
        } else {
            $query .= "s.city = ?";

            // prepare and bind
            $stmt = $conn->prepare($query);

            $stmt->bind_param("s", $place['city']); 
        }
//////////////////////// NO SUBURB OR CITY ////////////////////////////////////////////////////////
    } else if ($place['province']) {
        if ($place['country']) {
            $query .= "s.country = ? and
                    s.province = ?";

            // prepare and bind
            $stmt = $conn->prepare($query);

            $stmt->bind_param("ss", $place['country'], $place['province']); 
        } else {
            $query .= "s.province = ?";

            // prepare and bind
            $stmt = $conn->prepare($query);

            $stmt->bind_param("s", $place['province']); 
        }
//////////////////////////////// NO SUBURB, CITY, OR PROVINCE ///////////////////////////////        
    } else if ($place['country']) {
        $query .= "s.country = ?";

        // prepare and bind
        $stmt = $conn->prepare($query);

        $stmt->bind_param("s", $place['country']); 
    }

    return $stmt;
}

function queryDbForProducts($conn, $place)
{   

    $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 ";

    $stmt = buildSQLWhereClause($query, $conn, $place);

    $stmt->execute();
    $meta = $stmt->result_metadata();

    while ($field = $meta->fetch_field()) {
        $parameters[] =& $row[$field->name];
    }

When I use sql prepared statements by building up the sql statement and params the much better way, it doesn't work:

<?

function buildSQLWhereClause($place) {
    $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 ";
    $queryParams = [];
    $queryParamTypes = "";
    $i = 0;
    $len = count($place);
    foreach ($place as $key => $value) {
        if ($i == $len - 1) {
            $query .= "$key = ?";
            $queryParams[] = $value;
            $queryParamTypes .= "s";
        } else {
            $query .= "$key = ? AND ";
            $queryParams[] = $value;
            $queryParamTypes .= "s";
        }
        $i++;
    }

    return array(
            "query" => $query,
            "queryParams" => $queryParams,
            "queryParamTypes" => $queryParamTypes
        );
}

function queryDbForProducts($conn, $place)
{   
    $queryObject = buildSQLWhereClause($place);
    $query = $queryObject['query'];
    $queryParams = $queryObject['queryParams'];
    $queryParamTypes = $queryObject['queryParamTypes'];


    // prepare and bind
    $stmt = $conn->prepare($query);
    $stmt->bind_param($queryParamTypes, $queryParams); 
    $stmt->execute();
    $meta = $stmt->result_metadata();

Hovering over the $stmt in the debugger shows:

affected_rows:-1
insert_id:0
num_rows:0
param_count:4
field_count:13
errno:2031
error:"No data supplied for parameters in prepared statement"
error_list:array(1)
sqlstate:"HY000"
id:1

No data supplied? Hovering over the $queryParams parameter in the debugger shows:

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

So I did provide the query parameters to the $stmt->bind_param() function. Did I provide them in the wrong format?

Hovering over $QueryParamTypes shows:

"ssss"

Hovering over $query shows:

"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 suburb = ? AND city = ? AND province = ? AND country = ?"

How come it works when done with the code at the top of the question and it doesn't work when done with the code without all the is statements?

At the end, you show what the malfunctioning query looks like, can you also show the functioning query to compare? — Julie Pelletier, May 05 '16 at 08:25
@JuliePelletier `"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.province = ? and s.city = ? and s.suburb = ?"` — BeniaminoBaggins, May 05 '16 at 08:49

Chris · Answer 1 · 2016-05-05T08:33:16.200

2

bind_param does not take an array as an argument, it takes varargs. You will need to use call_user_func_array if you want to call it with a dynamic number of arguments.

i.e.

$params = array_unshift($queryParams, $queryParamTypes);
call_user_func_array(array($stmt, "bind_param"), $params);

edited May 05 '16 at 08:33

answered May 05 '16 at 08:21

Chris

5,571
2
20
32

I have gone down that route and it didn't work for unknown reasons, but we exhausted all options trying to do that. Could I break up the $params array into multiple strings instead? – BeniaminoBaggins May 05 '16 at 08:25
@Beniamino_Baggins You could do that if the number is always constant, but from your code it doesn't look like it is. What error did you get when you attempted this method? – Chris May 05 '16 at 08:29
I realised that I am not adding `s.country` etc into the sql query, only `country` when specifying the cloumn which was a major issue. I have now rectified that issue, but it is still not working. – BeniaminoBaggins May 05 '16 at 08:55

score 0 · Answer 2 · edited May 23 '17 at 10:28

The params passed to call_user_func_array, parameter 2, need to be referenced.

This is the working solution:

    function makeValuesReferenced($arr){
    $refs = array();
    foreach($arr as $key => $value)
        $refs[$key] = &$arr[$key];
    return $refs;
    }

$stmt = $conn->prepare($query);
//$stmt->bind_param($queryParamTypes, $queryParams); 
call_user_func_array(array($stmt, 'bind_param'), makeValuesReferenced($queryParams));
$stmt->execute();

As per this answer

Not knowing about the reference thing mucked me around for a long time. Hope this helps some people.

Getting call_user_func_array() to work with prepared sql statements

2 Answers2