Gossamer Forum: General: Databases and SQL: Urgent Help Needed

I have a plugin for Links SQL that works fine on my 2 installs, but
I am getting reports that others are receiving the following error.

Could someone please take a look and let me know if they can see the problem?
It says syntax error. Do I need to be quoting some of the values?

Failed to execute query: ' SELECT DISTINCT o.*, Distance FROM lsql_Links o INNER JOIN ( SELECT z.ZipCode as CenterZip, a.ZipCode as OuterZip, (3957 * (2 * ASIN(SQRT( POWER(SIN(((z.Latitude-a.Latitude)*0.017453293)/2),2) + COS(z.Latitude*0.017453293) * COS(a.Latitude*0.017453293) * POWER(SIN(((z.Longitude-a.Longitude)*0.017453293)/2),2) )))) AS Distance FROM lsql_ZipCodes z, lsql_ZipCodes a WHERE z.ZipCode = 94607 AND (3957 * (2 * ASIN(SQRT( POWER(SIN(((z.Latitude-a.Latitude)*0.017453293)/2),2) + COS(z.Latitude*0.017453293) * COS(a.Latitude*0.017453293) * POWER(SIN(((z.Longitude-a.Longitude)*0.017453293)/2),2) )))) <= 400 ) as CalculatedTable ON o.Zipcode = CalculatedTable.OuterZip ': You have an error in your SQL syntax near '( SELECT z.ZipCode as CenterZip, a.ZipCode as OuterZip, (3957 * (2 * ASIN' at line 4

The error cuts off just like you see above. Again it works on my 2 installs, so I am confused.

Code:
         my $sort_order = 'ORDER BY Distance '; 

         my $code = " 
         SELECT DISTINCT o.*, Distance 
         FROM ".$DB->prefix."Links o INNER JOIN 
         ( 
             SELECT z.ZipCode as CenterZip, a.ZipCode as OuterZip, 
                 (".$earth_radius." * (2 * ASIN(SQRT( 
                 POWER(SIN(((z.Latitude-a.Latitude)*0.017453293)/2),2) + 
                 COS(z.Latitude*0.017453293) * 
                 COS(a.Latitude*0.017453293) * 
                 POWER(SIN(((z.Longitude-a.Longitude)*0.017453293)/2),2) 
                 )))) AS Distance 
             FROM ".$DB->prefix."ZipCodes z, ".$DB->prefix."ZipCodes a 
             WHERE z.ZipCode = ".$query." AND 
                 (".$earth_radius." * (2 * ASIN(SQRT( 
                 POWER(SIN(((z.Latitude-a.Latitude)*0.017453293)/2),2) + 
                 COS(z.Latitude*0.017453293) * 
                 COS(a.Latitude*0.017453293) * 
                 POWER(SIN(((z.Longitude-a.Longitude)*0.017453293)/2),2) 
                 )))) <= ".$dist." 
         ) as CalculatedTable 
         ON o.".$opts->{'links_zipcode_col'}." = CalculatedTable.OuterZip SORT_ORDER 
         "; 

         $code =~ s/SORT_ORDER/$sort_order/; 

         my $offset   = ($args->{nh} - 1) * $args->{mh}; 
         my $sth = $links_db->prepare($code) || return { error => $GT::SQL::error }; 
 #        $sth->execute ($query, $dist) || return { error => $GT::SQL::error }; 
         $sth->execute () || return { error => $GT::SQL::error };

Thank you,
Chris
RGB World, Inc. - Software & Web Development.
rgbworld.com

Urgent Help Needed - Failed to execute query

Last edited by: