Find data 2 days before date mysql and fat free framework

Question

Hi i'm trying to make a module of upcoming services and i need to show the user the services 2 or more days before the date, i am working with php fat free and mysql and i have this query:

SELECT * FROM bitacora WHERE fechaprox >= NOW() - INTERVAL 2 DAY; 

this works on mysql enter image description here

And i am trying to put this in fat free like this:

public function avisos($f3)
{
    $this->M_Bitacora->cliente = 'SELECT nombre FROM cliente WHERE id_cliente= bitacora.id_cliente';
   $result= $this->M_Bitacora->find('SELECT * FROM bitacora WHERE fechaprox >= NOW() - INTERVAL 2 DAY');
   $items= array();
   foreach($result as $bitacora){
       $items[] = $bitacora->cast();
   }
   echo json_encode([
    'mensaje' => count($items) > 0 ? '' : 'Aun no hay registros',
    'info'=> [
        'items' => $items,
        'total' => count($items)
    ]
]);
    
}

But this is my error: Internal Server Error

PDOStatement: 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 'SELECT * FROM bitacora WHERE fechaprox >= NOW() - INTERVAL 2 DAY' at line 1

Can you help me? or is there another way to get that data that i need?

Answer 1

So I'm gonna go out on a limb here and say that M_Bitacora is a DB\SQL\Mapper object right?

If you're using a mapper object, then what you're doing is you use the Mapper object to hide all that pesky SQL and tuck it somewhere else. It has a bunch of helper methods to find(), load(), and select() among other methods to quickly help you get the data you need and map it to a PHP object.

Since you're going the raw SQL route, what you're going to want is to use your db connection. If you still want to use the SQL, then it's best to get your SQL connection var that you created to run the SQL. and then like @Caius Jard suggested, use exec().

<?php

// if this is how you set up your db connection...
$f3->set('DB', new DB\SQL(/*config stuff*/);

// then in your code example above
public function avisos($f3)
{
    // you can just join this in the query below. Much more efficient anyways.
    //$this->M_Bitacora->cliente = 'SELECT nombre FROM cliente WHERE id_cliente= bitacora.id_cliente';
   $items= $f3->DB->exec('SELECT b.*, c.nombre 
        FROM bitacora b 
        JOIN cliente c ON c.id_cliente = b.id_cliente
        WHERE b.fechaprox >= NOW() - INTERVAL 2 DAY');

   // it is automatically fetch as an associative array (hopefully if you configured your PDO object like that)
   //$items= array();
   //foreach($result as $bitacora){
   //    $items[] = $bitacora->cast();
   //}
   echo json_encode([
    'mensaje' => count($items) > 0 ? '' : 'Aun no hay registros',
    'info'=> [
        'items' => $items,
        'total' => count($items)
    ]
]);
    
}