Organizational Research By

Surprising Reserch Topic questions - 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:


| 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
                            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

Related Hot Questions

Your answer

Your name to display (optional):
Privacy: Your email address will only be used for sending these notifications.
Anti-spam verification:
To avoid this verification in future, please log in or register.