mysql - Trying to compare a rowvalue to form value while in a query using cfif and performing the query -
i know, title sounds confusing literally couldn't think of way word it. anyway, here's mean
<cfquery name="search_all" datasource="contactdata"> select distinct people.first_name, people.last_name, state_lkp.state, zip_lkp.zip, number_lkp.phone_number, email_lkp.email, country_lkp.country, address_lkp.address, city_lkp.city, count(distinct email_lkp.email_id) mail_count, count(distinct number_lkp.phone_number_id) number_count, people.people_id people left join state_lkp on state_lkp.people_id = people.people_id left join zip_lkp on zip_lkp.people_id = people.people_id left join number_lkp on number_lkp.people_id = people.people_id left join email_lkp on email_lkp.people_id = people.people_id left join country_lkp on country_lkp.people_id = people.people_id left join city_lkp on city_lkp.people_id = people.people_id left join address_lkp on address_lkp.people_id = people.people_id <cfif lcase(people.last_name) eq lcase(form.search_bar)> people.last_name ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) <cfelseif lcase(people.first_name) eq lcase(form.search_bar)> people.first_name ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) <cfelseif lcase(address_lkp.address) eq lcase(form.search_bar)> address_lkp.address ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) <cfelseif lcase(coutnry_lkp.country) eq lcase(form.search_bar)> country_lkp.country ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) <cfelseif lcase(city_lkp.city) eq lcase(form.search_bar)> city_lkp.city ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) <cfelseif lcase(state_lkp.state) eq lcase(form.search_bar)> state_lkp.state ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) <cfelseif lcase(zip_lkp.zip) eq lcase(form.search_bar)> zip_lkp.zip ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) <cfelseif lcase(email_lkp.email) eq lcase(form.search_bar)> email_lkp.email ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) <cfelseif lcase(number_lkp.phone_number) eq lcase(form.search_bar)> number_lkp.phone_number ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) </cfif> group people.people_id desc; </cfquery> see cfif/ cfelseif statements there? when try compare people.last_name form.search_bar error. because coldfusion doesn't recognize variable because technically hasn't been created in scope of coldfusion , still mysql code waiting executed.
now know there other ways of doing (like making html select drop down , have user select want search for. i'm curious see if i'm doing here possible solution haven't thought of.
pic of error message: 
the error itself
element last_name undefined in people.
if need clarify something, please let me know. taking time @ question.
edit: note had query this:
<cfquery name="search_all" datasource="contactdata"> select distinct people.first_name, people.last_name, state_lkp.state, zip_lkp.zip, number_lkp.phone_number, email_lkp.email, country_lkp.country, address_lkp.address, city_lkp.city, count(distinct email_lkp.email_id) mail_count, count(distinct number_lkp.phone_number_id) number_count, people.people_id people left join state_lkp on state_lkp.people_id = people.people_id left join zip_lkp on zip_lkp.people_id = people.people_id left join number_lkp on number_lkp.people_id = people.people_id left join email_lkp on email_lkp.people_id = people.people_id left join country_lkp on country_lkp.people_id = people.people_id left join city_lkp on city_lkp.people_id = people.people_id left join address_lkp on address_lkp.people_id = people.people_id people.last_name ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) or people.first_name ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) or address_lkp.address ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) or country_lkp.country ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) or city_lkp.city ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) or state_lkp.state ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) or zip_lkp.zip ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) or email_lkp.email ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) or number_lkp.phone_number ltrim(rtrim(<cfqueryparam value="%#form.search_bar#%" cfsqltype="cf_sql_varchar" maxlength="500">)) group people.people_id desc; </cfquery> you think work right? didn't work in manner expected. say, example, have 10 entries in db, 8 have country of 'merica' , 2 have country of 'nippon'. user enters 'merica' in field , expects on 'merica' results. instead result entries, including 'nippon' ones.
here, screenshot:
the text search results:
first name last name address city zip country state mail count number count test subject 123 imperfect rd huge 12546 usa tx 1 1 yelp pley 616 symmetry rd kinikinik 15051 usa co 1 1 son goku 560 ave none none nippon none 1 1 addias 8998 beat st breakin 12478 usa ny 1 1 gotta yolo 123 once st. miami 04211 usa fl 1 1 what comes down is. if search merica results. want whole word match of merica results, not results contain merica in combination not in country. likewise, if search 'test subject', expect person test subject come up. not test subject , bob because bob has test somewhere in records.
edit: best create self contained environment create git necessary files backed functionality. hosting provider not have coldfusion support , can't install there because lack root access via terminal. hell, can't sudo install coldfusion on it. anyway, here's link git. sql file there import db schema create same 1 i'm using application.
https://github.com/vinceomega/contact-manager
thanks attempted help.
i think you're looking for:
<cfquery name="search_all"> select distinct people.first_name, people.last_name, state_lkp.state, zip_lkp.zip, number_lkp.phone_number, email_lkp.email, country_lkp.country, address_lkp.address, city_lkp.city, count(distinct email_lkp.email_id) mail_count, count(distinct number_lkp.phone_number_id) number_count, people.people_id people left join state_lkp on state_lkp.people_id = people.people_id left join zip_lkp on zip_lkp.people_id = people.people_id left join number_lkp on number_lkp.people_id = people.people_id left join email_lkp on email_lkp.people_id = people.people_id left join country_lkp on country_lkp.people_id = people.people_id left join city_lkp on city_lkp.people_id = people.people_id left join address_lkp on address_lkp.people_id = people.people_id people.last_name <cfqueryparam value="%#trim( lcase(form.search_bar) )#%" cfsqltype="cf_sql_varchar" maxlength="500"> or people.first_name <cfqueryparam value="%#trim( lcase(form.search_bar) )#%" cfsqltype="cf_sql_varchar" maxlength="500"> or people.first_name <cfqueryparam value="%#trim( lcase(form.search_bar) )#%" cfsqltype="cf_sql_varchar" maxlength="500"> or address_lkp.address <cfqueryparam value="%#trim( lcase(form.search_bar) )#%" cfsqltype="cf_sql_varchar" maxlength="500"> -- more filters here group people.people_id desc </cfquery>
Comments
Post a Comment