PHP: A way to sort through a loop and exclude certain entries after the query

Question

I am creating a system that displays items from a database table, dependant on if they are available.

I have two tables:

Reservations:

----------------------------------------------------------------------
| ResID | CarID | UserID | startTime | startDate | endTime | endDate  |
----------------------------------------------------------------------
|   1   |   4   |    4   | 13:00:00  |2013-04-21 |13:00:00 |2013-04-29|

And Cars:

-------------------------------------------
| CarID  |  make | model | serial | image |
-------------------------------------------
|   1    | Honda | civic | ky675  | 1.png | 

So when a user visits a page, they input a start date and end date, a list of cars is then displayed after a query is ran to check if the car is available on that date.

So I came up with this query.

$carstring = mysql_query("SELECT * FROM cars 
                            {$statement} 
                            AND deleted = 'no' 
                            AND carID NOT IN (
                              SELECT carID FROM reservations 
                                WHERE startDate = '".$sqlcoldate."'
                            ) 
                          GROUP BY model");

What this does is select all cars from the database, it then however excludes the array of carIDs that are the array result from the sub-query. The remainder cars, it groups by model and displays them in a loop like so:

$getcars = $carstring;
while($searchcars = mysql_fetch_array($getcars)) {

 **some HTML showing car imag and make etc etc**

 }

This 'works'. As in, if I had 5 Honda Civics, and all of them were reserved on the dates the user had entered, their carIDs would be excluded from the loop. However it would not show the Honda Civic in the list at all, because all of them would be gone.

I need a way to tell if I run out of a certain model of car on a certain date. So I have three Civics, I know three are excluded, so I display one but it is not available to book.

I think the solution is to remove the sub-query. So First I run:

$nonavail = mysql_query("SELECT carID FROM reservations 
                         WHERE startDate = '".$sqlcoldate."'");
while($noavailrow = mysql_fetch_array($nonavail)) { 

This gives me an array of carIDs I need to exclude, then I can grab all cars and sorth them using PHP and my carIDs. But I'm lost now...

Can someone please point me in the correct direction?

Answer 1

It should be possible to do it like this:

SELECT    c.make, c.model, count(c.carid) - count(x.resid) available_cars
FROM      cars c
LEFT JOIN (
   SELECT   r.carid, r.resid
   FROM     reservations r 
   WHERE    ? BETWEEN r.startdate AND r.enddate) x ON x.carid = c.carid
WHERE     c.deleted = 'no'
GROUP BY  c.make, c.model
ORDER BY  c.make, c.model

The inner subquery will select only those reservations with start time before and end time after the given time (indicated by ?). The outer query then selects all the cars and tries to find a reservation that belongs to each car (there can be one or none, but never more than one, because I guess you can't have several reservations for the same car at the same time). Finally the resultset is grouped by make and model and for each the number of cars in total and the number of reservations is calculated and the difference is the number of available cars.

This approach makes use of the fact that COUNT() does only count values that are not NULL.

Answer 2

I'm going to offer up a PHP solution. SQL is probably the better performing option so this is really just to add some variety to the thread.

Here is how I understand the problem. You want to show the cars that are available AND at least one car for each model that is not available. ie:

• Ford Taurus - Available
• Ford Taurus - Available
• Honda Civic - Unavailable
• Hundai Sonata - Avaialble

...

// Keep track of the models so we know which ones we have already processed
$models = array();

// Filter the array of results
array_filter( $results, function ( $result ) use ( &$models )
{
    // If the model has not yet been represented, add the model to the models 
    // array and return true, preserving this item in the array
    if ( !in_array( $result->model, $models )
    {
        $models[] = $result->model;
        return true;
    }

    // If the car is in the excluded list, remove this item from the array
    if ( in_array( $result->car_id, $excluded_car_ids ) )
    {
        return false;
    }

    return true;
});