Ordering Zipcodes By Nearest

[jakebur01]

How can I order this query of zip codes within the radius by nearest?

  /**
     * Get Zipcode By Radius
     * @param  string $zip    US zip code
     * @param  ing $radius    Radius in miles
     * @return array         List of nearby zipcodes
     */
    protected function get_zipcodes_by_radius ( $zip, $radius )
    {
        $sql = 'SELECT distinct(zip) FROM zip_codes WHERE (3958*3.1415926*sqrt((latitude-'.$zip->latitude.')*(latitude-'.$zip->latitude.') + cos(latitude/57.29578)*cos('.$zip->latitude.'/57.29578)*(longitude-'.$zip->longitude.')*(longitude-'.$zip->longitude.'))/180) <= '.$radius.';';
        $zip_codes = array();
        if ( $result = $this->db->query( $sql ) )
        {
            while( $row = $result->fetch_object() )
            {
                array_push( $zip_codes, $row->zip );
            }
            $result->close();
        }
        return $zip_codes;
    }

 

[gw1500se]

I do something similar and use the MapQuest API. You have to sign up for a key but it is free.

[kicken]

Move your distance calculation into the SELECT list so it's a field and remove the WHERE clause.  Then wrap your query in an outer query and apply your WHERE and ORDER BY clauses to that query.  While your at it switch to a prepared query with parameters rather than concatenation to prevent and potential user input problems.  Looks like maybe your using mysqli so I showed that option below.

$sql = '
    SELECT DISTINCT zip
    FROM (
        SELECT 
            zip
            (3958*3.1415926*sqrt((latitude-?)*(latitude-?) + cos(latitude/57.29578)*cos(?/57.29578)*(longitude-?)*(longitude-?))/180) as distance
        FROM zip_codes
    ) r
    WHERE
        r.distance < ?
    ORDER BY
        r.distance
';

$stmt = $this->db->prepare($sql);
$stmt->bind_param('dddddd', $zip->latitude, $zip->latitude, $zip->latitude, $zip->longitude, $zip->longitude, $radius);
$stmt->bind_result($rowZip);
$stmt->execute();

$zip_codes = [];
while ($stmt->fetch()){
    $zip_codes[] = $rowZip;
}


return $zip_codes;

 

[jakebur01]

4 hours ago, kicken said:

Move your distance calculation into the SELECT list so it's a field and remove the WHERE clause.  Then wrap your query in an outer query and apply your WHERE and ORDER BY clauses to that query.  While your at it switch to a prepared query with parameters rather than concatenation to prevent and potential user input problems.  Looks like maybe your using mysqli so I showed that option below.

Fatal error: Uncaught Error: Call to a member function bind_param() on boolean

Edited February 24, 2020 by jakebur01

[chhorn]

then your query is invalid, enable error reporting

$pdo = new PDO('mysql:host=localhost;dbname=someTable', 'username', 'password', array(
  PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
));

[Barand]

... or as you are using mysqli, put this line before your connection creation

mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT);

 

[jakebur01]

Fatal error: Uncaught mysqli_sql_exception: FUNCTION imsafe.zip does not exist in /home/zipclass.php:99 Stack trace: #0 /home/zipclass.php(99): mysqli->prepare('\n SELECT...') #1 /home/zipclass.php(34): ZipCodeUtility->get_zipcodes_by_radius(Object(stdClass), 100) #2 /home/TOOL_zipcodetest.php(45): ZipCodeUtility->search_radius(71075, 100) #3 {main} thrown in /home/zipclass.php on line 99

imsafe is the name of the database

the table is zip_codes

Edited February 24, 2020 by jakebur01

[chhorn]

that code

imsafe.zip

is nowhere mentioned on this thread.

Edited February 24, 2020 by chhorn

[jakebur01]

1 hour ago, chhorn said:

that code

imsafe.zip

is nowhere mentioned on this thread.

What is it looking for?  Is it thinking that is a function, or looking for a database table?

[kicken]

I missed a comma between zip and the distance calculation in the inner select query.  As a result it's parsing it as a function call instead of as separate columns.

Add a comma between zip and ( in the inner select.

[jakebur01]

Hey, that listed them.  Although, they are still not listed by nearest.  They are all random.

[gw1500se]

Post your current code.

[jakebur01]

$sql = '
    SELECT DISTINCT zip
    FROM (
        SELECT 
            zip,
            (3958*3.1415926*sqrt((latitude-?)*(latitude-?) + cos(latitude/57.29578)*cos(?/57.29578)*(longitude-?)*(longitude-?))/180) as distance
        FROM zip_codes
    ) r
    WHERE
        r.distance < ?
    ORDER BY
        r.distance
';

$stmt = $this->db->prepare($sql);
$stmt->bind_param('dddddd', $zip->latitude, $zip->latitude, $zip->latitude, $zip->longitude, $zip->longitude, $radius);
$stmt->bind_result($rowZip);
$stmt->execute();

$zip_codes = [];
while ($stmt->fetch()){
    $zip_codes[] = $rowZip;
}


return $zip_codes;

 

[gw1500se]

This is really a MySQL question. I'm not a MySQL expert so I don't know where latitude and longitude come from. I assume they are columns in your data base. I've never used a calculated value for ORDER BY rather than a column value. In my scenario I don't store that in my database. I just pass the zip codes to the MapQuest API and get the distance when I need it. If it were me I'd put the zip codes in an array, then add the distances to that array from the API and sort the array on the distances.

[jakebur01]

Thanks, but I’d rather solve this and pull from my own database.

[jakebur01]

7 hours ago, kicken said:

I missed a comma between zip and the distance calculation in the inner select query.  As a result it's parsing it as a function call instead of as separate columns.

Add a comma between zip and ( in the inner select.

Hey Kicken,  it’s pulling the zip codes now but not sorting by distance.

[jakebur01]

2 hours ago, jakebur01 said:

Hey Kicken,  it’s pulling the zip codes now but not sorting by distance.

Disregard that. The array of zip codes is in the correct order.  It's my IN() in the mysql query I'm using to pull the users within those zip codes that throwing it out of order.

  $sql = "SELECT *
              FROM `users`
              WHERE `ZIPCODE` IN($List)";

I changed it to:

SELECT *
              FROM `users`
              WHERE `ZIPCODE` IN($List)
              ORDER BY FIELD(ZIPCODE, $List)

It's working now!  Thanks Kicken and to everyone else that helped.

Edited February 25, 2020 by jakebur01

[kicken]

Rather than taking your list of zip codes and doing another query, you could just join the users table in your original query.  It would make things easier and more efficient.

SELECT u.*
FROM users u
INNER JOIN (
    SELECT 
        zip,
        (3958*3.1415926*sqrt((latitude-?)*(latitude-?) + cos(latitude/57.29578)*cos(?/57.29578)*(longitude-?)*(longitude-?))/180) as distance
    FROM zip_codes
) zipDistance ON zipDistance.zip = u.ZIPCODE
WHERE
    zipDistance.distance < ?
ORDER BY
    zipDistance.distance

If your $zip variable comes from another query that looks up a user-supplied zip code you could handle that with a join also and simplify your parameters.

$sql = '
SELECT 
    r.userId,
    r.firstName,
    r.lastName
FROM (
    SELECT
        u.userId,
        u.firstName,
        u.lastName,
        (3958*3.1415926*sqrt((userZip.latitude-targetZip.latitude)*(userZip.latitude-targetZip.latitude) + cos(userZip.latitude/57.29578)*cos(targetZip.latitude/57.29578)*(userZip.longitude-targetZip.longitude)*(userZip.longitude-targetZip.longitude))/180) as distance
    FROM users u
    INNER JOIN zip_codes targetZip ON targetZip.zip = ?
    INNER JOIN zip_codes userZip ON userZip.zip=u.ZIPCODE
) r
WHERE
    r.distance < ?
ORDER BY
    r.distance
';

$zip = '12345'; //User input
$radius = 30;

$stmt = $this->db->prepare($sql);
$stmt->bind_param('sd', $zip, $radius);
$stmt->bind_result($userId, $firstName, $lastName);
$stmt->execute();

$userList = [];
while ($stmt->fetch()){
    $userList[] = [$userId, $firstName, $lastName];
}