Need help on nested loop of queries in php and mysql?
- by mysqllearner
Hi,
I am trying to get do this:
<?php
$good_customer = 0;
$q = mysql_query("SELECT user FROM users WHERE activated = '1'"); // this gives me about 40k users
while($r = mysql_fetch_assoc($q)){
$money_spent = 0;
$user = $r['user'];
// Do queries on another 20 tables
for($i = 1; $i<=20 ; $i++){
$tbl_name = 'data' . $i;
$q2 = mysql_query("SELECT money_spent FROM $tbl_name WHERE user = '{$user}'");
while($r2 = mysql_fetch_assoc($q2)){
$money_spend += $r2['money_spent'];
}
if($money_spend > 1000000){
$good_customer += 1;
}
}
}
This is just an example. I am testing on localhost, for single user, it returns very fast. But when I try 1000, it takes forever, not even mentioned 40k users.
Anyway to optimise/improve this code?
EDIT:
By the way, each of the others 20 tables has ~20 - 40k records
