mySQL bind_param with IN(?) [duplicate]

Question

Using bind_param on all my queries, I now want to use an IN(?) where the number of elements in the list can vary.

The SQLout function I'm using here basically does a $sql_db->prepare, ->bind_param, ->execute(), ->store_result(), ->bind_result

// the code below does not work as the query only matches on element 'a':
$locations = ('a','b','c','d','e');

SQLout ("SELECT Name FROM Users WHERE Locations IN (?)",
    array('s', $locations), array(&$usrName));


// the code below does work as a brute-force method,
// but is not a viable solution as I can't anticipate the number of elements in $locations going forward:

SQLout ("SELECT Name FROM Users WHERE Locations IN (?,?,?,?,?)",
    array('sssss', $locations[0],$locations[1],$locations[2],$locations[3],$locations[4]), array(&$usrName));

Has anyone come up with a more elegant solution to this?

Answer 1

This is one place placeholders fall on their faces. Minus the auto-escaping, they're almost literally just a string replacement operation internally, meaning that if you have WHERE Locations IN (?), and pass in 1,2,3,4, you'll get the equivalent of

WHERE Locations IN ('1,2,3,4')  // note, it's a string, not individual comma-separated integers

logically equivalent to

WHERE Locations = '1,2,3,4' // again, just a string

instead of the intended

WHERE Locations = 1 OR Locations = 2 OR Locations = 3 OR Locations = 4

The only practical solution is to build your own list of comma-separated placeholders (?), e.g:

$placeholders = implode(',', array_fill(0, count($values), '?'));
$sql = "SELECT Name FROM Users WHERE Locations IN ($placeholders)";

and then bind your parameters are usual.

Answer 2

As Hazmat said, you need to build up the parameters and then pass them by calling call_user_func_array on the prepared statement, but slightly closer to working code than his example :)

//In the calling code
$queryString = "SELECT Name FROM Users WHERE Locations IN (";
$queryString .= getWhereIn($locations);
$queryString .= " )";

$parametersArray = array();

foreach($locations as $location){
    $parameter = array();
    $parameter[0] = 's'; //It's a string
    $parameter[1] = $location;

    $parametersArray[] = $parameter;
}



//This is a function in a class that wraps around the class mysqli_statement
function    bindParameterArray($parameterArray){

    $typesString = '';
    $parameterValuesArray = array();

    foreach($parameterArray as $parameterAndType){
        $typesString .= $parameterAndType[0];
        $parameterValuesArray[] = $parameterAndType[1];
    }

    $finalParamArray = array($typesString);
    $finalParamArray = array_merge($finalParamArray, $parametersArray);
    call_user_func_array(array($this->statement, "bind_param"), $finalParamArray);
}

function getWhereIn($inArray){
    $string = "";
    $separator = "";
    for($x=0 ; $x<count($inArray) ; $x++){
        $string .= $separator."?";
        $separator = ", ";
    }
    return  $string;
}

Answer 3

You can "build" in IN clause before you prepare/bind it.

$sql = 'SELECT Name FROM Users WHERE Locations IN (' . implode(array_fill(0, count($locations), '?')) . ')';

Then you can use call_user_func_array to bind the parameters, without ever knowing how many there are.

// Parameters for SQLOut
$params = array(
    # The SQL Query
    $sql,
    # The params for bind_param
    array(str_repeat('s', count($locations))),
    # The params for bind_result
    array(&$usrName)
);

// Add the locations into the parameter list
foreach($locations as &$loc){
    // not sure if this is needed, but bind_param
    // expects its parameters to be references
    $params[1][] = &$loc;
}

// Call your function
call_user_func_array('SQLout', $params);

_{Note: This is untested}