Changeset 197

Timestamp:

Oct 26, 2006, 10:43:45 PM (14 years ago)

Author:

Nick Burch

Message:

Finish script, seems to work

File:

• trunk/npemap.org.uk/cgi/get-distant-postcodes.fcgi (modified) (3 diffs)

trunk/npemap.org.uk/cgi/get-distant-postcodes.fcgi

@@ -60 +60 @@
         }
 
-        # Tidy up from another run
+        # Tidy up from another run, if required
         my $checksql = "SELECT tablename FROM pg_tables WHERE tablename = 'averages'";
-        my $check = $dbh->prepare($checksql);
-        $check->execute;
-        if($check->fetchrow_array) {
-                $dbh->do("DROP TABLE averages");
+        my @has_table = $dbh->selectrow_array($checksql);
+        if(@has_table) {
+                $dbh->do("DROP TABLE averages;");
         }
 
         # Get our sql
         # First calculate the average
-        my $prepsql = "CREATE TABLE averages (pcpart VARCHAR(10),avg_easting INTEGER, avg_northing INTEGER);";
-        $prepsql .= "SELECT $pcpart AS pcpart, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing INTO averages FROM postcodes GROUP BY $pcgroup ";
+        my $prepsql .= "SELECT $pcpart AS pcpart, AVG(easting) AS avg_easting, AVG(northing) AS avg_northing INTO TEMP TABLE averages FROM postcodes GROUP BY $pcgroup ";
 
         # Then join to find problem ones
@@ -85 +83 @@
         # Do the query
     my $sth = $dbh->prepare($sql);
-        print $sql."\n";
-    
     if ($sth->execute()) {
         print "Content-type: text/javascript\n\n";
@@ -103 +99 @@
 
         # Tidy up
-        $check->execute;
-        if($check->fetchrow_array) {
+        @has_table = $dbh->selectrow_array($checksql);
+        if(@has_table) {
                 $dbh->do("DROP TABLE averages");
         }