PHP MySql Join, GROUP BY problem

[lasha]

Hello all
 
Have a problem with some query and i'll explain it now...

I attached image file with tables which i have

SELECT car.id, car.model, books.id, books.car_id, books.start_date, books.end_date
FROM car LEFT JOIN books ON books.car_id = car.id
WHERE books.car_id IS NULL
OR books.start_date NOT BETWEEN '" . $_POST['start'] . "' AND '" . $_POST['end'] . "'
AND
books.end_date NOT BETWEEN '" . $_POST['start'] . "' AND '" . $_POST['end'] . "'
GROUP BY books.car_id";

when car with id 1 is booked 1 time, car doesnt appears if book date range is matches to users choosen date range, but when car with id 1 is booked 3 times it appears 2 time becouse time range matched only one record of book table. i want that if time rage is matched even for one books record, dont show this car at all..

 

Please help with this and tanks you

 

p.s. Sorry for my English

Edited April 28, 2014 by lasha

[Psycho]

There's another problem with your logic aside from the multiple bookings issue. namely this:

OR books.start_date NOT BETWEEN '" . $_POST['start'] . "' AND '" . $_POST['end'] . "'

AND
books.end_date NOT BETWEEN '" . $_POST['start'] . "' AND '" . $_POST['end'] . "'

Let's say the car was booked from Jan 1 to Jan 31. If your POST dates were Jan 10 to Jan 20 then it would appear as if the car was available because neither the book start date nor the book end date are within the POST dates. Plus, when using OR along with other conditions, it is best to enclose conditions in parens both to ensure it is interpreted correctly and to ensure readability.

 

So, based on your post, I believe you simply want a list of cars that are available within the given time period. If so, you do not need the booking information in the SELECT part of the clause since you don't want cars that are booked in the given date range anyway. A better way to approach this is to use a query to find where the cars are booked for the selected time period and use that as an exclusion.

 

it may seem illogical, but the way to find those current booking that conflict with the request is where the booked start date is less than the request end date AND the booked end date is greater than the request start date

$requestStart =  date('Y-m-d', strtotime($_POST['start']));
$requestEnd = date('Y-m-d', strtotime($_POST['end']));
 
$query = "SELECT car.id, car.model
          FROM car
          WHERE car.id NOT IN
          (
              SELECT car_id
              FROM Books
              WHERE books.start_date < '{$requestEnd}'
                AND books.end_date > '{$requestStart}'
          )";

EDIT: Also, Never use POST data directly in your query. The code above has some logic to prevent malicious input to cause SQL injections.

Edited April 28, 2014 by Psycho

[Barand]

try

SELECT car.id, car.model, books.id, books.car_id, books.start_date, books.end_date
FROM car 
LEFT JOIN books ON books.car_id = car.id
    AND books.start_date <= '{$_POST['end']}'
    AND books.end_date >= '{$_POST['start']}' 
WHERE books.car_id IS NULL
GROUP BY books.car_id

[lasha]

Thanks for helping

 

Psycho, it is correct ideas what you gave to me, i guess my query was illogical but to succesfully accomplish this idea i need more help becouse my query is more complex than i posted above.

 

How to translate at your way? I stuck here

 

This is real query in my booking module

SELECT car.id, car.model, car.engine, car.passenger, car.liters, car.transmission, car.doors, car.engine_type, car.luggage, car.condintioner, car.stereo, car.gps, car.propulsion, car.price1, car.price2, car.price3, car.price4, car.img, car.thumb, car.owner, car.location, books.car_id, books.start_date, books.end_date FROM car
LEFT JOIN books ON car.id = books.car_id
WHERE 
car.cat_id = $cat_id AND
books.car_id IS NULL
AND
car.location = 'london'
AND
car.state = 'published'
OR
car.cat_id = $cat_id 
AND books.start_date 
NOT BETWEEN '" . $_POST['start'] . "' AND  '" . $_POST['end'] . "' 
AND books.end_date
NOT BETWEEN '" . $_POST['start'] . "' AND  '" . $_POST['end'] . "'
AND
car.location = 'london'
AND
car.state = 'published'
GROUP BY car.id";

EDIT: Also, Never use POST data directly in your query. The code above has some logic to prevent malicious input to cause SQL injections.

I will keepin mind

Edited April 28, 2014 by lasha

[Psycho]

@Barand,

 

I tried the same thing - but it doesn't work. For some reason, when adding the additional parameters to the ON condition for the dates it is excluding those records from the "Car" table that do not meet those conditions from the results (where the car_id would be NULL) - even though it is using a LEFT JOIN. Not sure why. When just using the books ON books.car_id = car.id condition it includes all the car records and has a NULL value for car_id. Not sure if it is a bug in the version of MySQL I am using or what.

 

@lasha

 

One thing that some of us do not like is whenwe answer a question and then get a response along the lines of "that's great but what I really need is this". Many times, the thing the user needs requires a completely different solution and we've just wasted out time.

 

In this case, you can use the query I provided and add the other excluding conditions to the WHERE condition. I think this will get you what you want.

$query = "SELECT car.id, car.model, car.engine, car.passenger, car.liters, car.transmission, car.doors,
                 car.engine_type, car.luggage, car.condintioner, car.stereo, car.gps, car.propulsion,
                 car.price1, car.price2, car.price3, car.price4, car.img, car.thumb, car.owner, car.location
          FROM car
          WHERE car.id NOT IN
          (
              SELECT car_id
              FROM Books
              WHERE books.start_date < '{$requestEnd}'
                AND books.end_date > '{$requestStart}'
          )
          AND car.cat_id = $cat_id
          AND car.location = 'london'
          AND car.state = 'published'"

Edited April 28, 2014 by Psycho

[lasha]

Thank you very much Psycho... I successfully added to my module, code that you provided for me. It did the thing. I'll keep in mind every word you say. Thank you again

 

This is current working code. And it works perfectly )

 

$query2 = "SELECT  car.id, car.model, car.engine, car.passenger, car.liters, car.transmission, car.doors, car.engine_type, car.luggage, car.condintioner, car.stereo, car.gps, car.propulsion, car.price1, car.price2, car.price3, car.price4, car.img, car.thumb, car.owner, car.location, car.state
          FROM car
          WHERE car.id NOT IN
          (
              SELECT car_id
              FROM books
              WHERE books.start_date < '{$requestEnd}'
                AND books.end_date > '{$requestStart}'
          )
          AND car.cat_id = $cat_id
          AND car.location = 'london'
          AND car.state = 'published'";

Edited April 28, 2014 by lasha

[Barand]

@Psycho

 

Strange - it worked for me

mysql> SELECT * FROM books;
+---------+--------+------------+------------+
| book_id | car_id | start_date | end_date   |
+---------+--------+------------+------------+
|       1 |      1 | 2014-05-03 | 2014-05-07 |
|       2 |      1 | 2014-05-08 | 2014-05-11 |
|       3 |      2 | 2014-05-06 | 2014-05-08 |
|       4 |      3 | 2014-05-03 | 2014-05-12 |
|       5 |      4 | 2014-05-03 | 2014-05-04 |
|       6 |      4 | 2014-05-11 | 2014-05-15 |
+---------+--------+------------+------------+

ID 4 is the only one not booked between May 5 and May 10

mysql> SELECT car.id, car.model
    -> FROM car
    -> LEFT JOIN books ON books.car_id = car.id
    ->     AND books.start_date <= '2014-05-10'
    ->     AND books.end_date >= '2014-05-05'
    -> WHERE books.car_id IS NULL
    -> GROUP BY car.id;
+----+----------+
| id | model    |
+----+----------+
|  4 | Fiat 500 |
+----+----------+

[Psycho]

 

@Psycho

 

Strange - it worked for me

 

The problem I was having was for Car records for which there were no current, corresponding Book records. I found my problem, which was I was doing a GROUP BY on the car_id column from the Books table. But, I think it is still odd.

 

If a card does has no current Booking records, the value of car_id in the LEFT JOIN would be NULL.

 

Likewise, if a car has existing booking records, but none match the JOIN conditions, the value of car_id would likewise be NULL>

 

But, for some reason, when using GROUP BY car_id, a car matching that first scenario would be in the results, whereas one in the second does not.