SQL Query rawQuery with PHP-MySQLi-Database-Class

Question

I'm using PHP-MySQLi-Database-Class (MysqliDb.php).

I have got that query, but Wamp says:

Fatal error: Problem preparing query

 SELECT car_id FROM gm_cars WHERE car_id NOT IN 
( 
  SELECT reserve_car_id FROM gm_reservations 
 WHERE reserve_dropOff > ? AND reserve_dropOff = ? AND reserve_pickUp ) 

Erreur de syntaxe près de '' à la ligne 1 in C:\wamp\www\work\libs\PHP-MySQLi-Database-Class-master\MysqliDb.php on line 823

Query: Connect to database

>$carsBetweenDates = $db->rawQuery
('SELECT car_id FROM gm_cars WHERE car_id NOT IN 
   (SELECT reserve_car_id FROM gm_reservations 
     WHERE reserve_dropOff > ? AND reserve_dropOff <= ? 
     UNION 
     DISTINCT SELECT reserve_car_id FROM gm_reservations 
      WHERE reserve_pickUp >= ? AND reserve_pickUp < ?)', 
      Array('2014-12-20 20:00:00', '2014-12-22 20:00:00', 
      '2014-12-20 20:00:00', '2014-12-22 20:00:00'));

I don't see the error. Thanks so much. Sorry for my english.

Answer 1

Can you try calling the function like this:

$db->rawQuery('QUERY', array(), FALSE);

I think the sanitizing damages your query ( it may strip html characters, in your case the < > conditions )

Assuming this is the class: https://github.com/joshcam/PHP-MySQLi-Database-Class/blob/master/MysqliDb.php

Answer 2

Try change the query in order to simplify it. First of all you join the two tables into one, and then invert the conditions to remove partially the "NOT IN"

SELECT gm_cars.car_id 
FROM gm_cars LEFT JOIN gm_reservations
ON gm_cars.car_id = gm_reservations.reserve_car_id
WHERE (reserve_dropOff <= ? OR reserve_dropOff > ?)
    AND (reserve_pickUp < ? OR reserve_pickUp >= ?)
    AND gm_cars.car_id NOT IN Array('...')
GROUP BY gm_cars.car_id

One more thing, I assume that you are going to replace the "?" with values in the final query, if you want a more generic query you can do something like this:(example in PHP)

$queryStaff = "SELECT STAFF.id FROM STAFF WHERE name = '" . $your_variable . "'" 

Answer 3

an example.

$db->rawQuery("SELECT * FROM print_orders WHERE print_guests.id = print_orders.id AND print_order_products.order_id = print_orders.id AND  print_orders.id = ? AND print_guests.email = ?  AND print_order_products.order_id = ? " , array($_POST['orden'], $_POST['email'], $_POST['orden']));