Include record count in dropdown list

[PeDa]

My MySQL table contains a column Date and various other columns; there are about 5000 records, and Date can have one of about 20 values. I want to make dropdown list containing the various values of Date and the number of records containing this Date: something like

SELECT Date AS ThisDate, (COUNT(*) Date WHERE Date=ThisDate) FROM MyTable

 

[ChrisHirst]

echo() the count into the caption for the option element just as you probably already do for the name of the item.

The code you provided is the SQL query and nothing to do with actually sending the output to the page other than it returns the data you need.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[PeDa]

quote:
"echo() the count into the caption for the option element just as you probably already do for the name of the item.
The code you provided is the SQL query and nothing to do with actually sending the output to the page other than it returns the data you need."

I see now I was unclear. I know how to make the dropdown list as such. I am looking voor valid SQL to fill it (my pseudo-SQL doesn't work)

 

[ChrisHirst]

Aha!
You got a PHP answer because this IS the PHP forum, ... ... For MySQL you need the be in forum436 and we need a clue about the structure of the table(s), because you cannot back-reference a query variable like this;

"WHERE Date=ThisDate)";

in the same query.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

 

[PeDa]

quote:
For MySQL you need the be in forum436: MySQL and we need a clue about the structure of the table(s)

OK; thank you; I will

Peter D

 

[southbeach]

All you'll need to do is use GROUP BY and you should get the query you're looking for.

--
SouthBeach

http://www.fpgroups.com

The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[PeDa]

quote:
All you'll need to do is use GROUP BY and you should get the query you're looking for.

Yes, thank you. I had discovered that, and have now got it werking.