Query runs on Enterprise manager but not in PHP

Lurk

I have the following query, which is accessing an MSSQL database. When I run the query in Enterprise Manager, it runs perfectly in less than 5 seconds. However when I run it under PHP it exceeds the 30 second execution time and states that it stops on the line which only contains:

mssql_query($sql_out);

$sql_out = "SELECT Account.Acc_Reference, Account.Acc_Description, Acc_Template.AT_Description, Journal_Item.JI_Amount, Account_Balances.AB_Actual, Account_Balances.AB_Budget, Account_Balances.AB_On_Order
FROM Account Account INNER JOIN
     Account_Balances Account_Balances ON Account.Acc_Id = Account_Balances.Acc_Id INNER JOIN
     Acc_Template Acc_Template ON Account.AT_Id = Acc_Template.AT_Id INNER JOIN
     Journal_Item Journal_Item ON Journal_Item.Acc_Id = Account.Acc_Id
WHERE
     (Account_Balances.FY_Id = '$fyid') AND (Acc_Template.AT_Description IN ('Expenses', 'Capital works in progress', 'Assets'))";

Thanks

killerbishop

Use:

set_time_limit(false);

This should let you run indefinitely. If this is for a webpage and the result set is fairly large, consider designing a paging system where you see 10 entries per page or so using LIMIT clauses.

Lurk

Aha, I just thought about it and the problem may be in another area... Here is the next bit of the code(with $sql_out being the query above)..what it does is take data from several tables in an MSSQL DB and puts it in one table in a MySQL DB. The code worked fine before I changed the query to link a 4th table in the MSSQL query....



$query = mssql_query($sql_out);


//loop to get each row from mssql 

$i = 1;


while ($line = mssql_fetch_array($query)) {


//insert current finesse data into mysql 

	$acc_ref = $line["Acc_Reference"]; 
	$acc_des = $line["Acc_Description"]; 
	$at_des = $line["AT_Description"];
	$ab_abf = $line["JI_Amount"];
	$ab_bud = $line["AB_Budget"]; 
	$ab_act = $line["AB_Actual"]; 
	$ab_ono = $line["AB_On_Order"];


	$sql_in = "INSERT INTO $mytablename (account_id,Acc_Reference,Acc_Description,AT_Description,JI_Amount,AB_Budget,AB_Actual,AB_On_Order) VALUES ('$i','$acc_ref','$acc_des','$at_des','$ab_abf','$ab_bud','$ab_act','$ab_ono')";

	mysql_query($sql_in); 


	$i++;

  } //end loop

Thanks for the reply

Lurk

bump

Any takers?