Skip to main content
Inspiring
March 17, 2008
Question

search problem

  • March 17, 2008
  • 3 replies
  • 1130 views
I am making a simple search but when i enter some search word, it generates an error like i enter "tango" as search keyword, it generated error as: column tango not found..

here is the search code i am trying to do..

<cfquery name="Recordset1" datasource="#request.dsn#" username="#request.user#" password="#request.password#">
SELECT properties_details.*, suburbname,
profession
FROM properties_details
JOIN suburbs ON suburbs.suburb_id = properties_details.suburb_id
JOIN tenants ON tenants.tenantID = properties_details.tenant_id
WHERE properties_details.status = <cfqueryparam cfsqltype="cf_sql_varchar" value="Yes">
<cfloop index="i" list="#url.search_string#" delimiters=" ">
<CFIF url.search_string is not "">
AND property_unique_name LIKE '%#i#%'
</CFIF>

<CFIF url.search_string is not "">
OR property_features LIKE '%#i#%'
</CFIF>

<CFIF url.search_string is not "">
OR suburbname LIKE '%#i#%'
</CFIF>

<CFIF url.search_string is not "">
OR profession LIKE '%#i#%'
</CFIF>

<CFIF url.search_string is not "">
OR property_layout LIKE '%#i#%'
</CFIF>

<CFIF url.search_string is not "">
OR price_range >= (#Left(i,6)#)
</CFIF>

<CFIF url.search_string is not "">
OR price_range <= (#right(i,6)#)
</CFIF>
</cfloop>
ORDER BY property_unique_name asc
</cfquery>

if i enter nothing it shows me properties list and if i enter something, it generates error:

Can anybody tell me what is happening here, i tried it for the last 3 days
    This topic has been closed for replies.

    3 replies

    Inspiring
    March 17, 2008
    first, i would probably use a tinyint(1) datatype for yes/no fields...
    but if you use other values in it too (i.e. 'pending' or 'closed', etc)
    then varchar is good.

    second, i forgot to ask what exactly is the error you are getting when
    trying to show only records with status = 'yes'?

    have you tried just using WHERE status = 'yes' ? (without the
    <cfqueryparam ...>, just to test) are you sure the values are actually
    'yes', and not ' yes' or 'yes '?


    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com/
    Inspiring
    March 17, 2008
    i created a new query and shoed the fields whose status is yes, then i dumped the data and got the correct data like 10 records whose value was yes, but when i tried the old query also by removing, it still generates the error, I do not know what is happening here..

    Inspiring
    March 17, 2008
    which datatype is your status field?

    it is varchar in mysql database
    Inspiring
    March 17, 2008
    i suspect it must be this code that generates that error:

    <CFIF url.search_string is not "">
    OR price_range >= (#Left(i,6)#)
    </CFIF>

    <CFIF url.search_string is not "">
    OR price_range <= (#right(i,6)#)
    </CFIF>

    when you enter tango as search string it tries to compare price_range to
    (tango) and assumes tango is a name of a column in one of your tables...

    Azadi Saryev
    Sabai-dee.com
    http://www.sabai-dee.com/
    Inspiring
    March 17, 2008
    Thanks for pointing out mate but well i need to match the price valu like if the price the 600000, it should only show the property of the value.

    The price is stored in the database as: 400000:500000

    So i was making the value to extract the value of last 6 digits from the right and the left irresptively.

    Cheers and how should i solve that problemo?
    Inspiring
    March 17, 2008
    quote:

    Originally posted by: nightwolf666
    Thanks for pointing out mate but well i need to match the price valu like if the price the 600000, it should only show the property of the value.

    The price is stored in the database as: 400000:500000

    So i was making the value to extract the value of last 6 digits from the right and the left irresptively.

    Cheers and how should i solve that problemo?


    Replace that column with 2 numeric ones.