PDO mixing unnamed and named parameters

Question

I am trying to use this for a basic pagination search:

$construct = '? AND ? AND..'; $query = $database->prepare('SELECT * FROM something WHERE something LIKE ' . $construct . ' LIMIT :offset, :results'); 

The only reason I mix them is that unnamed parameters cannot have int values ​​due to a PHP error, obviously: https://bugs.php.net/bug.php?id=44639

However, if I do not mix them, how can I search for a variable number of terms using bindings?

Update

After messing with it, I decided to more or less use named parameters and some loops:

  // build prepared statement $construct = ''; for ($x = 0; $x <= $searchArrayCount; $x++) { $construct .= ($x < $searchArrayCount) ? ":var$x OR name LIKE " : ":var$x LIMIT :start, :perPage"; } $query = $database->prepare('SELECT something FROM something WHERE name LIKE ' . $construct); // bind parameters for ($x = 0; $x <= $searchArrayCount; $x++) { $searchArray[$x] = "%$searchArray[$x]%"; $query->bindParam(":var$x", $searchArray[$x]); } $query->bindParam(':start', $searchArrayCount, PDO::PARAM_INT); $query->bindParam(':perPage', $perPage, PDO::PARAM_INT); 

If there is a better way around this, I would like to be informed.

Answer 1

Moderate parameters can have int values. Just define an explicit type in the bind function.

There may be a problem with the data type of the variables. It's good to use the intval() function before.

Your solution in an unnamed data type might look like this:

 $counter = 0; //build prepared statement $query = $database->prepare('SELECT something FROM something WHERE 0 OR '. implode(' OR ', array_fill(0 , $searchArrayCount, 'name LIKE ?')). ' LIMIT ?, ?'); // bind parameters foreach($searchArray as $value) { $counter++; $query->bindValue($counter, ('%'.$value.'%'), PDO::PARAM_STR); } $query->bindValue(($counter+1), ($page*$perPage), PDO::PARAM_INT); $query->bindParam(($counter+2), $perPage, PDO::PARAM_INT); 

Note. I used rather bindValue() to bindParam() . And beware of the first LIMIT parameter. If the counter is here, then the selection will begin at the end of the data and no rows will be returned.

Answer 2

Faced with a similar problem, in the end I just skipped the binding options in the LIMIT clause:

 sprintf('LIMIT %d, %d', $offset, $size); 

Another workaround (if your system supports it) is to switch to binding your own parameters. Apparently, this is an emulation layer that exhibits this behavior:

 $database->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);