Re: [PHP-DB] Help to improve MySQL query
On Fri, Aug 8, 2008 at 5:25 PM, Micah Gersten [EMAIL PROTECTED] wrote: How about select Incidents.* from Incidents inner join Calls on Incidents.id=Calls.incidentid where Calls.status='Open'? ... Dee Ayy wrote: ... The status column never has the text Open. ... -- 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
Use an appropriate status. Thank you, Micah Gersten onShore Networks Internal Developer http://www.onshore.com Dee Ayy wrote: On Fri, Aug 8, 2008 at 5:25 PM, Micah Gersten [EMAIL PROTECTED] wrote: How about select Incidents.* from Incidents inner join Calls on Incidents.id=Calls.incidentid where Calls.status='Open'? ... Dee Ayy wrote: ... The status column never has the text Open. ... -- 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
On Sat, Aug 9, 2008 at 1:32 AM, Niel Archer [EMAIL PROTECTED] wrote: 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 With the id (auto incremented int), the last record of either table would be the largest id in a particular group. Do not use NOT LIKE 'Completed', it's an inefficient way of doing != 'Completed' I'll modify that as you said. My real concern is that $theHugeListOfIncidentIds keeps growing and is used in the Calls.id IN ($theHugeListOfIncidentIds). Even if the $theHugeListOfIncidentIds was replaced with an actual MySQL query instead of first being processed by PHP, I think that is a bad approach (but maybe that is the solution if I only change this query and do not change/add columns or states). -- 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
[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
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
