Organizational Research By

Surprising Reserch Topic

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


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?

asked Sep 13, 2013 in MYSQL by rajesh
edited Sep 12, 2013
0 votes
29 views



Related Hot Questions

2 Answers

0 votes
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.
answered Sep 13, 2013 by rajesh
edited Sep 12, 2013
0 votes
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.
answered Sep 13, 2013 by rajesh
edited Sep 12, 2013

...