Databases
StackOverflow2013
Postsdbo
POPHP: A way to sort through a loop and exclude certain entries after the query
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POPHP: A way to sort through a loop and exclude certain entries after the query
    primarykey
    data
    text
    <p>I am creating a system that displays items from a database table, dependant on if they are available. </p> <p>I have two tables:</p> <p>Reservations:</p> <pre><code>---------------------------------------------------------------------- | ResID | CarID | UserID | startTime | startDate | endTime | endDate | ---------------------------------------------------------------------- | 1 | 4 | 4 | 13:00:00 |2013-04-21 |13:00:00 |2013-04-29| </code></pre> <p>And Cars:</p> <pre><code>------------------------------------------- | CarID | make | model | serial | image | ------------------------------------------- | 1 | Honda | civic | ky675 | 1.png | </code></pre> <p>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.</p> <p>So I came up with this query. </p> <pre><code>$carstring = mysql_query("SELECT * FROM cars {$statement} AND deleted = 'no' AND carID NOT IN ( SELECT carID FROM reservations WHERE startDate = '".$sqlcoldate."' ) GROUP BY model"); </code></pre> <p>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:</p> <pre><code>$getcars = $carstring; while($searchcars = mysql_fetch_array($getcars)) { **some HTML showing car imag and make etc etc** } </code></pre> <p>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. </p> <p>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.</p> <p>I think the solution is to remove the sub-query. So First I run:</p> <pre><code>$nonavail = mysql_query("SELECT carID FROM reservations WHERE startDate = '".$sqlcoldate."'"); while($noavailrow = mysql_fetch_array($nonavail)) { </code></pre> <p>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...</p> <p>Can someone please point me in the correct direction?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USdnagirl
    1. USBohdi
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POPHP: A way to sort through a loop and exclude certain entries after the query
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POPHP: A way to sort through a loop and exclude certain entries after the query
      1. USBohdi
      1. VTBountyStart
    3. VO
      singulars
      1. POPHP: A way to sort through a loop and exclude certain entries after the query
      1. USfthiella
      1. VTFavorite
    1. COdo not use `mysql_*` it is old. and we do not like old functions.
      singulars
      1. POPHP: A way to sort through a loop and exclude certain entries after the query
      1. USshnisaka
    2. COI'm not sure what exactly it is what you want your new query to produce. Could you post a sample of the expected output?
      singulars
      1. POPHP: A way to sort through a loop and exclude certain entries after the query
      1. USDaan
    3. COSELECT based on model,make, left join available cars (do check the enddate though), group by model / make, count() available (or 0 if the left join results is NULL). All in 1 query, even your sorting can be done in this query.
      singulars
      1. POPHP: A way to sort through a loop and exclude certain entries after the query
      1. USWrikken
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload