A database was designed with the following tables:
Incidents
_________
id (auto incremented int)
...
Calls
_____
id (auto incremented int)
incidentId (foreign key)
status (varchar 32)
...
The status of the last Call is the status of the related Incident.
Statuses can be "Not started" through various states up to "Completed".
The status column never has the text "Open".
If the last Call for the related Incident is not "Completed", then it
is considered to be "Open".
My task is to getIncidentsWithStatus("Open") using PHP.
The existing inefficient method is in the PHP function
getIncidentsWithStatus($status = "Open"), made worse by mingling with
PHP and then another MySQL query. It first finds
$theHugeListOfIncidentIds of the last Calls OF ALL INCIDENTS, then
uses Calls.id IN ($theHugeListOfIncidentIds) AND Calls.status NOT LIKE
'Completed'. The reason this was done was that if Calls.status NOT
LIKE 'Completed' was used first, then the result would include all
Incidents.
A) What would be an efficient MySQL query with the database in the
present state to getIncidentsWithStatus("Open")?
I can think of two alternatives, which require the database to be modified:
1a) Add a trigger to update a new column named "statusFromCall" in the
Incidents table when the Calls.status is updated.
1b) Add PHP code to update the new column named "statusFromCall" in
the Incidents table when the Calls.status is updated.
2) Then just query for Incidents WHERE statusFromCall NOT LIKE 'Completed'.
B) What would be the MySQL query to create such a trigger in 1a?
Thanks.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
