MySQL - Perl: how to get an array of zip codes in represented by "x" miles represented by "zipcode" in Perl example

Question

I found a lot of calculations here, and some php examples and most of them just above my head.

I found this example:

SELECT b.zip_code, b.state,
       (3956 * (2 * ASIN(SQRT(
       POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) +
       COS(a.lat*0.017453293) *
       COS(b.lat*0.017453293) *
       POWER(SIN(((a.lng-b.lng)*0.017453293)/2),2))))) AS distance
FROM zips a, zips b
WHERE
       a.zip_code = '90210' ## I would use the users submitted value
GROUP BY distance
having distance <= 5; ## I would use the users submitted value

But I am having trouble understanding how to execute a query with my database.

Looks like this query has everything I need.

However, I can’t even find / understand what b.zip_code really is! (that b.and zips a, zips b?)

I also do not need statein the request.

The my myQL SQL structure is as follows:

    ZIP | LAT     | LONG
  33416 | 26.6654 | -80.0929

I wrote this in an attempt to return some results (not based on the above query), but it only gives one zip code.

## Just for a test BUT, in reality I desire to SELECT a zip code WHERE ZIP = the users submitted zip code
## not by a submitted lat lon. I left off the $connect var, assume it there.
my $set1 = (26.6654 - 0.20);
my $set2 = (26.6654 + 0.20);
my $set3 = (-80.0929 - 0.143);
my $set4 = (-80.0929 + 0.143);
my $test123 = $connect->prepare(qq{SELECT `ZIP` FROM `POSTAL`
WHERE `LAT` >= ? AND `LAT` <= ? 
AND `LONG` >= ? AND `LONG` <= ?})  or die "$DBI::errstr";
$test123->execute("$set1","$set2","$set3","$set4") or die "$DBI::errstr";
my $cntr;
while(@zip = $test123->fetchrow_array()) {
    print qq~$zip[$cntr]~;
    push(@zips,$zip[$cntr]);
    $cntr++;
}

, , .

, Perl, zip- , DISTANCE . , , . - .

Answer 1

, . ( , ). , . :

my $query = "SELECT b.zip_code,
       (3956 * (2 * ASIN(SQRT(
       POWER(SIN(((a.lat-b.lat)*0.017453293)/2),2) +
       COS(a.lat*0.017453293) *
       COS(b.lat*0.017453293) *
       POWER(SIN(((a.lng-b.lng)*0.017453293)/2),2))))) AS distance
FROM zips a, zips b WHERE
       a.zip_code = ? 
GROUP BY distance having distance <= ?";

my $sth = $dbh->prepare($query);
$sth->execute( $user_submitted_zip, $user_submitted_distance );
while( my ($zip, $distance) = $sth->fetchrow() ) ) {
     # do something
}

, ( 30 . ), . , , ​​ Sphinx, .

Answer 2

, :

/, b.zip_code ! ( "b." "zips a, zips b"?)

, . , - "zips" ( , "zips" "). , , "zips", "zips", - "w "," a "" b".

, "b.xxx" " xxx , SECOND ".

Answer 3

fetchrow_array , , , ( , , ).

while ($test123->fetchrow_array()) , . , while , . - for my $row ($test123->fetchrow_array()) { ..., , .

- , ( ) , , :

my @zips = ();    # for final results
for my $row ($test123->fetchrow_array()) {
    push @zips, $row->[0];
}

or even more briefly with a Perl expression map:

my @zips = map { $_->[0] } $test123->fetchrow_array()

which does the same thing.