0

I'm trying to use the ? instead of binding a value because you can't mix ? and :binds in the same query and I need to use ? for the array I'm imploding within the query. 

//Set values
$limit = 5;
$offset = 2;
$location = array("Cornwall", "Devon", "Dorset", "Norfolk & Suffolk", "Somerset", "Wiltshire");

//Sort the array by adding ?
$qMarks = str_repeat('?,', count($location) - 1) . '?';

//Query using ? because you can't mix them in a query
$location[] = (int)$limit;
$location[] = (int)$offset;
$sql = $conn->prepare("SELECT * FROM adverts WHERE location IN ($qMarks) AND status = 2 LIMIT ? OFFSET ?");
$sql->execute($location);
$adverts=$sql->fetchAll();

This should insert each string in the array into the query and on the end add the $limit & $offset variables.

When I dump the query it seems to be displaying as I want it has each of the array items and then has the last two for the limit and offset but doesn't run;

array(8) { [0]=> string(8) "Cornwall" [1]=> string(5) "Devon" [2]=> string(6) "Dorset" [3]=> string(17) "Norfolk & Suffolk" [4]=> string(8) "Somerset" [5]=> string(9) "Wiltshire" [6]=> int(5) [7]=> int(2) }

This is the error I'm getting with the code;

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''5' OFFSET '0'' at line 1' in /advert.php:30
Stack trace:
#0 advert.php(30): PDOStatement->execute(Array)
#1 {main}
  thrown in advert.php on line 30
user2598957
  • 263
  • 6
  • 12
  • Not quite that's using binds and a manual array, my array value will change and I'm using the "?" instead of binds because of the way I implode the array. – user2598957 Jan 14 '15 at 19:05
  • Well, you have to use binds. Parameters passed as execute list[] will always be strings. You can try disabling emulated prepares and `LIMIT 1*?` casting (not sure if recent Mysqld support that though). – mario Jan 14 '15 at 19:07
  • just embed the limit/offset directly into the query. It's not coming from "external" data, so unless you're feeling particularly suicidal, you're at no risk of injection attacks. `->prepare("SELECT ... LIMIT $limit OFFSET $offset");` – Marc B Jan 14 '15 at 19:12

0 Answers0