Geek asks and answers

MySQL bind_param with IN (?)

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

SQLout feature that I use here mainly performs $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?

+5
source share
5 answers

This is one place, placeholders get on their faces. With the exception of automatic escaping, they are almost literally just an internal string replacement operation, which means that if you have WHERE Locations IN (?)and you pass 1,2,3,4, you will get the equivalent

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

logically equivalent

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

- , (?), :

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

, .

+5

, , , call_user_func_array , , :)

//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 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;
}
+2

"" IN, / .

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

call_user_func_array , .

// 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);

.

+1

IN, , . - , IN, JOIN, .

-1

  - ?

. .

Mysqli , .
, .

SafeMysql ( ) , ( ).

In your particular case, it will be as simple as a 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 the ugly codes that you can get by playing with some of the PHP and API functions (and still receive alarming warnings, it depends on the version of PHP you are currently using), this code is neat and readable . This is an important issue. You can tell what this code does even after a year has passed.

-2
source

More articles:


All Articles