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: 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:

oh nippon

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

Popular posts from this blog

php - cannot display multiple markers in google maps v3 from traceroute result -

i'm newbie in php mysql , google maps v3. have problems in google maps v3. made traceroute web based , map traceroute's result in google maps v3 final project of college education. have try many tutorials didn't work. have tried tutorials in forum nothing worked. problem cannot display multiple markers got location data database. maybe because i'm newbie , not know how do. confuse looping show markers. here code <?php error_reporting(e_all ^ (e_notice)); ini_set('max_execution_time', 360); $enable_log_user = false; global $ip, $host_name, $host_ip; $host = @$_post['host']; $trace = @$_post['trace']; $self = $_server['php_self']; include("phpsqlajax_dbinfo.php"); $connection = mysql_connect ('127.0.0.1', $username, $password); if (!$connection) { die('not connected : ' . mysql_error());} $db_selected = mysql_select_db($database, $connection); if (!$db_selected) { die ('can\'t use db : ...
Read more

c# - DetailsView in ASP.Net - How to add another column on the side/add a control in each row? -

i have detailsview control on page used edit various fields of record, works in respect. i looking way add 1 column (and if works, why not more) right, absolutely read-only, show same fields of record comparison purposes. i aware there no obvious way such thing out of box detailsview . have looked other controls (transposed gridview , recommended formview , listview ), nothing satisfies. have special data binding setup using detailsview , can't out of without losing features. anyone on how "hack in" additional columns (for display only) on detailsview ? the solution have now, use second detailsview , visible set false in aspx. in code, make sure databind hidden detailsview hosts data third column first, initial detailsview named itemdetails . and in item created event, pass third column html rendering of hidden controls (in last code block) : protected void itemdetails_itemcreated(object sender, eventargs e) { if (dataite...
Read more

javascript - firefox memory leak -

i have following script (i use raphaeljs also) draws circle, waits 1 second, draws circle again, etc (you can copy paste - functional, need include raphael raphaeljs.com): <div id="holder"> </div> <script src="raphael.js" type="text/javascript"></script> <script> var paper = raphael(10, 50, 320, 200); var rad = math.pi / 180; var finangle = 0; function sector(cx, cy, r, startangle, endangle, params) { var x1 = cx + r * math.cos(-startangle * rad), x2 = cx + r * math.cos(-endangle * rad), y1 = cy + r * math.sin(-startangle * rad), y2 = cy + r * math.sin(-endangle * rad); return ["m", cx, cy, "l", x1, y1, "a", r, r, 0, +(endangle - startangle > 180), 0, x2, y2]; } var path = sector(100,100,50,0,180); var arc = paper.path(path); function draw() { path = sector(100,100,50,0,finangle); ...
Read more