MySQL & PHP Looping - Listing by State then City

2 New Member

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

Jul 17 '07 #1

Subscribe Reply

2470

pbmods

5,821

Recognized Expert Expert

Heya, SuperHeroGeek. Welcome to TSDN!

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

Expand|Select|Wrap|Line Numbers

 
$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:

Expand|Select|Wrap|Line Numbers

 
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:

Expand|Select|Wrap|Line Numbers

 
$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>");

}

Jul 18 '07 #2

SuperHeroGeek

New Member

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

Jul 18 '07 #3

Similar topics

php MySql form

by: iwasinnihon | last post by:

I don't usually like to do this, but I need help. I have gone over this code and cannot figure out why it doesn't work. First of all it doesn't check to see if you have filled in the required...

PHP

"Unwind" a Database & create a Flat Table

by: Max | last post by:

Hello everyone, and thank you in advance for all of your help! I have a tblPrint table in Access 2002. Its structure is as follows: tblPrint ------------------ Name, Text Address1, Text...

Microsoft Access / VBA

how to in mysql/php

by: Jim Whitaker | last post by:

Expand full for viewing... I have a parent/child table. The basic setup is as follows: The parent table is called load sheet. It has fields such as Tripno, carrier, loaddate, etc. The child...

MySQL Database

problem with returning all results from search query MYSQL & PHP

by: thepresidentis | last post by:

here is my problem, i have a website that compiles concert listings for users to search for shows in their local area, i ran into a problem with returning search results due to an improper format...

PHP

problem with returning all search results MYSQL PHP

by: thepresidentis | last post by:

here is my problem, i have a website that compiles concert listings for users to search for shows in their local area, i ran into a problem with returning search results due to an improper format...

MySQL Database

Insert into MySQL Database with PHP POST Method

by: ctrap | last post by:

Hi Everyone, Here's my situation: I'm making a website for an employment agency, there is a page where potential employees can visit and "pre-register". When the submit button is hit the...

PHP

Retrieve Array Data through List/Menu box using Mysql query

by: TechnoAtif | last post by:

Hi ALL I have entered some array values using checkboxes into mysql database through a form. Next iam creating a searchpage where all those cateogories inserted through checkboxes has to be...

PHP

Tricky Javascript Looping

by: npm | last post by:

I've got an XML list of radio stations that I want to display, but I think it involves some nested FOR loops that I can't quite figure out. Here's a sample of my XML: <stations> <state...

Javascript

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

C / C++

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...

Windows Server

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new...

Microsoft Access / VBA

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...

C# / C Sharp

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...

C# / C Sharp