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: