mySQL bind_param with IN(?)

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?

PDO answers has nothing to do with mysqli problems. PDO is a heaven compared to mysqli in terms of handling variable number of prepared statements — Your Common Sense, Feb 19 '13 at 15:22

score 10 · Accepted Answer · edited Oct 13 '19 at 13:27

10

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.

edited Oct 13 '19 at 13:27

mickmackusa

43,625
12
83
136

answered Feb 19 '13 at 15:07

Marc B

356,200
43
426
500

`$stmt->execute($values)`. easy as can be. – Marc B Feb 19 '13 at 15:26
2

Unfortunately, binding prepared statements in mysqli is not as easy as in PDO. Mysqli doesn't have `$stmt->execute($values)` feature. Nor it have bindValue method (so you can't bind in a loop using iterated variable) which makes such a simple task a circus and a nightmare at once. This is why this answer is way incomplete – Your Common Sense Feb 19 '13 at 16:09
Works best for my case. I'll have to run some speed tests on the use of long IN elements. – user2033684 Feb 19 '13 at 16:32
3

@MarcB Where you have used implode - why no comma separator? Are you deliberately forming a string like: "????????????" as opposed to "?,?,?,?,?,?,?,?,?,?" – s-low Feb 16 '17 at 12:53

Danack · Answer 2 · 2013-02-19T15:44:26.273

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

gen_Eric · Answer 3 · 2013-02-19T15:19:38.817

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}

score -1 · Answer 4 · answered Feb 19 '13 at 15:05

-1

IN is usually slow and not prepared statement friendly. The better solution is to build a table of the items that would be in the IN and use a JOIN to get the same effect.

answered Feb 19 '13 at 15:05

Peter Wooster

6,009
2
27
39

@Your Then provide an example of how to get variable numbers of values in the IN. – Peter Wooster Feb 19 '13 at 15:07
@your please read http://stackoverflow.com/questions/4771183/sql-fixed-value-in-vs-inner-join-performance The problem with IN is that you can't index the list like you can in a table and as the list gets longer the performance degrades quickly. Of course it's fast when the list is short. – Peter Wooster Feb 19 '13 at 15:15

score -2 · Answer 5 · edited Oct 13 '19 at 13:32

-2

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

Sure. I have.

Mysqli is practically unusable with prepared statements, especially with such complex cases.
So, it's better to get rid of prepared statements and implement your own placeholders with support of all real life cases that a developer can meet.

safeMysql (a creation of mine) has a solution you're looking for (and also solutions for a dozen other headaches as well).

In your particular case, it would be as easy as this single line of code

// the code works alright:
$locations = array('a', 'b', 'c', 'd', 'e');
$usrName = $db->getCol("SELECT Name FROM Users WHERE Locations IN (?a)", $locations);

Unlike ugly codes you can get while playing with some PHP and API functions (and still get disturbing warnings depends on the PHP version you're using at the moment), this code is neat and readable. This is an important matter. You can tell what this code does even after a year has passed.

edited Oct 13 '19 at 13:32

mickmackusa

43,625
12
83
136

answered Feb 19 '13 at 15:14

Your Common Sense

156,878
40
214
345

It's still going to be slow if the list is long http://stackoverflow.com/questions/4771183/sql-fixed-value-in-vs-inner-join-performance – Peter Wooster Feb 19 '13 at 15:21
I've no idea where your repuation has come from on this website when you started this answer with "Mysqli is practically unusable with prepared statements". – A Friend Nov 17 '19 at 03:54
@AFriend oh that's simple :). Just get back into 2013, try to use mysqli and you'd clearly see what I meant. Cheers. – Your Common Sense Nov 17 '19 at 05:31

mySQL bind_param with IN(?)

5 Answers5

Linked

Related