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: stackoverflow.com/questions/17589626/… and stackoverflow.com/questions/5929036/… — user5051310
– user5051310, Commented Oct 26, 2015 at 11:41

Christian · Accepted Answer · 2015-10-26 12:32:19Z

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, 2015 at 12:32

answered Oct 26, 2015 at 11:33

Christian

1,57712 silver badges16 bronze badges

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

4 Comments

PrimuS Over a year ago

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

Christian Over a year ago

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.

Cerad Over a year ago

@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.

Christian Over a year ago

@Cerad, Thanks for the advice. Made the change.

pedromanoel · Accepted Answer · 2015-10-26 11:48:52Z

1

@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);
}

answered Oct 26, 2015 at 11:48

pedromanoel

3,3422 gold badges27 silver badges24 bronze badges

Comments

pedromanoel · Accepted Answer · 2015-10-30 17:43:55Z

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, 2015 at 17:43

pedromanoel

3,3422 gold badges27 silver badges24 bronze badges

answered Oct 26, 2015 at 12:38

John

1,6654 gold badges24 silver badges50 bronze badges

Collectives™ on Stack Overflow

Prepared Statement with variable array

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related