Symfony Invalid parameter format, : given

Question

I need create multiply search by years. From request I get string like 2017,2018 and then I want get Questions which createdAt, between from start year and end year. I have query builder with part, and I'am not understand why I have this error

        if ($paramFetcher->get('years')) {

        $orXSearch = $qb->expr()->orX();
        $yearData = trim($paramFetcher->get('years'));
        foreach (explode(',', $yearData) as $key => $id) {
            if (!$id) {
                continue;
            }

            $orXSearch
                ->add($qb->expr()->between('q.createdAt', ':'.$key.'dateFrom', ':'.$key.'dateTo'));

            $date = $this->additionalFunction->validateDateTime($id, 'Y');
            $first = clone $date;
            $first->setDate($date->format('Y'), 1, 1);
            $first->setTime(0, 0, 0);
            $last = clone $date;
            $last->setDate($date->format('Y'), 12, 31);
            $last->setTime(23, 59   , 59);

            $qb
                ->setParameter($key.'dateFrom', $first->format('Y-m-d H:i:s'))
                ->setParameter($key.'dateTo', $last->format('Y-m-d H:i:s'));
        }
        $qb->andWhere($orXSearch);
    }

error:

symfony Invalid parameter format, : given, but :<name> or ?<num> expected.

What if you avoid to concat string within the DQL? So, after your if(!$id), add $cParamA=':'.$key.'dateFrom'; $cParamB=':'.$key.'dateTo'; and then use $cParamA and $cParamB in your DQL. You would most likely need two other variable for the seParameter. Also, make sure to trim $key just in case — Preciel
– Preciel, Commented Feb 12, 2018 at 19:15
SELECT q FROM AppBundle:Questions q WHERE (q.createdAt BETWEEN :0dateFrom AND :0dateTo) OR (q.createdAt BETWEEN :1dateFrom AND :1dateTo) ORDER BY q.createdAt ASC and I checked parameters, everything is in place — shuba.ivan
– shuba.ivan, Commented Feb 12, 2018 at 19:31
I changed query to $orXSearch ->add($qb->expr()->between( 'q.createdAt', $qb->expr()->literal($first->format('Y-m-d H:i:s')), $qb->expr()->literal($last->format('Y-m-d H:i:s'))) ); but I think this is bad way, I want use parameters — shuba.ivan
– shuba.ivan, Commented Feb 12, 2018 at 19:32
yes, you should use parameters indeed... But I can't guess where your query is wrong... :/ — Preciel
– Preciel, Commented Feb 12, 2018 at 19:44
I found another solution here stackoverflow.com/questions/16445652/… — armel sauvy
– armel sauvy, Commented Jul 16, 2021 at 13:41

lxg · Accepted Answer · 2018-02-12 21:48:19Z

6

In your foreach loop, you’re looping over the result of an explode operation which yields a numeric array, i.e. $key will always have a numeric value.

Hence, your parameter placeholder is colon + number + string, i.e. :1dateFrom. This is not allowed. Either you reference a string value with a colon + string placeholder (:foo), or you reference a numeric value with a question mark + number value (?1).

Your problem is easy to solve: Simply add any letter between the colon and the number, and you’re good:

->add($qb->expr()->between(
    'q.createdAt',
    ':x'.$key.'dateFrom',
    ':x'.$key.'dateTo'
));

answered Feb 12, 2018 at 21:48

lxg

13.2k13 gold badges56 silver badges81 bronze badges

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

2 Comments

Glioburd Over a year ago

Just lost couple of hours because of this, I did :1filter, :2filter, instead of :filter1, :filter2 as param name. Thanks for saving my laptop that I was about to throw out the window.

miko7358 Over a year ago

I join your comment @Glioburd my laptop was about to get crushed. Thanks Lxg

Collectives™ on Stack Overflow

Symfony Invalid parameter format, : given

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related