Most efficient way to create drop down boxes with item count using MySQL

[dt192]

Hi, I have a database with around 15 columns i have written script that finds each distinct item from a given column and outputs it as a drop down boxe, so 15 drop down boxes in total, the form then gets posted and the input is used to filter the search results, what i would like to know is what is the most efficent way to do this, as i do not feel that the way i am doing it is at all efficent, also i would like to show the amount of items in each distinct catagory in the drop down so the user can see how many results in each catagory, as my explination is not very clear ill give a clear example:

 

if i had a book shop and had columns for hardback_softback & genre my menus would be output as

 

[back Type: All]

[Hardback (100)]

[softback (400)]

 

and

 

[Genre: All]

[biography (94)]

[Crime (244)]

[Horror (14)]

[Romance (148)]

 

but obviously the drop down boxes would be created using the <select> statement

[samshel]

i think u will have to do it one query per field..as u r doing as u can group and find count only one field at a time, if u combine 2 or more fields, it will give u count of combination which is not what u want.

[dt192]

oh thats a shame well would there be any way to speed things up by coming at it from a differnt angle maybe? as i have 8000 records in the database (that is increasing rapidly and will top out at somewhere around the 50,000 mark) and doing the 15 distinct statements on every page load lags noticably

[samshel]

u can run a schedule job which will store the fields and thier counts in a seperate table and u can refresh tht table after say every hour or so...this way u dont have to query ur main table that many times , every time u show ur page.

[dt192]

yea i was thinking that but the problem is new data is added through various scripts via cron every 10 mins, but i suppose i could update the numbers and fields in the script that add the new data, because searching and counting the whole database every 10 mins has got to be a lot better than hundereds of people triggering 15 searches on every page load lol, great thinking samshel your a genius 

[dt192]

I thought about it some more and decided that rathen than using the database to store what is essentially static data i would simply create the complete menu code dynamicly, save it to a advancedmenu.inc and simply include it in my page using include(); but what i was thinking is do different cron job items run one after the other or do they all start at the same time, if they run in sequence then i can just include the code to create the include file at the end of the last script that updates my database, but if they all run together then i will have to include it at the bottom of each one, any ideas?