Need help with counting records and creating an array

[Jeffro]

Alright.. I have enough trouble creating arrays but add in another layer and I really get confused.  Could someone help me figure this one out? 

 

Let's say I have a mysql db with a few hundred urls that all look like so:

 

http://atlanta.website1.com/topic1
http://atlanta.website1.com/topic2
http://atlanta.website2.com/topic1
http://florida.website1.com/topic1
http://florida.website1.com/topic2
http://florida.website1.com/topic3
http://florida.website1.com/topic4
http://florida.website2.com/topic1

 

Notice the state names are different and the websites. 

 

Now I want to:

1) Create the mysql query to do a total count on each state (regardless of website 1 or 2), so the result would lool like this:

atlanta (3)

florida (5)

 

 

2) Be able to create an array in my php code where I could at any time later in the page write a statement such as:

We show that florida has <? echo myflorida_count; ?> records.  

 

If anyone could shed some light, I would be eternally grateful. 

 

Thanks!

[kickstart]

Hi

 

Probably not efficient but this will do it:-

 

SELECT SUBSTR(someurl,8,INSTR(someurl,'.')-, COUNT(*)
FROM Places
GROUP BY SUBSTR(someurl,8,INSTR(someurl,'.')-

 

All the best

 

Keith

[Jeffro]

Hi

 

Probably not efficient but this will do it:-

 

SELECT SUBSTR(someurl,8,INSTR(someurl,'.')-, COUNT(*)
FROM Places
GROUP BY SUBSTR(someurl,8,INSTR(someurl,'.')-

 

All the best

 

Keith

 

Worked beautifully to create the count.  Thank you, Keith!