Count DISTINCT multiple mysql rows query

[whynot]

I insert data in mysql table row using multiple method : 1#2#3 . 1 is ID of country, 2 is Id of state, 3 is ID of town . now i have this table for real estate listings. for each list(home) i have country/state/town (1#2#3). in country table i have list of country - in country table i have list of state - in country table i have list of town. i need to The number of houses in country / state / town . my mean is :

USA [ 13 ] <!-- This Is equal of alabama+alaska+arizona -->
----Alabama [8] <!-- This Is equal of Adamsville+Addison+Akron -->
-------Adamsville [2]
-------Addison[5]
-------Akron[1] 
......(list of other City)
----Alaska [ 3 ]
-------Avondale[3]
......(list of other City)
----Arizona [ 2 ]
-------College[2]
......(list of other City)

 

Lisintg Table :

 

ID -- NAME -- LOCATION -- DATEJOIN -- ACTIVE
1 -- TEST -- 1#2#3    -- 20110101 -- 1
2 -- TEST1 -- 1#2#3    -- 20110101 -- 1
3 -- TEST2 -- 1#3#5    -- 20110101 -- 1
4 -- TEST3 -- 1#7#6    -- 20110101 -- 1

 

Country Table :

 

 id       -- name  
1        -- USA  

 

stats Table :

 

id        -- countryid -- name  
1        -- 1             -- albama
2        -- 1             -- alaska
3        -- 1             -- akron

 

town Table :

id        -- countryid -- statsid -- name  
1        -- 1             -- 1         -- adamsville
2        -- 1             -- 1         -- addison 
3        -- 1             -- 1         -- akron

 

Thanks For Any Help.

 

[whynot]

any help

[ManiacDan]

You have a column that's actually called "location" and contains data like "12#42#139"?  If so, (a) you did it wrong and (b) you'll never get your counts unless you load the entire table into memory in PHP and chunk through it, counting them by hand.

 

Your table should have had a city, state, and country COLUMN (3 columns, not 1), so you could easily do this in a single query.

[whynot]

You have a column that's actually called "location" and contains data like "12#42#139"?  If so, (a) you did it wrong and (b) you'll never get your counts unless you load the entire table into memory in PHP and chunk through it, counting them by hand.

 

Your table should have had a city, state, and country COLUMN (3 columns, not 1), so you could easily do this in a single query.

 

you right. so Now, I Change This To :

 

ID -- NAME -- country -- stats -- town -- DATEJOIN -- ACTIVE
1 -- TEST   -- 1     -- 2        -- 3       -- 20110101 -- 1
2 -- TEST1 -- 1     -- 2        -- 3       -- 20110101 -- 1
3 -- TEST2 -- 1     -- 3        -- 5       -- 20110101 -- 1
4 -- TEST3 -- 1     -- 7        -- 6       -- 20110101 -- 1

 

Now, How To Count And print list of country/stats/city.

 

Thanks

[ManiacDan]

Wow, that was quick.

 

So now you have the tables:

Listing

---------------

name

country

state

town

datejoin

active

 

 

country

------------

id

name

 

 

state

------------

id

name

 

 

 

town

--------------

id

name

 

 

And you would like a list of countries, states, and cities in your database with their total number of records for each, right?

 

That will be (something like):

SELECT
  COUNT(country.name) countryCount,
  country.name countryName,
  COUNT(state.name) stateCount,
  state.name stateName,
  COUNT(town.name) townCount,
  town.name townName
FROM 
  listing
JOIN country ON listing.country = country.id
JOIN state ON listing.state = state.id
JOIN town ON listing.town = town.id
GROUP BY country.name, state.name, town.name WITH ROLLUP
ORDER BY country.name, state.name, town.name

 

The phrase "with rollup" means that you will get "grand totals" in your table.  You will have to loop through this data set, which will include rows in which you  will see NULL for the town and a value for state.  That is the grand total for that state.  Then if you see a row with NULL for town and state, that's the grand total for the country.

 

If the results are confusing, let me know.

[whynot]

Thanks ManiacDan. Really, the results are confusing. I'm confused. i have database Prefix (my_ ) for mysql tables. Which tables need to have a prefix(my_ )? how to print list ? while loop?  For greater clarity