MySQL & PHP Looping - Listing by State then City

[SuperHeroGeek]

Hello.. I work for a newspaper that has several newsstand listings for all different cities and states. Currently, they are uploaded manually by copying and pasting. I would like to make the whole process dynamic, but I'm not quite sure on how to write the MySQL query or how to use loops in PHP to achieve the following:

My database contains the following fields:
1) Newsstand
2) Street Address
3) City
4) State
5) Zip Code

I would like to list the Newsstands by State then by City:
OHIO

ANDOVER
LOIS & BOB'S NEWSROOM INC. - 62 PUBLIC SQUARE
APPLE CREEK
MAYSVILLE HARNESS SHOP - 8572 MOUNT HOPE ROAD
PAM & BILL'S APPLE CREEK MARKET - 60 E. MAIN STREET

ASHLAND
BAILEY LAKE GENERAL STORE - 910 N. MAIN STREET
CIRCLE K - 411 MAIN STREET
HAWLEY'S C-STORE - 1923 STATE ROUTE 60


NEW YORK

ALIQUIPPA
HYSONGS MARATHON - 953 STATE ROUTE 18
PDQ MART - 1203 GRINGO ROAD

ALBION
ALBION MILL - 73 CANAL STREET

I hope this makes sense... Thanks

[pbmods]

Heya, SuperHeroGeek. Welcome to TSDN!

Ok. So you've got a query that looks something like this:

$sql = '
SELECT
        `newsstand`,
        `address`,
        `city`,
        `state`,
        `zip`
    FROM
        `newsstands`
    ORDER BY
        `state` ASC,
        `city` ASC,
        `address` ASC';
 

Now when you execute that query, you get results that might look something like this:

newsstand        address            city        state        zip
-------------    ------------    ---------    --------    ---
Albion Mil...    73 Canal ...    Albion        New York    some zip
Hysong's M...    953 State...    Aliquippa    New York    yeah
Lois & Bob...    62 Public...    Andover        Ohio        hm...
Maysville ...    8572 Moun...    Andover        Ohio        zerp
 

And so on. Notice that as you go through, each state is grouped together.

How do you know you're in a different state? When the current row's state is different than the last row's.

So as you traverse through your results:

$pCity =
$pState =
null;
 
while($row = mysql_fetch_assoc($result))
{
    if($row['state'] != $pState)
    {
        //    Output state header.
        print("<h2>{$row['state']}</h2>");
        $pState = $row['state'];
    }
 
    if($row['city'] != $pCity)
    {
        //    Output city header.
        print("<h3>{$row['city']}</h3>");
        $pCity = $row['city'];
    }
 
    //    Output row data
    print("<span>{$row['newsstand']} {$row['address']}</span>");
}
 

[SuperHeroGeek]

Thanks for the quick and very detailed response. I will implement this tomorrow and see how it goes.