Page 1 of 1

Optimized php executing many sql commands

Posted: Tue Jun 02, 2009 10:51 pm
by wtfbrb
pickle | Please use [ code=php ], [ code=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.


Ok, this code works, but it take hours to execute on a small list of about 3k. When it worked on a list of 10 I started to code it to take a generic table and id...but that stopped when I realized how inefficient the code was. Any advise on how I can make this run faster would be appreciated.

Code: Select all

<head>
<title>Congressional Poker Results</title>
</head>
 
<body>
<form name="search" method="post" action="append-cd.php">
<input name="tableName" type="text" size="10" />
<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" /> 
</form>
</body>
</html>
<?
//RETRIEVE TABLE TO UPDATE
$count = 0;
$connection     = @mysql_connect("localhost", "root", "***") or die(mysql_error());
$db_name        = "test";
$tableName  = $_POST['tableName'];
$upDB           = @mysql_select_db($db_name, $connection) or die(mysql_error());
$getTabQuery    = "Select myID,zip5,plus4 FROM my_zips WHERE CD = '0' ORDER BY zip5,plus4 LIMIT 7000";
$updateTable    = @mysql_query($getTabQuery,$connection) or die(mysql_error());
 
//GET ALL INFO FROM TABLE TO UPDATE
while ($row = mysql_fetch_array($updateTable)) {
    $upID       = $row['myID'];
    $zip5       = $row['zip5'];
    $plus4      = $row['plus4'];
    $searching  = $_POST['searching'];
  
  //RETRIEVE CD TABLE NAME
  $tabQuery     = "SELECT tabName FROM cim_cap_range WHERE LEFT($zip5, 3) BETWEEN lowZip AND highZip";
  $tresults     = @mysql_query($tabQuery,$connection) or die(mysql_error());
  $trow         = mysql_fetch_array($tresults);
  $tname        = $trow['tabName'];
  if (strlen($tname) > 0) {
      if (strlen($zip5) == 5) {
        $distIDQuery    = "SELECT * FROM $tname WHERE LEFT(zip,5)='$zip5' "; 
      }
      //CHECKS FOR PLUS 4 AND RETRIEVES districtIDs
      if (strlen($plus4) == 4) {
        $distIDQuery .= " AND RIGHT(zip,4)='$plus4';";
      }
      
      //GET DISTRICT FROM DISTRICT ID
      $distIDTable = @mysql_query($distIDQuery,$connection) or die(mysql_error());
      while ($row = mysql_fetch_array($distIDTable)) {
          $distID = $row['districtID'];
          $distQuery = "Select dist FROM cim_cap_dist where distID = $distID ";
          
          //GET CONGRESSIONAL DISTRICTS AND APPEND THEM TO TABLE
          $distTable = @mysql_query($distQuery,$connection) or die(mysql_error());
          while ($row = mysql_fetch_array($distTable)) {
            $cd             = $row['dist'];
            $updateQuery    = "UPDATE IGNORE my_zips set CD = $cd WHERE myID = $upID";
            $appDists       = @mysql_query($updateQuery,$connection) or die(mysql_error());
            $count += 1;
          }
      }
  }
}
echo $count
?>

pickle | Please use [ code=php ], [ code=text ], etc tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: :arrow: Posting Code in the Forums to learn how to do it too.

Re: Optimized php executing many sql commands

Posted: Wed Jun 03, 2009 9:55 am
by pickle
Well, you're re-using the variable $row multiple times - that could be dangerous.

I'd recommend doing as few queries in a loop as possible. For example, rather than retrieving the district every loop, retrieve all the districts at one time, then put them in an array. Then, in your while loop, look up the district in the array, rather than doing a new database query.