MySQL & PHP Looping - Listing by State then City

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 Post Reply

2468

pbmods

5,821

Expert 4TB

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

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

Looking to do Android software development, any suggestions? Is flutter better?

by: nemocccc | last post by:

hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

General

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++

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

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++

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

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

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

Career Advice

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