Page 1 of 1

Optimized php executing many sql commands

Posted: Tue Jun 02, 2009 3:51 pm
by wtfbrb
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
?>

Re: Optimized php executing many sql commands

Posted: Tue Jun 02, 2009 4:03 pm
by mikemike
whoa now. You're doing a few queries inside a loop of 7,000 - that's why it's taking so long.

Compact everything into one query and grab everything you need in that. Google for 'mysql JOIN', there will be plenty of documentation on how to do it.

Re: Optimized php executing many sql commands

Posted: Tue Jun 02, 2009 4:04 pm
by kuksenkate
You might get more help by 1. describing your algorithms briefly in words, and/or 2. posting in the "code critique" board.