Symfony Doctrine update query fail

Question

My code is:

$query = $em->createQuery("UPDATE AppBundle:Project u SET u.name=1, u.key=?2, u.leader=?3 WHERE u.id = ?4");
$query->setParameter(1, $project->name);
$query->setParameter(2, $project->key);
$query->setParameter(3, $project->lead->name);
$query->setParameter(4, $project->id);
$result = $query->getResult();

Now when I run it, it gives me a error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key = 'EIAD', leader = 'fake.name' WHERE id = '12305'' at line 1

I cant figure out what I'm doing wrong here. I even tried with the Object update like:

$project->setname();

etc.

EDIT: Forgot to say the code is used in a self created command!

I don't think it will solve the issue but you have `?` missing in `u.name=1`. Should be `u.name=?1`. — Jakub Matczak, Aug 31 '17 at 09:55
@JakubMatczak Forgotten but didn't solved the issue, same error still — Nubcake, Aug 31 '17 at 11:00

score 2 · Accepted Answer · answered Aug 31 '17 at 10:05

2

Using Querybuilder. Example:

$em = $this->getDoctrine()->getManager();     
$query = $em->getRepository(Project::class)->createQueryBuilder('')
            ->update(Project::class, 'u')

            ->set('u.name', ':name')
            ->set('u.key', ':key')
            ->set('u.leader', ':leader')
            ->setParameter('name', $project->name)
            ->setParameter('key', $project->key)
            ->setParameter('leader', $project->leader)

            ->where('u.id = :id')
            ->setParameter('id', $project->id)
            ->getQuery();

$result = $query->execute();

answered Aug 31 '17 at 10:05

Cesur APAYDIN

806
3
11
24

Well I now got an other error: [Doctrine\ORM\Query\QueryException] [Semantical Error] line 0, col 7 near 'AppBundle\Command\Project': Error: Class 'AppBundle\Command\Project' is not defined. [Doctrine\ORM\Query\QueryException] UPDATE AppBundle\Command\Project u SET u.name = :name, u.key = :key, u.leader = :leader WHERE u.id = :id Forgot to say that the code is used in a self created command – Nubcake Aug 31 '17 at 11:03
You need a use statement for Project or just go back to AppBundle:Project. – Cerad Aug 31 '17 at 12:01
@Cerad Also tried it then I got the same error back: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key = 'EIAD', leader = 'fake.name' WHERE id = '12305'' at line 1 – Nubcake Aug 31 '17 at 13:20
key is a reserved mysql word. https://dev.mysql.com/doc/refman/5.5/en/keywords.html Change the column name to something else. – Cerad Aug 31 '17 at 13:25
@Cerad Woahh, totally forgotten! Thanks! – Nubcake Aug 31 '17 at 14:49

score 0 · Answer 2 · answered Aug 31 '17 at 13:53

0

The properties of Entities are private or protected. This means you cannot access them directly like this: $project->name you have to use the getters and setters like this: $project->getName()

answered Aug 31 '17 at 13:53

jrswgtr

2,287
8
23
49

$project is a external cURL request to an api that gives me a public object back. Nothing to do with the issue ;) See in the error that the values are filled in. – Nubcake Aug 31 '17 at 14:41

Symfony Doctrine update query fail

2 Answers2

Linked