[PHP-DB] George Tomescu is out of the office.
I will be out of the office starting 09/07/2008 and will not return until 11/08/2008. I will respond to your message when I return. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Help to improve MySQL query
Hi You do not say how you identify the last call (there is no date/time field for example), so a complete answer is not really possible Do not use "NOT LIKE 'Completed'", it's an inefficient way of doing "!= 'Completed'" -- Niel Archer -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Help to improve MySQL query
How about "select Incidents.* from Incidents inner join Calls on
Incidents.id=Calls.incidentid where Calls.status='Open'"?
Thank you,
Micah Gersten
onShore Networks
Internal Developer
http://www.onshore.com
Dee Ayy wrote:
> 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
[PHP-DB] Help to improve MySQL query
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
