Symfony SQL error message : "SQLSTATE[42000]: Syntax error or access violation: 1064"

Question

I have an error that I don't comprehend :

An exception occurred while executing 'INSERT INTO achats (year_month, modele, code_reference, date_de_ajout, cout, date_de_achat, importance, ordre, type, location_de_achat) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["2020-07", "St\u2011Luc (echo\u2011graphie)", null, "2020-01-23", 2.97, "2020-01-23", null, 13, 24, 7]:

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 'year_month, modele, code_reference, date_de_ajout, cout, date_de_achat, importan' at line 1

My Entity is :

<?php

namespace App\Entity;

use Doctrine\ORM\Mapping as ORM;

use Symfony\Component\Validator\Constraints as Assert;

/**
 * Achats
 *
 * @ORM\Table(name="achats", indexes={@ORM\Index(name="type", columns={"type", "location_de_achat"}), @ORM\Index(name="location_de_achat", columns={"location_de_achat"}), @ORM\Index(name="Modele", columns={"Modele"}), @ORM\Index(name="IDX_9920924E8CDE5729", columns={"type"})})
 * @ORM\Entity
 */
class Achats
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="bigint", nullable=false, options={"unsigned"=true})
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * @return int
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * @param int $id
     * @return Achats
     */
    public function setId($id)
    {
        $this->id = $id;
        return $this;
    }

    //=======================================================================================

    /**
     * @ORM\Column(name="year_month", type="string", length=255)
     * @Assert\Length(
     *      min = 7,
     *      max = 7,
     *      minMessage = "Your date name must be at least {{ limit }} characters long",
     *      maxMessage = "Your date name cannot be longer than {{ limit }} characters"
     * )
     */
    private $yearMonth;

    public function getYearMonth(): ?string
    {
        return $this->yearMonth;
    }

    public function setYearMonth(string $yearMonth): self
    {
        $this->yearMonth = $yearMonth;

        return $this;
    }

    //=======================================================================================

    /**
     * @var string|null
     *
     * @ORM\Column(name="modele", type="string", length=255, nullable=true)
     */
    private $modele;

    /**
     * @return string|null
     */
    public function getModele()
    {
        return $this->modele;
    }

    /**
     * @param string|null $modele
     * @return Achats
     */
    public function setModele($modele)
    {
        $this->modele = $modele;
        return $this;
    }

    //=======================================================================================

    /**
     * @var string|null
     *
     * @ORM\Column(name="code_reference", type="string", length=255, nullable=true)
     */
    private $codeReference;

    /**
     * @return string|null
     */
    public function getCodeReference()
    {
        return $this->codeReference;
    }

    /**
     * @param string|null $codeReference
     * @return Achats
     */
    public function setCodeReference($codeReference)
    {
        $this->codeReference = $codeReference;
        return $this;
    }

    //=======================================================================================

    /**
     * @var \DateTime|null
     *
     * @ORM\Column(name="date_de_ajout", type="date", nullable=true)
     */
    private $dateDeAjout;

    /**
     * @return \DateTime|null
     */
    public function getDateDeAjout()
    {
        return $this->dateDeAjout;
    }

    /**
     * @param \DateTime|null $dateDeAjout
     * @return Achats
     */
    public function setDateDeAjout($dateDeAjout)
    {
        $this->dateDeAjout = $dateDeAjout;
        return $this;
    }

    //=======================================================================================

    /**
     * @var int|null
     *
     * @ORM\Column(name="cout", type="float", nullable=true, options={"unsigned"=true})
     */
    private $cout;

    /**
     * @return int|null
     */
    public function getCout()
    {
        return $this->cout;
    }

    /**
     * @param int|null $cout
     * @return Achats
     */
    public function setCout($cout)
    {
        $this->cout = $cout;
        return $this;
    }

    //=======================================================================================

    /**
     * @var \DateTime|null
     *
     * @ORM\Column(name="date_de_achat", type="date", nullable=true)
     */
    private $dateDeAchat;

    /**
     * @return \DateTime|null
     */
    public function getDateDeAchat()
    {
        return $this->dateDeAchat;
    }

    /**
     * @param \DateTime|null $dateDeAchat
     * @return Achats
     */
    public function setDateDeAchat($dateDeAchat)
    {
        $this->dateDeAchat = $dateDeAchat;
        return $this;
    }

    //=======================================================================================

    /**
     * @var \Types
     *
     * @ORM\ManyToOne(targetEntity="Types")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="type", referencedColumnName="id")
     * })
     */
    private $type;

    /**
     * @return \Types
     */
    public function getType()
    {
        return $this->type;
    }

    /**
     * @param \Types $type
     * @return Achats
     */
    public function setType($type)
    {
        $this->type = $type;
        return $this;
    }

    //=======================================================================================

    /**
     * @var \Lieux
     *
     * @ORM\ManyToOne(targetEntity="Lieux")
     * @ORM\JoinColumns({
     *   @ORM\JoinColumn(name="location_de_achat", referencedColumnName="id")
     * })
     */
    private $locationDeAchat;

    /**
     * @return \Lieux
     */
    public function getLocationDeAchat()
    {
        return $this->locationDeAchat;
    }

    /**
     * @param \Lieux $locationDeAchat
     * @return Achats
     */
    public function setLocationDeAchat($locationDeAchat)
    {
        $this->locationDeAchat = $locationDeAchat;
        return $this;
    }

    //=======================================================================================

    /**
     * @ORM\Column(type="smallint", nullable=true)
     */
    private $importance;

    public function getImportance(): ?int
    {
        return $this->importance;
    }

    public function setImportance(?int $importance): self
    {
        $this->importance = $importance;

        return $this;
    }

    //=======================================================================================

    /**
     * @ORM\Column(type="integer", nullable=true)
     */
    private $ordre;

    public function getOrdre(): ?int
    {
        return $this->ordre;
    }

    public function setOrdre(?int $ordre): self
    {
        $this->ordre = $ordre;

        return $this;
    }
}

Controller :

<?php

namespace App\Controller;

use App\Entity\Achats;
use App\Form\AchatsType;
use App\Form\AchatsNewType;
use Exception;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;

/**
 * @Route("/")
 */
class AchatsController extends AbstractController
{

    /**
     * @Route("/", name="achats_index", methods={"GET"})
     */
    public function index(): Response
    {
        $achats = $this->getDoctrine()
            ->getRepository(Achats::class)
            ->findAll();

        return $this->render('achats/index.html.twig', [
            'achats' => $achats,
        ]);
    }

    /**
     * @Route("/achats/new", name="achats_new", methods={"GET","POST"})
     * @param Request $request
     * @return Response
     * @throws Exception
     */
    public function new(Request $request): Response
    {
        $achat = new Achats();
        $form = $this->createForm(AchatsNewType::class, $achat);
        $form->handleRequest($request);

        if ($form->isSubmitted() && $form->isValid()) {

            $now = new \DateTime('now');

            $entityManager = $this->getDoctrine()->getManager();
            $entityManager->persist($achat);
            $entityManager->flush();

            return $this->redirectToRoute('achats_index');
        }

        return $this->render('achats/new.html.twig', [
            'achat' => $achat,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/achats/{id}", name="achats_show", methods={"GET"})
     */
    public function show(Achats $achat): Response
    {
        return $this->render('achats/show.html.twig', [
            'achat' => $achat,
        ]);
    }

    /**
     * @Route("/achats/{id}/edit", name="achats_edit", methods={"GET","POST"})
     */
    public function edit(Request $request, Achats $achat): Response
    {
        $form = $this->createForm(AchatsType::class, $achat);
        $form->handleRequest($request);

        if ($form->isSubmitted() && $form->isValid()) {
            $this->getDoctrine()->getManager()->flush();

            return $this->redirectToRoute('achats_index');
        }

        return $this->render('achats/edit.html.twig', [
            'achat' => $achat,
            'form' => $form->createView(),
        ]);
    }

    /**
     * @Route("/achats/{id}", name="achats_delete", methods={"DELETE"})
     */
    public function delete(Request $request, Achats $achat): Response
    {
        if ($this->isCsrfTokenValid('delete'.$achat->getId(), $request->request->get('_token'))) {
            $entityManager = $this->getDoctrine()->getManager();
            $entityManager->remove($achat);
            $entityManager->flush();
        }

        return $this->redirectToRoute('achats_index');
    }
}

Form :

<?php

namespace App\Form;

use App\Entity\Achats;

use Symfony\Component\Form\AbstractType;
use Symfony\Component\Form\Extension\Core\Type\ChoiceType;
use Symfony\Component\Form\Extension\Core\Type\DateType;
use Symfony\Component\Form\Extension\Core\Type\IntegerType;
use Symfony\Component\Form\Extension\Core\Type\MoneyType;
use Symfony\Component\Form\Extension\Core\Type\NumberType;
use Symfony\Component\Form\Extension\Core\Type\TextType;
use Symfony\Component\Form\FormBuilderInterface;
use Symfony\Component\OptionsResolver\OptionsResolver;

class AchatsNewType extends AbstractType
{
    public function buildForm(FormBuilderInterface $builder, array $options)
    {
        $builder
            ->add('importance', IntegerType::class, [

                'required' => false,

                'attr' => [
                    'placeholder' => 'Importance',
                    'min' => '0',
                    'max' => '100',
                    'step' => '0.01'
                ]
            ])

            ->add('yearMonth', TextType::class, [

                'attr' => [
                    'placeholder' => 'YYYY‑MM',
                    'min' => '7',
                    'max' => '7',
                ]
            ])

            ->add('ordre', IntegerType::class, [

                'required' => false,

                'attr' => [
                    'placeholder' => 'Ordre d\'importance',
                    'min' => '1',
                    'step' => '1'
                ]
            ])
            ->add('modele', TextType::class, [])

            ->add('codeReference', TextType::class, [

                'required' => false,
            ])

            ->add('dateDeAjout', DateType::class, [

                'data' => new \DateTime(),
            ])

            ->add('cout', NumberType::class, [

                'attr' => [
                    'placeholder' => 'Coût',
                    'min' => '0',
                    'step' => '0.01'
                ]
            ])

            ->add('dateDeAchat', DateType::class, [

                'data' => new \DateTime(),
            ])

            ->add('type')

            ->add('locationDeAchat');
    }

    public function configureOptions(OptionsResolver $resolver)
    {
        $resolver->setDefaults([
            'data_class' => Achats::class,
        ]);
    }
}

Please, if someone have an advice, I will be grateful.

If there is no any invisible (non-printable) byte or char between opening bracket and year_month than the only visible reason may be that year_month is treated as an interval measurement unit... — Akina
– Akina, Commented Jan 27, 2020 at 19:07

nealio82 · Accepted Answer · 2020-01-27 21:31:37Z

6

year_month

is a reserved word in MySQL so it can't be used as column name without special treatment

You need to enclose it in backticks in your query, like so

`year_month`

So your query would be

'INSERT INTO achats (`year_month`, modele, code_reference ...

As you're using Symfony I'll assume you're using Doctrine as well, in which case you need to tell the ORM to use the backticks in its generated queries

edited Jan 27, 2020 at 21:31

nealio82

2,6331 gold badge19 silver badges19 bronze badges

answered Jan 27, 2020 at 19:12

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

1 Comment

showdev Over a year ago

For reference, see Keywords and Reserved Words and Temporal Interval Arguments.

Collectives™ on Stack Overflow

Symfony SQL error message : "SQLSTATE[42000]: Syntax error or access violation: 1064"

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related