title: Need help on nested loop of queries in php and mysql?
body: <p>I am trying to get do this:</p>
  
  <pre><code>&lt;?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&lt;=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 &gt; 1000000){
          $good_customer += 1;
        }
      }
    }
  </code></pre>
  
  <p>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. </p>
  
  <p>Anyway to optimise/improve this code?</p>
  
  <p>EDIT:
  By the way, each of the others 20 tables has ~20 - 40k records</p>
  
  <p>EDIT2:</p>
  
  <p>Okay, drop the "money spend" idea. This is my current structures:</p>
  
  <p>user table => user is PK</p>
  
  <p>logs_week_1 table => user is FK.</p>
  
  <p>logs_week_2 table => user is FK</p>
  
  <p>logs_week_3 table => user is FK</p>
  
  <p>... will have more logs tables in future.</p>
  
  <p>I want to find the "average time" they spend on my site which the time stored in each of the logs tables.</p>
  
  <p>So you guys were saying, storing the logs weekly is a bad idea? I should merge into one table?</p>