create a 'non returning clients' report from SQL data
Posted: Sun Nov 20, 2005 6:51 am
Hi all,
I have a site with customer transaction data and customer data. There's a fair bit of it (data), about 600 clients and about 2000 transactions recorded.
In this situation (my first report so far) I want to create a 'non returning clients' report.
So far, with the code below, things run slowly and it often times out my php execution limit (even when extending it to 90 seconds). The idea for this is, loop through the 'clients' table, then for every clientID, loop through the transactions table, looking for transactions from that client. If the last transaction for that client is older than $DATE then they are classed as a non return client.
Can anyone see why this would be SO Slow? and perhaps, is there a better way? (As you can see here, i'm just echoing some data, i'm not even doing logic on the results and it's SLOOOOW)
ta
Rob
I have a site with customer transaction data and customer data. There's a fair bit of it (data), about 600 clients and about 2000 transactions recorded.
In this situation (my first report so far) I want to create a 'non returning clients' report.
So far, with the code below, things run slowly and it often times out my php execution limit (even when extending it to 90 seconds). The idea for this is, loop through the 'clients' table, then for every clientID, loop through the transactions table, looking for transactions from that client. If the last transaction for that client is older than $DATE then they are classed as a non return client.
Can anyone see why this would be SO Slow? and perhaps, is there a better way? (As you can see here, i'm just echoing some data, i'm not even doing logic on the results and it's SLOOOOW)
ta
Rob
Code: Select all
//Loop thru client database
mysql_select_db($dbname);
$sql = "SELECT * FROM clients ORDER BY id ASC";
$content = mysql_query($sql);
$Xcontent = mysql_fetch_array($content);
$num_rows = mysql_num_rows($content);
if ($num_rows > 0)
{
for ($y=1; $y<=$num_rows; $y++)
{
$id = $Xcontent["id"]; //the clients id
//we will search through the TRANSACTIONS table for each and every client now and extract required data into an array
mysql_select_db($dbname);
$sql2 = "SELECT * FROM transactions WHERE cust_id = '$id' ORDER BY date ASC";
echo "cust_id = $id:<br>";
$content2 = mysql_query($sql2);
$Xcontent2 = mysql_fetch_array($content2);
$num_rows2 = mysql_num_rows($content2);
if ($num_rows2 > 0)
{
for ($z=1; $z<=$num_rows2; $y++)
{
$trans_id = $Xcontent2["id"];
$trans_cust_id = $Xcontent2["cust_id"];
echo "trans_id = $trans_id:<br>";
$Xcontent = mysql_fetch_array($content);
}
}//end if num_rows2 > 0
$Xcontent = mysql_fetch_array($content);
}
}