PHP Using an array within a mysql query with PDO binds

Question

I've had a look at other stack overflow answers but I didn't see anything similar to my problem other than the error code.

I'm trying to use an array in my query which works but I'm not sure why with the :start and :offset binds it fails to run.

$location = array("England", "America", "Australia"); 

$qMarks = str_repeat('?,', count($location) - 1) . '?';

//Query
$sql=$conn->prepare("SELECT * FROM adverts WHERE location IN ($qMarks) AND status = 2 LIMIT :limit OFFSET :start");
$sql->bindValue(':limit', (int) $limit, PDO::PARAM_INT);
$sql->bindValue(':start', (int) $start, PDO::PARAM_INT);
$sql->execute($location);
$adverts=$sql->fetchAll();

I'm not sure why with the limit and offset binds present it doesn't run, is it to do with that you can't combining positional placeholders.

Update 1 -

If I remove the array and the qMark and change the execute to sql->execute();

then the binds run

Eternal1 · Accepted Answer · 2015-01-14 14:06:10Z

1

You cannot mix ? bindings and :param bindings in one prepared statement.

So, you need something like this:

$location = array("England", "America", "Australia");

$qMarks = str_repeat('?,', count($location) - 1) . '?'; 

$location[] = (int)$limit;
$location[] = (int)$offset;
$sql = $conn->prepare("SELECT * FROM adverts WHERE location IN ($qMarks) AND status = 2 LIMIT ? OFFSET ?");
$sql->execute($location);

edited Jan 14, 2015 at 14:06

answered Jan 14, 2015 at 11:37

Eternal1

5,6253 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user2598957 Over a year ago

Isn't the '?' only supported by mysqli because you need to use bind_param("ss", value, value"); and bind_param is undefined in pdo?

Eternal1 Over a year ago

Nope, you can use both in PDO, but not in the same prepared statement.

user2598957 Over a year ago

Something along these lines? ("SELECT * FROM adverts WHERE location IN ($qMarks) AND status = 2 LIMIT (limits) VALUES (?)");

user2598957 Over a year ago

Ah I see what direction to go in with the code above I get an "Syntax error or access violation" starting at "OFFSET".

Collectives™ on Stack Overflow

PHP Using an array within a mysql query with PDO binds

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related