Rosi Evangelin Rosi Evangelin - 7 months ago 80
SQL Question

Symfony 2 - Doctrine ORM Update query not working

I am trying to update a mysql table with following query using Doctrine. But the table is not get updated. Also below code didnt throw any error. I am totally confused. If i run the query, taken from getDQL() method, in mysql directly it showing 0 rows updated becuase of inproper qoutes. it is working after placed proper qoutes for values in the query. Need help to solve this puzzle.

Since i am new to doctrine, i will use the examples give in querybuilder class file.

$support = $this->createQueryBuilder('p')
->update('gcns', 'g')
->set("g.isActive", "0")
->andWhere("g.issn='".$issn."'");

Answer

Do you ever execute the query or are you just building it? You should have something along these lines to execute it:

$support->getQuery()->getSingleScalarResult();

If i run the query, taken from getDQL() method, in mysql directly it showing 0 rows updated becuase of inproper qoutes.

getDQL() returns DQL not SQL, so it will have improper quotesif you try to run it directly inside MySQL, but that's expected.

You shouldn't concatenate $issn into your query. You should use parameters instead:

$qb = $this->createQueryBuilder()

$support = $qb->update('gcns', 'g')
   ->set('g.isActive', '0')
   ->andWhere( $qb->expr()->eq('g.issn', ':issn') )
   ->setParameter( 'issn', $issn )
   ->getQuery()->getSingleScalarResult()
;