Asked  2 Years ago    Answers:  5   Viewed   80 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

1

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)");
Sunday, October 23, 2022
 
kentzo
 
5

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
Sunday, September 18, 2022
 
chadoh
 
4

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, October 21, 2022
2

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

Tuesday, October 4, 2022
 
rajagp
 
4

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.

Saturday, December 10, 2022
 
gbravor
 
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 

Browse Other Code Languages