[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL Query Array of Counties & Cities

Posted on 2012-08-21
10
Medium Priority
?
743 Views
Last Modified: 2012-09-14
I've got a database table of zip codes and am grouping zip codes into regions. So I have two tables, one with all of the zip code information and one with the region id and assigned zip code.

I'd like the system to do the following with only one trip to the database. I need the results to display the entire list of zip codes not yet assigned to a region, sorted by county.

So the results will look like this -

County A
12344 - 12345 - 12345 - 12347

County B
22344 - 22345 - 22345 - 22347

Select Distinct County, zipcode from the zip code table where the zip code is not in the regions table.

I'd then like to store these results in an array that I can go through to output the results.

Currently I'm pulling the distinct county, outputting in a foreach loop, and then running a new query to pull the zipcode and for each county.

Is it possible to do this with only one trip to the database? I'm running MySQL and PHP.
0
Comment
Question by:inspirebiz
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 25

Expert Comment

by:lwadwell
ID: 38318692
Assuming your table/column names:

SELECT DISTINCT country, zipcode
FROM zipcode_table
WHERE zipcode NOT IN (SELECT zipcode FROM regions_table)

or

SELECT DISTINCT country, zipcode
FROM zipcode_table zt
WHERE NOT EXISTS (SELECT 1 FROM regions_table rt WHERE zt.zipcode = rt.zipcode)

or

SELECT DISTINCT zt.country, zt.zipcode
FROM zipcode_table zt
LEFT JOIN regions_table rt ON zt.zipcode = rt.zipcode
WHERE rt.zipcode IS NULL


Different methods and performance - same functionality.
0
 

Author Comment

by:inspirebiz
ID: 38318698
Thank you, now how would I get the data output so that I can loop through all of the zipcodes for each distinct county? I'm guessing there needs to be a check in the loop to see when the county changes?
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38318714
Yes, look for a change in country will work.  You will need an "ORDER BY country" to ensure the order they are read.

Me, I would load into a 2-dimensional table where the 1st dimension is the country and the 2nd is the zipcode.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:inspirebiz
ID: 38318717
Can you show me the code for the 2 dimensional table? I'm not quite sure I understand that part. Thank you.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 38318733
something like this (note: I use mysqli)

$sql = <<<ENDOFSQL
SELECT DISTINCT country, zipcode
FROM zipcode_table
WHERE zipcode NOT IN (SELECT zipcode FROM regions_table)
ENDOFSQL;

$res = $mysqli->query($sql);
$zip_array = array();
while ( $row = $res->fetch_assoc() ) {
    $country = $row['country'];
    $zip_array[$country][] = $row['zipcode'];
}
print_r($zip_array);

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 38319006
This may not be the answer you're looking for, but you might want to consider these factors:

A ZIP code is not a location, it is a postal-carrier's route; it is something that people can remember, but it does not designate a location and different ZIP code data bases will report different locations.
A county may have many ZIP codes
A ZIP code may span multiple counties
A "region" is not a term of art
If you just use the first three digits of a ZIP code, you can get very good proximal information for many application.

It's not clear to me what your application is all about, but if you can tell us in layman's terms what you want to achieve, we may be able to help.  The level of precision we would need would be something like, "I want to find the nearest Starbucks" or something like that.
0
 

Author Comment

by:inspirebiz
ID: 38369381
@lwadwell - sorry it took so long to get back. Is it possible to create the array as part of the Query? I'd like to just run the query and have the following array available after only once call to the database. It looks like the fetch_assoc you suggested is going to make multiple calls - is that correct?

Array[0](county=>countyname, zipcodes=>Array([0]=>123, [1]=>234, [2]=>345, etc))
Array[1](county=>newcounty, zipcodes=>Array([0]=>555, [1]=>666, [2]=>777, etc))

Then I could do something like this

foreach(county as countyname){
   foreach(zipcodes as zip){}
}

Let me know if I'm not explaining this correctly. Thanks
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38369681
you could check out this kind of query:
SELECT country, group_concat(distinct zipcode separator ',' ) zipcodes
FROM zipcode_table zt
WHERE NOT EXISTS (SELECT 1 FROM regions_table rt WHERE zt.zipcode = rt.zipcode)
group by country

Open in new window

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 1500 total points
ID: 38370545
>> Is it possible to create the array as part of the Query?
Not that I am aware of.  @angelIII's suggestion above would return a delimited string that would need to be 'explode'd into an array.

>> It looks like the fetch_assoc you suggested is going to make multiple calls - is that correct?
Yes ... and even if it was possible to return arrays, you would still need multiple calls.  Why does this bother you?

>> Then I could do something like this
You can the way it was (with some slight changes) ...
$sql = <<<ENDOFSQL
SELECT DISTINCT country, zipcode
FROM zipcode_table
WHERE zipcode NOT IN (SELECT zipcode FROM regions_table)
ENDOFSQL;

$res = $mysqli->query($sql);
$county = array();
while ( $row = $res->fetch_assoc() ) {
    $name = $row['county'];
    $county[$name][] = $row['zipcode'];
}
foreach( $county as $countyname => $zipcodes ){
   foreach( $zipcodes as $zip ){}
}

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 38371964
do the following with only one trip to the database
@inspirebiz: Is this an academic assignment?  If you can tell us a little more about what you're doing -- from the 50,000 foot view of the topic -- maybe we can offer some helpful resources.  It's not as if the data had never been aggregated before!

There are only about 42,000 ZIP codes in the USA, and about 9,000 of these are probably irrelevant to your work because they are not associated with any US state - they are things like APO/FPO, etc.  There are a few more than 3000 counties (I am assuming you mean to use "counties" instead of "countries").  How long could it take to run 42,000 queries?  Since the result is static data, optimizing the queries is something of an academic errand with little value in the resulting data set.  Once built it will stay built.

If you understand the ZIP code system you will find that some of this work has already been done for you.  And the US Census already has this information.  You can call your US Representative's office for help if the Census web site and its public representatives are not helpful.  Your taxes have already paid to build this data base.  You can get this information directly from the Census on this page:
http://quickfacts.census.gov/cgi-bin/qfd/lookup

See http://www.laprbass.com/RAY_temp_inspirebiz.php for a way to scrape the data from the Census web site.

Some additional information here:
http://en.wikipedia.org/wiki/ZIP_code
http://en.wikipedia.org/wiki/County_%28United_States%29

Scrape code follows:
<?php // RAY_temp_inspirebiz.php
error_reporting(E_ALL);

// DEMONSTRATE HOW TO USE CURL POST TO CALL THE CENSUS.GOV WEB SERVICE

function curl_post($url, $post_array=array(), $timeout=2, $error_report=TRUE)
{
    // PREPARE THE POST STRING
    $post_string = NULL;
    foreach ($post_array as $key => $val)
    {
        $post_string .= $key . '=' . urlencode($val) . '&';
    }
    $post_string = rtrim($post_string, '&');

    // PREPARE THE CURL CALL
    $curl = curl_init();
    curl_setopt( $curl, CURLOPT_URL,            $url         );
    curl_setopt( $curl, CURLOPT_HEADER,         FALSE        );
    curl_setopt( $curl, CURLOPT_POST,           TRUE         );
    curl_setopt( $curl, CURLOPT_POSTFIELDS,     $post_string );
    curl_setopt( $curl, CURLOPT_TIMEOUT,        $timeout     );
    curl_setopt( $curl, CURLOPT_RETURNTRANSFER, TRUE         );

    // EXECUTE THE CURL CALL
    $htm = curl_exec($curl);
    $err = curl_errno($curl);
    $inf = curl_getinfo($curl);

    // ON FAILURE
    if (!$htm)
    {
        // PROCESS ERRORS HERE
        if ($error_report)
        {
            echo "CURL FAIL: $url TIMEOUT=$timeout, CURL_ERRNO=$err";
            echo "<pre>\n";
            var_dump($inf);
            echo "</pre>\n";
        }
        curl_close($curl);
        return FALSE;
    }

    // ON SUCCESS
    curl_close($curl);
    return $htm;
}

// SET THE URL
$url = "http://quickfacts.census.gov/cgi-bin/qfd/lookup";

// THIS IS A CONSTANT FOR THE CENSUS POST REQUEST
$args["state"] = '00000';

// SET THE 33,000 ZIP CODES
$places = array
( '22101'
, '21218'
, '35804'
, '94087'
)
;

// USAGE EXAMPLE CREATES ASSOCIATIVE ARRAY OF KEY=>VALUE PAIRS
foreach ($places as $zip)
{
    $args["place"] = $zip;

    // STALL FOR A RESPECTFUL MOMENT
    usleep(100000);

    // CALL CURL TO POST THE DATA
    $htm = curl_post($url, $args, 3, TRUE);

    // SHOW WHAT CAME BACK, IF ANYTHING
    if ($htm)
    {
        $arr = explode('<span CLASS="TEN">', $htm);
        $arr = explode('</span>', $arr[1]);
        $res = trim(strip_tags($arr[0]));
        echo "<br/>$zip IS LOCATED IN $res";
    }
    else
    {
        echo "NO RESPONSE FROM $url";
    }
}

Open in new window

HTH, ~Ray
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month19 days, 18 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question