Need help with a recordset

[Shane930]

I need some help with a recordset that selects a series of items with options. The problem is when I select the item it shows a list of options. Some of the option lists have five some have

[elDonrico]

your message seems to be truncated. whats is it again?

[Shane930]

I need some help with a recordset that selects a series of vehicle makes that
then show models when a make is selected. The problem is when I select the
make it shows a list of models like it should, some of the make lists have five
models and some have ten or more. What I need help with is how to limit the
list to just the models and not include the "null" values. This makes my drop
down list have large empty spaces at the end of the options listed. The query
is as follows:

SELECT *
FROM saa.vehicle_make
WHERE make = '#FORM.select_make#'

Question # 2:

What can I add to this recordset so that I can search on just the make alone
without the model included. The submit includes both of the make and model
lists so that makes it tough to break up.

Here is the recordset:

SELECT make
FROM saa.vehicle_make
ORDER BY make ASC

Thanks

Shane

[Shane930]

Sorry I reposted, thanks

[elDonrico]

so youre saying that you have something like ... jeep wrangler... and you want to know how to just look for jeep??

[Dan Bracuk]

Sounds like you want to have related selects. Google those words, it's been
discussed frequently. There is a custom tag that some people use (I'm not one
of them). Maybe someone can tell you how to find it.

[elDonrico]

SELECT make
FROM saa.vehicle_make
WHERE make='#FORM.select_make#'
ORDER BY make ASC

that isnt working for you for Question#2?

[elDonrico]

this is the one i have used in the past
<a target=_blank class=ftalternatingbarlinklarge href="http://www.javascriptkit.com/script/cut183.shtml">http://www.javascriptkit.com/script/cut183.shtml</a>

[Shane930]

Sorry I guess I wasn't being clear. The lists work fine with the present
recordsets but I was trying to clean them up just a little. I am using a table
in mysql instead of an array for ease of maintenance due to the high number of
makes and models I am working with.

For question number one I was looking for sql that allows me to only show the
models in the table for each make no matter how many are in the row. Currently
when I use the existing recordset I get a long list of blank spaces as it is
returning the "null" fields also. I would like it to just show the models and
stop at the first null field.

For the second question I wanted to be able to submit the form with just the
make listed if someone wanted to see all Hondas lets say. Not I have to select
the make also so it limits the return.

Thanks for all of the input

Shane

Here is the code I am using for the two dependent select boxes:

<p align="center">Vehicle Search by Make/Model</p>

<!--- store the selected make variable after the first select boxes submits
itself --->

<cfif isDefined('form.select_make')>
<cfset page.select_make = form.select_make>
</cfif>
<cfoutput>
<form name="DropDown" method="post">

<!--- query DB for the first drop down list --->
<cfquery name="get_make" datasource="saa">
SELECT make FROM saa.vehicle_make ORDER BY make ASC
</cfquery>
<!--- first drop down list --->
<!--- NOTICE the onChange javascript event in the select tag, this is what
submits the form after the first selection --->


<p align="center">Model:<select name="select_make" required="yes"
onchange="this.form.submit()">
<option>Select Make</option>
<!--- dynamically populate the first drop down list based on the get_make
query --->
<cfloop query="get_make">
<option value="#make#" <cfif isDefined('form.select_make')><cfif
form.select_make eq "#make#">selected</cfif></cfif>>#make#</option>
</cfloop>
</select></p>

<!--- if the first selection has been made, display the second drop
down list with the appropriate results --->
<cfif isDefined('form.select_make')>
<!--- query DB for second drop down list, based on the selected item from
the first list --->
<cfquery name="get_model" datasource="saa">
SELECT * FROM saa.vehicle_make WHERE make = '#FORM.select_make#'
</cfquery>
<!--- second drop down list --->

</cfif>
</form>
</cfoutput>
<cfoutput>
<form action="buyerModelSearchresults.cfm" method="POST">

<p align="center">Model: <select name="model" required="yes">
<option>Select Model</option>
<!--- dynamically populate the second drop down list based on the
get_make query --->
<cfloop query="get_model">
<option value="#model1#">#model1#</option>
<option value="#model2#">#model2#</option>
<option value="#model3#">#model3#</option>
<option value="#model4#">#model4#</option>
<option value="#model5#">#model5#</option>
<option value="#model6#">#model6#</option>
<option value="#model7#">#model7#</option>
<option value="#model8#">#model8#</option>
<option value="#model9#">#model9#</option>
<option value="#model10#">#model10#</option>
<option value="#model11#">#model11#</option>
<option value="#model12#">#model12#</option>
<option value="#model13#">#model13#</option>
<option value="#model14#">#model14#</option>
<option value="#model15#">#model15#</option>
<option value="#model16#">#model16#</option>
<option value="#model17#">#model17#</option>
<option value="#model18#">#model18#</option>
<option value="#model19#">#model19#</option>
<option value="#model20#">#model20#</option>
<option value="#model21#">#model21#</option>
<option value="#model22#">#model22#</option>
<option value="#model23#">#model23#</option>
<option value="#model24#">#model24#</option>
<option value="#model25#">#model25#</option>
</cfloop>

</select>
</p>
<p align="center">Search by Make/Model:</p>
<p align="center"><input type="submit" name="Submit" value="Submit"></p>
</form>
</cfoutput>

[paross1]

Please don't tell me that you have 25 model columns in your vehicle_make
table.... that would be a classic denormalized database, and the root of your
problem. If this is the case, you should migrate the model columns to a table
of its own, then create an associative entity (link table) between make and
model to resolve this many to many relationship. Once this is done, all that
needs to be done to find out what models go with which makes would be to join
all three tables and select the models for a particular make where they link in
the make-model entity (table). This method would also allow you to dynamically
add more models without having to add columns to the vehicle_make table. It is
much easier to maintain the database with DML than with DDL.

Phil