Optimized php executing many sql commands

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
wtfbrb
Forum Newbie
Posts: 2
Joined: Tue Jun 02, 2009 3:40 pm

Optimized php executing many sql commands

Post 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
?>
User avatar
mikemike
Forum Contributor
Posts: 355
Joined: Sun May 24, 2009 5:37 pm
Location: Chester, UK

Re: Optimized php executing many sql commands

Post 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.
kuksenkate
Forum Newbie
Posts: 5
Joined: Mon Jun 01, 2009 12:44 pm

Re: Optimized php executing many sql commands

Post by kuksenkate »

You might get more help by 1. describing your algorithms briefly in words, and/or 2. posting in the "code critique" board.
Post Reply