I've been scratching my head for some time with this one.

I have a dynamic dropdown where people can select the area of the country and are then shown a list of records for that area.

I need the dropdown to show the number of records in each area actually in the dropdown, like this:

Essex (9)
Sussex (4)
Dorset (7)
Somerset (0)
etc.

So selecting 'Dorset' the next page will show the 7 records in a repeat region.

How can I achieve this? I'm using PHP4 with MySQL 3.23.

Many thanks,

Jon

Edited by - jstanton on 02 Jun 2004 19:05:15

Edited by - jstanton on 02 Jun 2004 19:06:12

I suggest modifying your SQL in your recordset you use to generate the dropdown so that it returns the city name along with a new column containing the count for that city

<pre id=code><font face=courier size=2 id=code>
SELECT city, COUNT(city) AS city_count
FROM your_table
GROUP BY city
</font id=code></pre id=code>

Now you can dynamically populate the dropdown

Thanks for the info. I can see how this should work but can't get it to! I'm glad it's a query solution rather than lots of coding in loops.

I have two tables, customer details and county names. The customer details has a field which holds the countyid and this is linked to counties so that the county name is shown rather than the id number.

The dropdown has the counties listed and this needs to have the number of people in each county shown.

I'm getting confused using your example query as I can't get my head round which table should be used with the count and group by statements.

Could you maybe give me some more code to make it clearer?

Many thanks for your help with this.

Hi again

Here's my query at the moment:

SELECT counties.CountyID, counties.cCounty, count(events.eCounty) AS county_count
FROM counties, events
WHERE counties.CountyID = events.eCounty
GROUP BY events.eCounty
ORDER BY counties.cCounty ASC

This is showing the number next to each county but is only displaying the counties which have at least one record. I need to show all the counties and put (0) next to those that don't have any records.

Hmmmm.......

Try:

SELECT counties.cCounty, count(events.eCounty) AS county_count
FROM events INNER JOIN counties
ON events.eCounty = counties.CountyID
GROUP BY events.eCounty

This assumes that events.eCounty is the same datatype as counties.CountyID (like the primary key)

Thanks for that. It's looking better but still displays only the counties which have entries in the events table.

I've not used INNER JOIN before so can't work out why it's not working.

Change it to

SELECT counties.cCounty, count(events.eCounty) AS county_count
FROM events RIGHT JOIN counties
ON events.eCounty = counties.CountyID
GROUP BY events.eCounty

see: dev.mysql.com/doc/mysql/en/JOIN.html

Edited by - wdglide on 03 Jun 2004 14:09:01

Hi again

So very nearly there! Now I get the first county showing (0) and then only those with records showing after that. I've had a look at the link you've given and my head is spinning!

Got it! After much trying and head scratching I've got the query to work. Here it is:

SELECT counties.CountyID, counties.cCounty, count(events.eCounty) AS county_count
FROM counties LEFT JOIN events ON counties.CountyID=events.eCounty
GROUP BY counties.CountyID

Couldn't have done this without your help so many, many thanks for that.

One last thing, I need the number to show only if a certain field meets a certain criteria, like this:

WHERE events.eStartDate &gt; '$rightnow'
($rightnow stores the mktime() timestamp)

If I put this in I just get the earlier problem of only the records with something stored showing. I need this to only show events that haven't yet started. The resulting page works fine but the number in the dropdown shows all records regardless.

Thanks again for your help, very much appreciated.