<be gentle i'm the sensitive type 😉 >
This is long with a lot of probably useless background information, but I want to be as thorough as possible. 🙂
I've got a problem with some code timing out while parsing a large (and growing) table. This table is part of a larger database that is used in a customer relation management package. This particular table is basically a 3 column table of edits being performed to tickets in the crm package. Management wants a report of users doing certain functions that are recorded into this table (the functions being 'create ticket', 'appended ticket' and 'close ticket'). The database designers did not put an index on the table (speed issue number 1).
What I have dreamt up with (remember me = newbie) is a form with users names/ids that gets passed to a php script that parses the table and creates totals based on how many edits the user has performed. The problem I'm facing now is if more than 5 users are selected the script times out before execution is complete.
Here is the sloppy code I mentioned:
//$uid is array passed from multi select form from another page in the form we're only interested in part of the array element
$num=count($uid);
$oqry = "Created:";
$cqry = "Closed";
$eqry = "Appended";
for ($i=0;$i<$num;$i++) {
$name = substr($uid[$i],0,10);
$cquery = "SELECT DISTINCT mrID FROM MASTER1_HISTORY WHERE mrHISTORY like '%$name%$cqry%' and mrHISTORY like '$yy-$mm-$dd%'";
$oquery = "SELECT DISTINCT mrID FROM MASTER1_HISTORY WHERE mrHISTORY like '%$name% $oqry%' and mrHISTORY like '$yy-$mm-$dd%'";
$equery = "SELECT mrID FROM MASTER1_HISTORY WHERE mrHISTORY like '%$name%$eqry%' and mrHISTORY like '$yy-$mm-$dd%' and mrHISTORY NOT LIKE '%Closed Appended%'";
$crows = mssql_num_rows(mssql_query("$cquery",$link));
$orows = mssql_num_rows(mssql_query("$oquery",$link));
$erows = mssql_num_rows(mssql_query("$equery",$link));
<stuff about printing totals rrelevant to question removed>
}
Like I said this works for 1 to 5 users. What happens is the script goes through and calculates everything and then prints. My question is how can I have it parse the query, display the result, and then continue (hopefully avoiding the timeout issue by having it query, print, then continue instead of query all at once, then print)?
Any tips,suggestions would be greatly appreciated!
Eric
