Asked  7 Months ago    Answers:  5   Viewed   29 times
$statement = $db->prepare('SELECT blah FROM blah_table WHERE blahID IN (:a, :b, :c)');

What if the number of parameters is unknown until run-time? The only thing I can think of doing is a hacky kind of building of the sql string to make as many parameter placeholders as I need.

 Answers

44

You can build the "IN (...)" string dynamically:

$in_string = '(';
foreach ( $array_of_parameters as $parameter ) {
    $in_string .= ':' . chr($i + 97) . ','; // Get the ASCII character
}
$in_string = substr($in_string, 0, -1) . ')';

$statement = $db->prepare("SELECT blah FROM blah_table WHERE blahID IN ($in_string)");
Wednesday, March 31, 2021
 
nomie
answered 7 Months ago
42

Had already downloaded the driver and it didn't work. Found a new site for the driver and this one works.

https://github.com/Microsoft/msphpsql/releases

php.ini line added:

extension=php_pdo_sqlsrv_7_nts.dll
Wednesday, March 31, 2021
 
njai
answered 7 Months ago
73

showdev's comment is correct that the PDO DSN does not allow host:port syntax.

If your CMS is defining DB_HOST outside of your control, you can't use that constant directly. But you can pull information out of it.

$host_port = preg_replace('/:(d+)/', ';port=${1}', DB_HOST);
$db = new PDO("mysql:host={$host_port};dbname=".DB_NAME.";charset=utf8", 
    DB_USER, DB_PW, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
Friday, May 28, 2021
 
Chvanikoff
answered 5 Months ago
27

You'll have to manage the list of arguments (type and value) as you add the conditions. This means you need to use call_user_func_array to pass the list of values to bind_param.

Basically, in each of your if statements, not only add the condition, but also add the parameter type (e.g. $types .= 'i') and the parameter (e.g. $args[]=$arg).

You'll find a good example on how to do this in this comment of the bind_param documentation: http://www.php.net/manual/en/mysqli-stmt.bind-param.php#109256

Saturday, May 29, 2021
 
Pradip
answered 5 Months ago
80

A bit sloppy, but gets the job done.

function refValues($arr){
    if (strnatcmp(phpversion(),'5.3') >= 0) //Reference is required for PHP 5.3+
    {
        $refs = array();
        foreach($arr as $key => $value)
            $refs[$key] = &$arr[$key];
        return $refs;
    }
    return $arr;
}

$params = array();

$query = "SELECT * FROM table WHERE status = 1";

// Iterate over your paramters from $_GET
foreach ($_GET as $k => $v) 
{ 
  if(!empty($v)
  {
    $query .= " AND $k = ?";
    $params[$k] = helper::sanitize($v);
  }
}
// After you get through all your params...

$stmt = $mysqli->prepare($query);

// Bind em.
call_user_func_array(array($stmt, 'bind_param'), refValues($params));

That should do it, though I've never bound with mysqli before. Let me know how that works.

Friday, August 27, 2021
 
lechup
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share