Optimized php executing many sql commands

Coding Critique is the place to post source code for peer review by other members of DevNetwork. Any kind of code can be posted. Code posted does not have to be limited to PHP. All members are invited to contribute constructive criticism with the goal of improving the code. Posted code should include some background information about it and what areas you specifically would like help with.

Popular code excerpts may be moved to "Code Snippets" by the moderators.

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 »

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.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: Optimized php executing many sql commands

Post 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.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply