QLSTATE[42000]: Syntax error or access Violation: 1064 You have an error in your SQL syntax (PHP and Mysql)

Question

I am developing a virtual store with PHP 7 and Mysql. In the model class of requests, SQL seems to me to be correct, but since I’ve been trying to solve the problem for some time, I may be missing something.

Connection class:

<?php 

Class Conexao extends Config {

    private $host, $user, $pwd, $db;
    protected $obj, $items = array(), $prefix;
    public $paginacaoLinks, $totalpags, $limit, $init;

    function __construct(){
        $this->host = self::DB_HOST;
        $this->user = self::DB_USER;
        $this->pwd = self::DB_PWD;
        $this->db = self::DB_BASE;
        $this->prefix = self::DB_PREFIX;

        try {
            if($this->Connect() == null){
                $this->Connect();
            }
        } catch (Exception $e) {
            exit($e->getMessage() . '<h2> Erro ao conectar com o banco de dados! </h2>');
        }
    }

    private function Connect(){
        $options = array(
            PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8",
            PDO::ATTR_ERRMODE => PDO::ERRMODE_WARNING
        );
        $link = new PDO("mysql:host={$this->host};dbname={$this->db}", $this->user, $this->pwd, $options);
        return $link;
    }

    function ExecuteSQL($query, array $params = NULL){
        $this->obj = $this->Connect()->Prepare($query);

        if(count($params) > 0){
            foreach($params as $key =>$value){
                $this->obj->bindvalue($key, $value);
            }
        }

        return $this->obj->execute();
    }

    function ListaData(){
        return $this->obj->fetch(PDO::FETCH_ASSOC);
    }

    function TotalData(){
        return $this->obj->rowCount();
    }

    function GetItems(){
        return $this->items;
    }

    function PaginacaoLinks($campo, $tabela) {
        $pag = new Paginacao();

        $pag->getPaginacao($campo, $tabela);
        $this->paginacaoLinks = $pag->link;

        $this->totalpags = $pag->totalpags;
        $this->limit = $pag->limit;
        $this->init = $pag->init;

        $init = $pag->init;
        $limit = $pag->limit;

        if($this->totalpags > 0) {
            return " LIMIT {$init}, {$limit}";
        }else{
            return ' ';
        }
    }

    protected function Paginacao($paginas = array()) {
        $pag = '<ul>';
        $pag .= '<li><a href="?p=1">Início</i></a></li>';

        foreach($paginas as $p):
            $pag .= '<li><a href="?p='.$p.'">' . $p . '</a></li>';
        endforeach;

        $pag .= '<li><a href="?p='. $this->totalpags .'"> ... '.$this->totalpags.'</a></li>';
        $pag .= '</ul>';

        if($this->totalpags > 1) {
            return $pag;
        }
    }

    function showPaginacao() {
        return $this->Paginacao($this->paginacaoLinks);
    }

}

Model Class of Orders

<?php 

class Pedidos extends Conexao {
    function __construct() {
        parent::__construct();
    }

    function GravarPedido($client_id, $order_code, $reference, $orderStatus_id, $value_freight = null, $freight_type = null) {
        $retorno = FALSE;

        $query = "INSERT INTO {$this->prefix}order (order_date, order_time, order_code, reference, value_freight, freight_type, client_id, orderStatus_id) VALUES (:order_date, :order_time, :order_code, :reference, :value_freight, :freight_type, :client_id, :orderStatus_id)";
echo $query;
        $params = array(
            ':order_date' => Sistema::DataAtualUS(),
            ':order_time' => Sistema::HoraAtual(),
            ':order_code' => $order_code,
            ':reference' => $reference,
            ':value_freight' => $value_freight,
            ':freight_type' => $freight_type,
            ':client_id' => (int)$client_id,
            ':orderStatus_id' => (int)$orderStatus_id
        );

        $this->ExecuteSQL($query, $params);
        $this->RecordItems($order_code);

        $retorno = TRUE;
        return $retorno;
    }
}

Checkout Controller for order. In this controller I am doing test by entering the data manually, at the initialization of the order class

<?php 

    if(isset($_SESSION['PRO'])){
        $smarty = new Template();

        $carrinho = new Carrinho();

        $smarty->assign('GET_TEMA', Rotas::getSiteTema());
        $smarty->assign('GET_SITE_HOME', Rotas::getSiteHome());
        $smarty->assign('PAG_CARRINHO', Rotas::pagCarrinho());
        $smarty->assign('PRO', $carrinho->getCarrinho());
        $smarty->assign('VALOR_TOTAL', Sistema::MoedaBR($carrinho->getTotal()));
        $smarty->assign('PAG_ALTERARCARRINHO', Rotas::pagAlterarCarrinho());
        $smarty->assign('PAG_CONFIRMARPEDIDO', Rotas::pagConfirmarPedido());
        $smarty->assign('PAG_FINALIZARPEDIDO', Rotas::pagFinalizarPedido());

        $pedido = new Pedidos();
        $cliente = 1;
        $order_code = $_SESSION['order'];
        $ref = '020181ref';
        $orderStatus_id = 1;

        $pedido->GravarPedido($cliente, $order_code, $ref, $orderStatus_id);

        $smarty->display('finalizar-pedido.tpl');
    }else{

    }

Answer 1

Try writing table and column names between two crases ("`...`"), as below:

$query = "INSERT INTO `{$this->prefix}order` (`order_date`, `order_time`, `order_code`, `reference`, `value_freight`, `freight_type`, `client_id`, `orderStatus_id`) VALUES ...

If the variable $this->prefix do not return anything, will be only the word order, which is a reserved Mysql word. The crases would solve this problem by ignoring any reserved word between them.