Letsrocks Letsrocks - 7 months ago 58
PHP Question

Symfony2\Doctrine - Storage array in database

I'm building an Ecommerce App, and I would like to storage the user's orders.

FYI : I'm using

symfony 2.8
and
Doctrine2


So I have a Array field
order
in my Order entity. And in my controller I build an array with all the informations I want (products, user information, amount, tva).
But I got an SQL Error, and I dont know why. But I think it's when symfony2 serialize my array.

The Error :


Uncaught PHP Exception Doctrine\DBAL\Exception\SyntaxErrorException: "An exception occurred while executing 'INSERT INTO orders (validate, date, reference, order, user_id) VALUES (?, ?, ?, ?, ?)' with params [0, "2016-04-23 13:07:55", 0, "a:7:{s:3:\"tva\";a:2:{s:3:\"20%\";d:299.80000000000001;s:3:\"10%\";d:21;}s:7:\"product\";a:3:{i:1;a:5:{s:9:\"reference\";s:9:\"PROD00001\";s:4:\"name\";s:10:\"MacBookPro\";s:8:\"quantity\";i:1;s:7:\"priceHT\";d:999;s:8:\"priceTTC\";d:1198.8;}i:2;a:5:{s:9:\"reference\";s:9:\"PROD00002\";s:4:\"name\";s:16:\"Headphones Apple\";s:8:\"quantity\";s:1:\"3\";s:7:\"priceHT\";d:69.989999999999995;s:8:\"priceTTC\";d:76.989999999999995;}i:3;a:5:{s:9:\"reference\";s:9:\"PROD00003\";s:4:\"name\";s:20:\"T\u00e9l\u00e9vision Samsung\";s:8:\"quantity\";i:1;s:7:\"priceHT\";d:499.99000000000001;s:8:\"priceTTC\";d:599.99000000000001;}}s:8:\"delivery\";a:9:{s:9:\"firstname\";s:3:\"Doe\";s:8:\"lastName\";s:4:\"John\";s:5:\"email\";s:12:\"test@test.fr\";s:5:\"phone\";s:10:\"6666666666\";s:6:\"adress\";s:14:\"10 rue du test\";s:10:\"complement\";N;s:4:\"city\";s:6:\"Nantes\";s:7:\"country\";s:6:\"France\";s:10:\"postalCode\";s:5:\"44000\";}s:6:\"invoic\";a:9:{s:9:\"firstname\";s:3:\"Doe\";s:8:\"lastName\";s:4:\"John\";s:5:\"email\";s:12:\"test@test.fr\";s:5:\"phone\";s:10:\"6666666666\";s:6:\"adress\";s:14:\"10 rue du test\";s:10:\"complement\";N;s:4:\"city\";s:6:\"Nantes\";s:7:\"country\";s:6:\"France\";s:10:\"postalCode\";s:5:\"44000\";}s:8:\"amountHT\";d:1709;s:9:\"amountTTC\";d:2030;s:5:\"token\";s:40:\"2627ca31ef4d9d6ab3bf69dd38f02cbb8468bf7f\";}", 2]: 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 'order, user_id) VALUES (0, '2016-04-23 13:07:55', 0, 'a:7:{s:3:\"tva\";a:2:{s:3:' at line 1" at C:\wamp\www\eCommerce\vendor\doctrine\dbal\lib\Doctrine\DBAL\Driver\AbstractMySQLDriver.php line 90


This is my Order Entity

/**
* Orders
*
* @ORM\Table(name="orders")
* @ORM\Entity(repositoryClass="Ecommerce\EcommerceBundle\Repository\OrdersRepository")
*/
class Orders
{
/**
* @var int
*
* @ORM\Column(name="id", type="integer")
* @ORM\Id
* @ORM\GeneratedValue(strategy="AUTO")
*/
private $id;

/**
* @var bool
*
* @ORM\Column(name="validate", type="boolean")
*/
private $validate;

/**
* @var \DateTime
*
* @ORM\Column(name="date", type="datetimetz")
*/
private $date;

/**
* @var int
*
* @ORM\Column(name="reference", type="integer")
*/
private $reference;

/**
* @ORM\ManyToOne(targetEntity="Users\UsersBundle\Entity\Users", inversedBy="orders")
* @ORM\JoinColumn(nullable=true)
*/
private $user ;

/**
* @var array
*
* @ORM\Column(name="order", type="array")
*/
private $order;


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

/**
* Set validate
*
* @param boolean $validate
* @return Orders
*/
public function setValidate($validate)
{
$this->validate = $validate;

return $this;
}

/**
* Get validate
*
* @return boolean
*/
public function getValidate()
{
return $this->validate;
}

/**
* Set date
*
* @param \DateTime $date
* @return Orders
*/
public function setDate($date)
{
$this->date = $date;

return $this;
}

/**
* Get date
*
* @return \DateTime
*/
public function getDate()
{
return $this->date;
}

/**
* Set reference
*
* @param integer $reference
* @return Orders
*/
public function setReference($reference)
{
$this->reference = $reference;

return $this;
}

/**
* Get reference
*
* @return integer
*/
public function getReference()
{
return $this->reference;
}

/**
* Set user
*
* @param \Users\UsersBundle\Entity\Users $user
* @return Orders
*/
public function setUser(\Users\UsersBundle\Entity\Users $user = null)
{
$this->user = $user;

return $this;
}

/**
* Get user
*
* @return \Users\UsersBundle\Entity\Users
*/
public function getUser()
{
return $this->user;
}
/**
* Constructor
*/
public function __construct()
{
$this->products = new \Doctrine\Common\Collections\ArrayCollection();
}

/**
* Add products
*
* @param \Ecommerce\EcommerceBundle\Entity\Products $products
* @return Orders
*/
public function addProduct(\Ecommerce\EcommerceBundle\Entity\Products $products)
{
$this->products[] = $products;

return $this;
}


/**
* Set order
*
* @param array $order
* @return Orders
*/
public function setOrder($order)
{
$this->order = $order;

return $this;
}

/**
* Get order
*
* @return array
*/
public function getOrder()
{
return $this->order;
}
}


My Controller

public function orderAction(Request $request){

$session = $request->getSession() ;
$em = $this->getDoctrine()->getManager() ;
$generator = $this->container->get('security.secure_random');
if(!$session->has('order')){
$order = new Orders();
} else {
$order = $em->getRepository('EcommerceBundle:Orders')->find($session->get('order')) ;
}
$order->setDate(new \DateTime());
$order->setUser($this->container->get('security.context')->getToken()->getUser());
$order->setValidate(0);
$order->setReference(bin2hex($generator->nextBytes(20)));
$order->setOrder($this->order($request));
if(!$session->has('order')){
$em->persist($order);
$session->set('order',$order) ;
}
$em->flush() ;

return new Response($order->getId());
}


I'm really stuck right now

Tell me if you need more information or code.

Thanks.

Answer

order is a reserved SQL word. You need to quote it: Quoting Reserved Words

Comments