I am trying to pull available contract IDs. The table ‘shorttermID’ is simply a list of numbers from 2000 to 4000. These are the contact numbers that can be used. The table ‘carpark’ stores the bookings.
I need to see which contract numbers are currently being used and display all the other ones that are available.
There are 3 factors that determine if the contract ID is taken 1. The field ‘CarHere’ == ‘Y’
2. The return date (dc2) is greater than todays date.
3. the departdate (dc1) is less than todays date and the return date is empty.
This is the query I have…
[php]$clientID = new WA_MySQLi_RS(“clientID”,$georgeconnect_i,0);
$clientID->setQuery(“SELECT ID FROM shorttermID WHERE ID NOT IN (SELECT carpark.ShortTermID FROM carpark WHERE (CarHere =‘Y’ OR dc2 > date(‘Y-m-d’) OR (dc1 <= date(‘Y-m-d’) AND (dc2 IS NULL OR dc2=’’)))) ORDER BY ID”);
$clientID->execute();
[/php]
Is there a better way to do it as it takes about 20 seconds to run. I have indexed the fields in the database. There are about 20,000 records in the carpark table.