CODEWITHSUNDEEP
phpmysqlsymfony
Gaz-mên Arifi
Gaz-mên Arifi

Reputation: 33

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

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.

Upvotes: 2

Views: 4394

Answers (1)

nbk
nbk

Reputation: 49400

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

Upvotes: 6

Related Questions