0

I'm trying to use an array in my mysql query, when I use my variable holding the array it returns no results, as well as no errors. 

$location = array("Cornwall", "Devon", "Dorset", "Norfolk & Suffolk", "Somerset", "Wiltshire");

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

This returns no errors no results but the query does work if I hard code the values in such as:

$sql=$conn->prepare("SELECT * FROM adverts WHERE location IN ('Cornwall', 'Devon') ");
Sam
  • 1,546
  • 4
  • 14
  • 22
  • You will have to bind the locations separately – PeeHaa Jan 12 '15 at 11:54
  • It requires some machinery to build up the string sent to `prepare()`, but eventually, each value in `$location` needs its own `?` placeholder. The linked answer has good solutions using `array_fill()`. – Michael Berkowski Jan 12 '15 at 11:55
  • Note that you cannot _combine_ positional placeholders `?` with named ones like `:start, :limit`, so you will need to change those to `?` instead. – Michael Berkowski Jan 12 '15 at 11:56
  • [This one in particular](http://stackoverflow.com/a/10722827/541091) is concise and should work for you - you'll just need to add the other 2 params for start & limit onto the array sent to `execute()`. – Michael Berkowski Jan 12 '15 at 11:59
  • Do you have any example of using "?" in the query i cant get it to work with the :start & :offset values in the query – Sam Jan 12 '15 at 14:25

0 Answers0