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
– Julie Pelletier, Commented May 5, 2016 at 8: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
– BeniaminoBaggins, Commented May 5, 2016 at 8:49

Chris · Accepted Answer · 2016-05-05 08:33:16Z

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 5, 2016 at 8:33

answered May 5, 2016 at 8:21

Chris

5,9562 gold badges23 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

BeniaminoBaggins Over a year ago

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?

Chris Over a year ago

@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?

BeniaminoBaggins Over a year ago

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.

Community · Accepted Answer · 2017-05-23 10:28:11Z

0

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.

edited May 23, 2017 at 10:28

CommunityBot

11 silver badge

answered May 5, 2016 at 9:29

BeniaminoBaggins

12.6k46 gold badges176 silver badges338 bronze badges

Collectives™ on Stack Overflow

Getting call_user_func_array() to work with prepared sql statements

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related