mysql 'SELECT' question... please help

i have a db with all U.S states, counties, cities. On a state level page i want to display the 15 counties that have the MOST cities in them. How would I do this?

SELECT counties FROM $table WHERE state='$state' GROUP BY counties...I can't figure out the rest


Maybe the HAVING clause? I don't really know how to properly use that.

Your help is appreciated

 

Look into the COUNT() function of mysql.

 

Yep you need to use the count method to see first add up each city in every country and then you'll be abl to display by most cities

 

Let's say you have your country_table has an unique id, country_name and state_ocurrence columns
Let's say that you have your state_table has an unique state_id, country_id and state_name columns

COUNTRY_TABLE

country_id
country_name
state_ocurrence


STATE_TABLE

state_id
country_id
state_name

$sql_country = "SELECT country_id, country_name FROM country_table";
$result_country = mysql_query($sql_country);

$row_country = mysql_fetch_assoc($result_country);

$total_row_country = mysql_num_rows($result_country);

foreach($row_country['country_id'] as $countryID){


$sql_state = "SELECT * FROM state_table WHERE country_id= $countryID ";
$result_state = mysql_query($sql_state);

$row_state = mysql_fetch_assoc($result_state);

$total_row_state = mysql_num_rows($result_state);

// The line bellow updates your country_table how many states has each country
UPDATE country_table SET state_ocurrence = $total_row_state where country_id = $countryID ;


// uncomment the line bellow if you want to print it.
//echo " Country Name: ".$row_country['country_name']." State Ocurrence: ".$total_row_state."<br>";
}

// NOW you can use your query to display the 15 counties that have the MOST cities in them

SELECT counties FROM $country_table ORDER BY state_ocurrence DESC LIMIT 15

Try this out. I made it right now for you.
Let me know if it works or if you have any question that I could help.


Sincerely...
Mike30

 

For the cities just repeat the process

 

That looks great but my table has already been setup for some time. My table has the following columns:

City
abbr
County
Zip Code

Here is an example:

abbr county city Zip
-------|----------------|----------|--------
CA Orange_County Irvine 92602

 

SELECT counties, count(*) FROM $table WHERE state='$state' GROUP BY counties ORDER BY 2 DESC LIMIT 15

should work...

 

K, that looked good and i tried that and got this error:

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource on line 13

Here is what code looks like:

<?php

   $abbr = $_GET['state'];


include 'config.php';
include 'opendb.php';


$query  = "SELECT county, countyurl count(*) FROM demographic WHERE abbr='$abbr' GROUP BY countyurl ORDER BY 2 DESC LIMIT 15";
$result = mysql_query($query);

while(list($county,$countyurl)= mysql_fetch_row($result))
{
    echo "<a href=\"/county/$abbr-$countyurl.html\">$county $wording</a> | ";
}

include 'closedb.php';
?>

PHP:

 

Nevermind, duplicate post wrong area.

 

That's not what I have written

This should work:

SELECT county, count(*) AS countyurl FROM demographic WHERE abbr='$abbr' GROUP BY countyurl ORDER BY 2 DESC LIMIT 15

PHP:

 

That works GREAT for listing top 15 counties! One problem, the links got screwed up. Where I 'echo' the link structure... instead of $countyurl it is showing the count of the cities. For example:

/county/WA-116.html

instead of:

/county/WA-King.html

I REALLY appreciate your help!

 

If the countyurl is the same in each line of the same county, this should work:

SELECT county, countryurl, count(*) AS counter FROM demographic WHERE abbr='$abbr' GROUP BY county, countyurl ORDER BY 2 DESC LIMIT 15

PHP:

 

This is what I get from that:

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource on line 13

 

Sorry, I had a typing error.

SELECT county, countyurl, count(*) AS counter FROM demographic WHERE abbr='$abbr' GROUP BY county, countyurl ORDER BY 2 DESC LIMIT 15

PHP:

 

It lists 15, but not by number of cities within the county. Here is the code I have now (count(*) AS counter?):

<?php

   $abbr = $_GET['state'];


include 'config.php';
include 'opendb.php';


$query  = "SELECT county, countyurl, count(*) AS counter FROM demographic WHERE abbr='$abbr' GROUP BY county, countyurl ORDER BY 2 DESC LIMIT 15";
$result = mysql_query($query);

while(list($county,$countyurl)= mysql_fetch_row($result))
{
    echo "<a href=\"/county/$abbr-$countyurl.html\">$county $wording</a> | ";
}

include 'closedb.php';

?>

PHP:

 

Sorry, again not thought to the end:

SELECT county, countyurl, count(*) AS counter FROM demographic WHERE abbr='$abbr' GROUP BY county, countyurl ORDER BY 3 DESC LIMIT 15

PHP:

We are getting closer

 

That's exactly what is shown in my previous post... doesn't work right as I stated above.

We are getting closer!

 

I'm getting old
Please see my edit in the last post.

 

THANKS! That's awesome!

 

SELECT county FROM table_name WHERE county='county_name' ORDER BY (SELECT COUNT(cities) FROM table_name WHERE county='count_name') DESC LIMIT 0,15

PHP:

Just a single SQL would work I suppose.

try it and let me know please