Coldfusion Search Query

Hello,

I currently have a page setup to search our database and return results. Right now my problem is that I have to type in 1 to get it to return results with people who have a feature set to True in the database. I am trying to find out how to make it so all they would have to do is hit the search button instead of typing in 1 for them to search it.

 

i am confudes by ur words:

 

I can't understand either one of you

 

Post the code.

 

hello, sorry bout the confusion. i was in a rush when i wrote that message - here is the link to the code
http://cfx.pastebin.com/m573f90fa

basically, the current search method is designed to where if there is nothing entered in the search bar, you will need to type in "1" in order for it to return all results. i would like to make it so you don't have to enter anything, hit search and it will return all results. see code for more info. (there are several reports it pulls based on which query you pick from the dropdown)

 

default whereText to 1 so it will return all results if no search criteria is specified.

 

Personally, I avoid using preserveSingleQuotes() whenever possible as it poses a sql injection risk. Also, using cfqueryparam is better for performance. Instead you could you could build the where clause dynamically within the query. Start with a

WHERE 1 = 1

Then append any additional clauses using AND

		<cfquery name="getUsers" datasource="#ds#">
			select clientid, prefix, firstname, lastname, suffix, email, ereport, ereportStartDate, enter_date, update_date, edit_date, phone, rReport, rReportStartDate
			from gaming_clients 
			where 1 = 1
			<cfif searchBy eq "email">
                            and email LIKE <cfqueryparam value="%#searchCriteria#%" cfsqltype="cf_sql_varchar">
                        <cfelseif searchBy eq "ereport">
                             .... Other conditions ... ETC ...
			</cfif>
			order by lastname, firstname 
		</cfquery>

Code (markup):