List Menu values in Query String....

[ClarkKent101]

Hi Everyone,

I am trying to build an advanced search page for a website i'm designing in ColdFusion. I have a few drop down menu's which contain labels and values and what i would like to include in each one of those drop down menu's is an option (which will be initially selected) to send nothing when the form is submitted.

E.g. This option will have the label of 'Select Next ...' but should have no value so that it won't send anything in the query string. I'm sure everyone has come across a drop down list like this in some search form where you have certain options that act as subheadings within the drop down list. Problem is when i don't include a value in an option like that the label gets sent in the query string. How do i prevent this from happening? My objective is to have the 'Select Next ...' option send nothing at all, in other words if that option is selected it must be ignored completely as if it doesn't exist so that the results page can process the rest of the values being sent by the other controls on the search form.

Thanks for your time,

- CK

 

[r937]

go ahead and let it send something

typically, dropdown lists have values like

<option value='KS'>Kansas</option>

or
<option value='3'>Supersize 'em</option>

and in both cases you can use

<option value='0'>Select Next ...</option>

and just test for this while building the query

so, if you do detect a '0' value, then you omit that search criterion from the WHERE clause

SELECT ...
FROM ...
WHERE 1=1
<cfif form.state NEQ '0'>
AND datable.state = '#form.state#'
</cfif>



r937.com | rudy.ca

 

[ClarkKent101]

Hi r937,

Thank you for your response. I had actually already tried that but i keep getting a syntax error. In your <cfif> statement you have 'form.state', the page that gets sent the query doesn't have a form, so if i were to include that on the destination page it would tell me the form.state control is undefined. Instead i had tried this...

SELECT ..
FROM ...
WHERE 1=1
<cfif #URL.state# NEQ '0'>
AND datable.state = '#URL.state#'
</cfif>

But i get an error that states:

"Error Executing Database Query
Syntax Error...."

Any ideas?

Thanks for your help,

- CK

 

[r937]

how is it that the dropdown (which is a form element) gets translated into a parameter in URL scope? you're not using a form with GET are you?

anyhow, regarding your syntax error, i'd need to see the actual query (surely your table isn't called "datable") and also please mention which database you're using

r937.com | rudy.ca

 

[ClarkKent101]

Hi r937,

Sorry i should have included more information in my previous post. I have a two pages, one that has the actual form with the dropdown's and another where the search results gets displayed. The page with the form has 4 controls at the moment, it has two textfields and two dropdowns. The page with the form does use the GET method, at first i tried using the POST method but nothing got displayed on the results page and a query string wasn't displayed, thats why i changed the method to GET.

I am using MySQL, here is some of the form code...

--------------------------------------------------------

<form id="frmAdvSearch" name="frmAdvSearch" method="get" action="/cars/search/advancedresults.cfm">
<table width="348" border="0" align="center" cellspacing="2">
<td><div align="right" class="maintext">New/Used:</div></td>
<td>
<div align="left" class="maintext">
<select name="newused" id="newused">
<option value="New" selected="selected">New</option>
<option value="Used">Used</option>
</select>
</div></td>

--------------------------------------------------------

In the above code i have only included one of the dropdowns so this post doesn't get too long. The following code is the actual query...

--------------------------------------------------------

SELECT ......
FROM ....
WHERE
(car_classifieds.carmodel.carTypeID = car_classifieds.cartype.carTypeID
AND car_classifieds.carmodel.carModelID = car_classifieds.ticket.carModelID)
AND car_classifieds.carmodel.online = 1
AND car_classifieds.carmodel.TypeOfTransport = '#URL.transport#'
AND car_classifieds.carmodel.carNewOrUsed = '#URL.newused#'
AND (car_classifieds.ticket.ticketID = '#URL.ticket#'
OR car_classifieds.carmodel.carModelName LIKE '%#URL.carmodelname#%')

--------------------------------------------------------

The above query contains all the constrictions for the query. Not to stray from the threads problem at hand but the constrictions in the above query isn't working like i would expect it to, the results i get aren't correct. For example, one of my controls on the form i built asks for a ticket ID, if i type in 11 (which exists) it doesn't display the appropriate information for that specific ticket ID. That is another problem besides for the dropdown menu problem i started this thread with, but if you can point out what i'm doing wrong i would really appreciate it .

Thank you for your help,

- CK

 

[r937]

your query looks okay

when developing a query for use in a language like coldfusion, it is very important to test your query outside of that language first

this way you can be sure that it returns what you want it to return, and not get confused by results not displaying properly (which could be the fault of either the query or your display code)

and please, go back to POST

r937.com | rudy.ca

 

[ClarkKent101]

Thank you for the advice I would gladly use POST if i knew how to get it to work ... when i use the POST method there is no query string displayed, i'd have to manually write a query string in the action of the form right? If that is what i have to do, could you give me an example to work off of?

So lets say i have the dropdown menu i previously posted and i want to include that in the query string, would I write the query in this sort of way?...

action="search.cfm?newused=#FORM.newused#" or something to that effect.

Thank you for your help,

- CK

 

[r937]

sorry, i have no idea what you're talking about when you say "i'd have to manually write a query string in the action of the form"

but it sounds like you're using some kind of framework like fusebox, and you might want to check their tutorial

r937.com | rudy.ca

 

[ClarkKent101]

This is my first time creating search functionality for this dynamic application i have created so if i say something confusing please excuse it . What i meant is, when i use POST i don't see a query string in the address bar once i click the submit button. If i have filled out the search form and the method of the form is POST, when i submit, no query string is displayed to me so i assume no query string is sent to the destination page (results page)... that's pretty much why i used GET to begin with because it sends all the form data in a query string.

The only times i have used the POST method would be to add or update something in the database and in those occasions i had to manually write a query string (or dreamweaver would write it for me) in the action for the form i was dealing with. That's why i asked before if i had to manually write a query string if i were to use the POST method because i don't see one being sent to the results page.

I hope this clears things up ,

Thanks,

- CK

 

[r937]

why do you need a query string? with POST, all the form variables are sent as form variables

anyhow, it's your call

the main point of this thread is the CFIF to determine if a dropdown select list form field was set to the "all" option

r937.com | rudy.ca

 

[ClarkKent101]

Ok i see where i was going wrong, i was under the impression that the POST method wasn't sending anything to the results page.... i modified the results page's code so it accepts the form variables and now the dropdown lists work perfectly fine. When the 'Select ...' option is selected, the correct results are displayed.

The only problem i am experiencing now is the textfields. If i for example type something in one of the textfields, incorrect results appear.... E.g., I have two textfields, if i were to enter 9 in the Ticket ID textfield, it returns everything in the recordset, acts almost as if the constriction isn't even there... Here is how the constrictions look now...

-----------------------------------------------------

WHERE (car_classifieds.carmodel.carTypeID = car_classifieds.cartype.carTypeID
AND car_classifieds.carmodel.carModelID = car_classifieds.ticket.carModelID)
AND car_classifieds.carmodel.online = 1
<cfif FORM.transport NEQ '0'>
AND car_classifieds.carmodel.TypeOfTransport = '#FORM.transport#'
</cfif>
<cfif FORM.newused NEQ '0'>
AND car_classifieds.carmodel.carNewOrUsed = '#FORM.newused#'
</cfif>
AND (car_classifieds.ticket.ticketID = '#FORM.ticket#'
OR car_classifieds.carmodel.carModelName LIKE '%#FORM.carmodelname#%')

-----------------------------------------------------

The last constriction that is in parenthesis is where i think lies the problem... It states there i have two textfields, the carmodelname field works fine, it's only the ticket field... whenever i type in a Ticket ID, it returns the Ticket ID i was looking for along with every other Ticket ID in the database; instead of returning the single Ticket ID i searched for (just for the record, when i type in a Ticket ID - nothing is typed into the carmodelname textfield). Is that part of the query incorrect?

Thank you for your help, i highly appreciate it

- CK

 

[r937]

if you type nothing into the carmodelname field, then it will be passed as an empty string

then when it hits your query, the sql becomes ...

OR car_classifieds.carmodel.carModelName LIKE '%%'

which of course is true for every row!

(well, strictly speaking, it isn't true for rows where car_classifieds.carmodel.carModelName is null, but i expect you probably don't have any of those)

so before you incorporate that restriction into the query, you need another CFIF

r937.com | rudy.ca

 

[ClarkKent101]

Hey r937,

Sorry for the late reply, i just wanted to let you know i incorporated a cfif statement around each restriction and the results of the advanced search works perfectly fine.

Thank you for your help ,

- CK

 

[r937]

r937.com | rudy.ca