Search code examples
phpsqlpdolocalhostslim

Error: "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax. ". Problem in the .env or PDO?

I am fixing someone else's code and am getting a

"SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax" error.

My first thought was I didn't install slim correctly, but installing again didn't make any difference. So maybe the problem is in the .env file or the PDO. Can anyone help?

enter image description here

PDO: 


declare(strict_types=1);

namespace App\Model;

use PDO;

abstract class PdoModel
{
   protected static $connection;

   protected function getConnection() : PDO
   {
       // Singleton pattern to use just 1 DB connection for all database calls
       if (!self::$connection) {
           self::$connection = new PDO('mysql:host=' . getenv('DB_HOST') . ';dbname=' . getenv('DB_DATABASE'), getenv('DB_USERNAME'), getenv('DB_PASSWORD'));
           self::$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
           self::$connection->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
       }

       return self::$connection;
   }
}

.env:

DB_HOST=localhost
DB_DATABASE=exam
DB_USERNAME=root
DB_PASSWORD=
DB_PORT=3306

first part of eventmodel:


declare(strict_types=1);

namespace App\Model;

use DateTime;

class EventModel extends PdoModel
{
 public function getEvents() : array
 {
     $query = "SELECT e.id,e.name,e.description,e.event_manager_id,em.name as event_manager_name ,
     e.company_name,DATE_FORMAT(datetime,'%d-%m-%Y') as date,DATE_FORMAT(datetime,'%H:%i') as time,
      e.maximum_amount_of_guests as guests,
               FROM event e LEFT JOIN event_manager em ON (em.id = e.event_manager_id) ORDER BY date DESC";
     $statement = $this->getConnection()->prepare($query);
     $statement->execute();
     return $statement->fetchAll();
 }

Any suggestions are welcome. I have no idea left what the problem might be.

Solution

  • In your query you have an extra comma before FROM

    Correct Query:

    SELECT 
    e.id,
    e.name,
    e.description,
    e.event_manager_id,
    em.name as event_manager_name,
    e.company_name,
    DATE_FORMAT(datetime,'%d-%m-%Y') as date,
    DATE_FORMAT(datetime,'%H:%i') as time,
    e.maximum_amount_of_guests as guests
FROM event e 
LEFT JOIN event_manager em ON (em.id = e.event_manager_id) 
ORDER BY date DESC

    Error in your query:

    You have an issue here:

    e.maximum_amount_of_guests as guests, // this will generate error
               FROM event e

    1064 clearly show you, you have an issue in your query not .env file.

    Server Error Reference: