Drop down list and recordcount

[shawntbanks]

I am try to figure out how to develop a dropdown list that is generated from a table in SQL, and then in the dropdown list get it to list how many listing are in another table with with the ID of the menu items. Example:
Cars (45 listings)
Trucks (10 listings)
vans (6 listings)

 

[ecobb]

This FAQ tells you how to dynamically populate a drop down box. What will be displayed in the box depends on how your query is written.

faq232-1158

Show us the query and maybe we can set up to get your desired results. Most likely you will just need to use COUNT and join the tables.

Hope This Helps!

Ecobb
- I hate computers!

 

[CidMatrix]

OK, you got two tables. I'm assuming that are properly linked.

Your SQL should look something like this:

<CFQUERY NAME=&quot;getAutoCount&quot;>
SELECT
Tbl_Auto.Desc AS AutoType, Tbl_Auto.AutoID, '( ' & (Count(Tbl_Models.AutoID)) & ' listings)' AS CountDesc

FROM
Tbl_Auto INNER JOIN Tbl_Models ON Tbl_Auto.AutoID = Tbl_Models.AutoID
GROUP BY Tbl_Auto.Desc
</CFQUERY>

Once you have that, just create a select and have the options populate from this query, like this:
<SELECT Name=&quot;ChAutoType&quot;>
<CFOUTPUT QUERY=&quot;getAutoType&quot;>
<OPTION VALUE = &quot;#AutoID#&quot;>#AutoType#</OPTION>
</CFOUTPUT>
</SELECT>

In a nutshell that should be it.

 

[r937]

CidMatrix, cute trick putting the parentheses into the SELECT list, however, in order to match the output...

<OPTION VALUE = &quot;#AutoID#&quot;>#AutoType#</OPTION>

the query should be

[tt]select Tbl_Auto.AutoID
, Tbl_Auto.Desc
|| '( '
|| Count(Tbl_Models.AutoID)
|| ' listings)' as AutoType[/tt]

note this uses the standard sql double pipes as the concatenation operator, since shawntbanks neglected to mention which database this is for


rudy

http://r937.com/

 

[shawntbanks]

Here is my code, I am new at this. I realize now that I should have given this to you at first. I really appriciate the help


<cfquery datasource=&quot;market&quot; name=&quot;getcars&quot;>
select subcategory
from adds
Where subcategoryid = 1
</cfquery>
<cfquery datasource=&quot;market&quot; name=&quot;gettrucks&quot;>
select subcategory
from adds
Where subcategoryid = 2
</cfquery>
<cfset caradds = getcars.recordcount>

<cfset truckadds = gettrucks.recordcount>