Prepared Statement with variable array

Question

I have an array of fields I possibly want to look for in my DB:

$locations = array(
        2,3,5
    );

The number of locations can differ, and I want to create a prepared statement (with Doctrine in Symfony2) that does the same as this:

SELECT * FROM locations WHERE start = 1 AND (end = 2 OR end = 3 OR end = 5)

(I think) I know how to use prepared statements in general, like this:

$query = $em->createQuery(
  'SELECT l
  FROM AppBundle:Location l
  WHERE l.start > :start
  AND (l.end = :end1
  OR l.end = :end2
  OR l.end = :end3)
  ORDER BY l.duration ASC'
)
->setParameter('start', '1')
->setParameter('end1', '2')
->setParameter('end2', '3')
->setParameter('end3', '5');

but how can I do that dynamically if I do not have the size of the array?

Any hint appreciated!

Seems related: http://stackoverflow.com/questions/17589626/symfony2-3-raw-sql-query-with-in-clause and http://stackoverflow.com/questions/5929036/how-to-use-where-in-with-doctrine-2 — , Oct 26 '15 at 11:41

Christian · Accepted Answer · 2015-10-26T12:32:19.233

2

<?php
$locations = array(
    2,3,5
);

$query = $em->createQuery(
  'SELECT l
  FROM AppBundle:Location l
  WHERE l.start > :start
  AND l.end IN :end
  ORDER BY l.duration ASC'
)
->setParameter('start', '1');
->setParameter('end', $locations);

?>

edited Oct 26 '15 at 12:32

answered Oct 26 '15 at 11:33

Christian

1,557
11
16

Thanks, I am on the right track, but `WHERE start = 1 AND end IN("2,3,5")` gives me only one result, whereas `WHERE start = 1 AND end IN(2,3,5)` gives me all three desired. So instead of converting it to a string, I just use the locations array for the IN statement! Thank you – PrimuS Oct 26 '15 at 11:42
Fair enough, not too often I use this so a little foggy on it but if you got it working now then all is good. I will update the answer. – Christian Oct 26 '15 at 11:45
@Christian - Please consider editing your answer and remove the first section with the $locationsString. It really opens things up to sql injection attacks. Your second approach is the way to go. – Cerad Oct 26 '15 at 12:31
@Cerad, Thanks for the advice. Made the change. – Christian Oct 26 '15 at 12:32

score 1 · Answer 2 · answered Oct 26 '15 at 11:48

@Christian's answer is fine, but just for the sake of completion, you can use foreach in order to create your SQL string.

<?php
$locations = array(2, 3, 5);
$endQueries = [];

foreach ($locations as $i => $location) {
    $endQueries[] = "l.end = :end{$i}";
}

$queryString = 'SELECT l
FROM AppBundle:Location l
WHERE l.start > :start
AND (' . implode(' OR ', $endQueries) . ')
ORDER BY l.duration ASC';

$query = $em->createQuery($queryString);
$query->setParameter("start", '1');

foreach ($locations as $i => $location) {
    $query->setParameter("end{$i}", $location);
}

score 0 · Answer 3 · edited Oct 30 '15 at 17:43

0

Maybe you are missing fetchAll?

<?php
$locations = array(
    2,3,5
);

$query = $em->createQuery(
  'SELECT l
  FROM AppBundle:Location l
  WHERE l.start > :start
  AND l.end IN :end
  ORDER BY l.duration ASC'
)
->setParameter('start', '1');
->setParameter('end', $locations);

/* == Added a fetchAll call == */
$query->execute->fetchAll();

?>

edited Oct 30 '15 at 17:43

pedromanoel

3,232
2
24
23

answered Oct 26 '15 at 12:38

John

1,595
4
21
44

Prepared Statement with variable array

3 Answers3