PHP: a way to sort by loop and exclude certain records after a query

Question

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

I have two tables:

Reservation:

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

Therefore, when a user visits the page, they enter a start date and an end date, after the query is executed, a list of cars is displayed 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 means is to select all cars from the database, but then exclude the carID array, which is the result of the array from the subquery. Other cars, they are grouped by model and display them as follows:

 $getcars = $carstring; while($searchcars = mysql_fetch_array($getcars)) { **some HTML showing car imag and make etc etc** } 

It works". As if I had 5 Honda Civics, and all of them were reserved for user input dates, their carIDs would be excluded from the cycle. However, this would not have shown the Honda Civic on the list at all, because they would all disappear.

I need a way to find out if a particular car model will end on a specific date. So, I have three Civics, I know that three are excluded, so I show one, but it is not available for booking.

I think the solution is to remove the subquery. 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 that I need to exclude, then I can capture all the cars and use them using PHP and my carIDs. But now I'm lost ...

Can anyone point me in the right direction?

Answer 1

It should be possible to do this as follows:

 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 internal subquery will select only these reservations with a start time before and after the end time (indicated by ? ). An external query then selects all the cars and tries to find a reservation belonging to each car (there may be one or none, but no more than one, because, I think, you cannot have several reservations for the same car at the same time) . Finally, the result set is grouped into make and model , and for each of them the number of cars and the number of reservations are calculated, and the difference is the number of cars available.

This approach takes advantage of the fact that COUNT() only counts non- NULL values.

Answer 2

I am going to offer a PHP solution. SQL is probably a more efficient option, so it's actually just adding some variety to the stream.

This is how I understand the problem. You want to show available cars And at least one car for each model that is not available. i.e:

• Ford Taurus - Available
• Ford Taurus - Available
• Honda Civic - Not Available
• 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; }); 

Answer 3

This request checks if there is a start or end date for the reservation during the period when the new customer desires a car. He summarizes the available cars.

 SELECT c.model, ... , SUM( CASE WHEN (r.startDate BETWEEN $desiredstart AND $desiredend) OR (r.endData BETWEEN $desiredstart AND $desiredend) THEN 0 ELSE 1 END) as num_available FROM cars c LEFT JOIN reservations r ON (c.CarID=r.CarID) WHERE r.deleted = 'no' ( SELECT carID FROM reservations WHERE AND ) GROUP BY c.model 

Answer 4

Select your “master list” of models and models, and then click “join” to what's available. This may not be the most optimized syntax, but it is pretty clear:

 select Models.*, Available.* from (select make, model from Cars GROUP BY make, model) as Models LEFT JOIN (SELECT * FROM Cars WHERE CarID NOT IN (1,3,6)) as Available on Models.model = Available.model 

You will notice that I removed the logic that excludes the reserved cars and replaced it with NOT IN for testing purposes. But it’s easy enough for you to plug it back in.

See http://sqlfiddle.com/#!2/266db/12/0

Answer 5

I had a similar problem to solve.

First of all, you should understand that you are looking for a non-reserved car in the database containing your reserved cars ...

You are looking for information that is NOT in your database!
But you can subtract it from what you have.

To simplify the comparison task, you must add the date and time (in the reservation table and in two user criteria - start and end) to the "timestamp" format ... It will be easier for you to compare all your existing reservations to the user's request.
see http://db.apache.org/derby/docs/10.7/ref/rrefsqlj27620.html for format information.

Thus, you should search for your reserved cars using your user data (start and end time stamps) and filter them outside of all possible cars.

UserStart UserEnd
| |
case A | ------ | |
case |
case C |
in particular, D | ------- | --- |
case | | ---------- | Case F | -------- | ------------------ | -------------- - |
| |


You need cases A and E.

This script should detect cases B, C, D, and F ... and put all the CarIDs in an array that I called "NotAvaibleCars".
He forgets cases A and E (past / completed reservations and future reservations).

Then you should check for each car if its identifier is "inArray" before showing it on the results page.

The code:

 $allReservedCars = mysql_query("SELECT * from reservation") or die(mysql_error()); $allCars = mysql_query("SELECT * from cars") or die(mysql_error()); $NotAvailableCars = array(); $userStart = $_POST['yourClientStartCriteria']; $userEnd = $_POST['yourClientEndCriteria']; while ($allCarsCheck = mysql_fetch_array($allReservedCars)){ $TimestampStart = $allCarsCheck['startDate'] . ' ' . $allCarsCheck['startTime']; $TimestampEnd = $allCarsCheck['startDate'] . ' ' .$allCarsCheck['startTime']; if ( (($TimestampStart>$userStart)&&($TimestampStart<$userEnd)) || // if cases C or D (A reservation start already exist for this car between userStart and userEnd) (($TimestampEnd>$userStart)&&($TimestampEnd<$userEnd)) || // if case B or C (A reservation end already exist for this car between userStart and userEnd) (($TimestampStart<$userStart)&&($TimestampEnd>$userEnd)) // if case F (A reservation that wraps userStart and userEnd already exist for this car) ) { array_push($NotAvailableCars, $allCarsCheck['CarID']); // You have now an array of the NOT available cars. } } while($searchcars = mysql_fetch_array($allcars)) { if (inArray($searchcars['CarID'],$NotAvailableCars)){ //Do nothing! } else{ // Your code to show this available car. } } 

;)


Ok ... I read my decision again after 8 hours ...
And I see that I missed the main question: to show something when there is no machine for the make / model combine.

I worked on this ... And I have ... The kind of solution.
But I hope there is a better one ... That's all I can see now.

 $distinctMakeModel = mysql_query("SELECT distinct make, model from cars"); while ($row = mysql_fetch_array($distinctMakeModel)){ array_push($CarModelArray, $row['make'] . ' -splitter- ' . $row['model']) // Array with all make/model combination } $CarQtyArray = array(); for($i=0;$i<$CarModelArray.length;$i++){ $thisCar = explode(' -splitter- ',$carModelArray[$i]); $ThisCarQty = mysql_query("SELECT count(*) from cars where make=$thisCar[0] AND model=$thisCar[1]"); $thiscar = mysql_fetch_array($ThisCarQty); array_push($CarQtyArray, $thiscar[0]; //So now you have an array of quantity with indexes matching the $carModelArray indexes. } 

Now you have an array of cars / model and an array of quantities with corresponding indices.

The following code fragment will create an array of inaccessible quantities with identical matching indices.

 for($i=0;$i<$CarModelArray.length;$i++){ $thisCar = explode(' -splitter- ',$carModelArray[$i]); $NotAvailableCarQtyArray[$i] = 0; for($j=0;$j<$NotAvailableCars.length;$j++){ $thisNotAvailable = mysql_query("Select count(*) where make=$thisCar[$i] AND model=$thisCar[$i] AND carID=$NotAvailableCars[$j]"); $isThisTheModel = mysql_fetch_array($thisNotAvailable); if ($isThisTheModel<>'0'){ $NotAvailableCarQtyArray[$i] ++; } } } 

So now ...
in $ CarModelArray [0], let's say you have a Honda Civic.
in $ CarQtyArray [0] you have 3.
in $ NotAvailableCarQtyArray [0], you have 3 (all gone!).

 for ($i=0;$i<$CarModelArray.length;$i++){ if ($CarQtyArray[$i]==$NotAvailableCarQtyArray[$i]){ echo $CarModelArray[$i] . ' are not available.<br>'; } } 

Here waaayyy is required for many requests!
It shouldn't be a great idea to put an SQL query in a loop.
But, again ... I do not understand how to do this.

This is similar to the number of cars exiting the Start and End search times the number of models you made.

But it should work ...
At least I tried.
;)

Answer 6

 $carstring = mysql_query("SELECT *,(SELECT count(CarID) FROM reservations WHERE reservations.CarID=cars.CarID AND '".$sqlcoldate."' NOT BETWEEN startDate AND endDate ) as availability FROM cars {$statement} AND deleted = 'no' GROUP BY model"); 

The above request will give you a list of cars with an additional field (availability), in which the number of cars of a particular model will be available, if the car is not available, the field will simply show zero.

Answer 7

As I understand it, you want to get a list of available cars and a list of inaccessible car branches grouped by "make" and "mode" (for example, Honda Civid, Ford Escape). Therefore, I think you are right to make 2 requests, but the second request seems to be wrong.

The first request is your first request that inaccessible cars cannot receive.

The second request is to get all cars inaccessible to the "make" and "model" groups. This query looks like this: $nonavail = mysql_query("SELECT carID FROM reservations WHERE startDate = '".$sqlcoldate."' GROUP BY 'make', 'type'");

After receiving 2 arrays, you can manipulate and correctly display them in the layout. Keep in mind that in the first place cars are available, the second is an inaccessible car branch.

Answer 8

Here is a solution for a separate list of cars available and not available for booking. This is very useful for you.

Get a list of available cars for booking. those.). Reservations are available for a specific date range.

 select *,'available' as status from cars where CarID not in(SELECT CarID FROM reservations WHERE UNIX_TIMESTAMP( CONCAT( startDate, ' ', startTime ) ) <= $startTimestamp AND UNIX_TIMESTAMP( CONCAT( endDate, ' ', endTime ) ) >= $endTimestamp)WHERE deleted = 'no' group by make,model order by make,model asc 

Get a list of available cars for booking. those.). already reserved for a specific date range.

 select *,'not available' as status from cars where CarID not in(SELECT CarID FROM reservations WHERE UNIX_TIMESTAMP( CONCAT( startDate, ' ', startTime ) ) <= $startTimestamp AND UNIX_TIMESTAMP( CONCAT( endDate, ' ', endTime ) ) >= $endTimestamp)WHERE deleted = 'no' group by make,model order by make,model asc 

Note: convert the date to a timestamp format.

 $startDateTime="21/04/2013 13:00:00"; //date example list($sDay, $sMonth, $sYear, $sHour, $sMinute,$sSeconds) = split('[/ :]', $startDateTime); $startTimestamp=mktime($sHour, $sMinute,$sSeconds, $sMonth, $sDay, $sYear); $endDateTime="29/04/2013 13:00:00"; //date example list($eDay, $eMonth, $eYear, $eHour, $eMinute,$eSeconds) = split('[/ :]', $endDateTime); $endTimestamp=mktime($eHour, $eMinute,$eSeconds, $eMonth, $eDay, $eYear); 

and the code is below:

 <?php $startDateTime="21/04/2013 13:00:00"; //date example list($sDay, $sMonth, $sYear, $sHour, $sMinute,$sSeconds) = split('[/ :]', $startDateTime); $startTimestamp=mktime($sHour, $sMinute,$sSeconds, $sMonth, $sDay, $sYear); $endDateTime="29/04/2013 13:00:00"; //date example list($eDay, $eMonth, $eYear, $eHour, $eMinute,$eSeconds) = split('[/ :]', $endDateTime); $endTimestamp=mktime($eHour, $eMinute,$eSeconds, $eMonth, $eDay, $eYear); $carstringAvailable = mysql_query("select *,'available' as status from cars WHERE CarID not in( SELECT CarID FROM reservations WHERE UNIX_TIMESTAMP( CONCAT( startDate, ' ', startTime ) ) <= $start_Timestamp AND UNIX_TIMESTAMP( CONCAT( endDate, ' ', endTime ) ) >= $end_Timestamp ) AND deleted = 'no' group by make,model order by make,model asc"); $getcarsAvailable = $carstringAvailable; ?> <h3>Available cars for reservation</h3> <table> <tr> <th>CarID</th><th>make</th><th>model</th><th>serial</th><th>image</th><th>status</th> </tr> <?php if(mysql_num_rows($getcarsAvailable)>0) { while($searchcarsAvail = mysql_fetch_array($getcarsAvailable)) { ?> <tr> <td><?php echo $searchcarsAvail['CarID'];?></td> <td><?php echo $searchcarsAvail['make'];?></td> <td><?php echo $searchcarsAvail['model'];?></td> <td><?php echo $searchcarsAvail['serial'];?></td> <td><?php echo $searchcarsAvail['image'];?></td> <td><?php echo $searchcarsAvail['status'];?></td> </tr> <?php } } else { ?> <tr> <td colspan='5'>No records found</td> </tr> <?php } ?> </table> <?php $carstringUnAvailable = mysql_query("select *,'available' as status from cars WHERE CarID in( SELECT CarID FROM reservations WHERE UNIX_TIMESTAMP( CONCAT( startDate, ' ', startTime ) ) <= $start_Timestamp AND UNIX_TIMESTAMP( CONCAT( endDate, ' ', endTime ) ) >= $end_Timestamp ) AND deleted = 'no' group by make,model order by make,model asc"); $getcarsUnAvailable = $carstringUnAvailable; ?> <h3>Already Reserved cars list</h3> <table> <tr> <th>CarID</th><th>make</th><th>model</th><th>serial</th><th>image</th><th>status</th> </tr> <?php if(mysql_num_rows($getcarsUnAvailable)>0) { while($searchcarsUnAvail = mysql_fetch_array($getcarsUnAvailable)) { ?> <tr> <td><?php echo $searchcarsUnAvail['CarID'];?></td> <td><?php echo $searchcarsUnAvail['make'];?></td> <td><?php echo $searchcarsUnAvail['model'];?></td> <td><?php echo $searchcarsUnAvail['serial'];?></td> <td><?php echo $searchcarsUnAvail['image'];?></td> <td><?php echo $searchcarsUnAvail['status'];?></td> </tr> <?php } } else { ?> <tr> <td colspan='5'>No records found</td> </tr> <?php } ?> </table>