search problem

Report · Mar 16, 2008

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

Report · Mar 16, 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/

Report · Mar 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?

Report · Mar 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.

Report · Mar 17, 2008

it is generating no error..

When i enter a it displays all records irrespective of its activeness and activeness, What is happening

I do not know

I am frustated at this?

Report · Mar 17, 2008

it is generating no error..

When i enter a it displays all records irrespective of its activeness and activeness, What is happening

I do not know

I am frustated at this?

Report · Mar 17, 2008

I thought using varchar would be somewhat buggy so i changed it to tinyint and made the value of the status to to 1 for active and 2 for inactive but same problem..

Here is my little changed search code which is troubling..

<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 0=0
and
properties_details.status = <cfqueryparam cfsqltype="cf_sql_numeric" value="1">
<cfloop index="i" list="#url.search_string#>" delimiters=" ">
<CFIF url.search_string is not "">
AND property_unique_name LIKE '%#i#%'
OR property_features LIKE '%#i#%'
OR suburbname LIKE '%#i#%'
OR profession LIKE '%#i#%'
OR property_layout LIKE '%#i#%'
<cfelseif url.search_string IS "">
AND property_unique_name LIKE '%#i#%'
OR property_features LIKE '%#i#%'
OR suburbname LIKE '%#i#%'
OR profession LIKE '%#i#%'
OR property_layout LIKE '%#i#%'
</CFIF>
</cfloop>
ORDER BY property_unique_name asc
</cfquery>

now when i search blank, it generates all the values irrespective of actiness and inactiveness.

if i put something like "a" to search, it returns no results and if something like a suburb name, it gives indifferent results..

like including the inactive fields also..

This is below how my serach goes:
search.cfm?search_string=Avalon+Industrial&act=22

Report · Mar 17, 2008

try this:

<cfquery name="Recordset1" datasource="#request.dsn#"
username="#request.user#" password="#request.password#">
SELECT pd.*, s.suburbname, t.profession
FROM properties_details pd
INNER JOIN suburbs s ON s.suburb_id = pd.suburb_id
INNER JOIN tenants t ON t.tenantID = pd.tenant_id
WHERE 0=0
AND pd.status = <cfqueryparam cfsqltype="cf_sql_numeric" value="1">
<CFIF len(trim(url.search_string))>
<cfloop index="i" list="#trim(url.search_string)#" delimiters="
,.;:|\/?!+">
AND
(pd.property_unique_name LIKE '%#i#%'
OR pd.property_features LIKE '%#i#%'
OR s.suburbname LIKE '%#i#%'
OR t.profession LIKE '%#i#%'
OR pd.property_layout LIKE '%#i#%')
</cfloop>
</CFIF>
ORDER BY pd.property_unique_name
</cfquery>

i might have mixed up which table some fields come from, so double-check
that...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Report · Mar 18, 2008

Thanks Pal, That resolved the query

But i need some answer from you that why you have used this regex kind of expression:

,.;:|\/?!+

and this played some role in this..

A New learning Curve for me instead..

Thanks and Please explain that above i shud be very thankful to you

Report · Mar 18, 2008

that is no regex at all. just multiple list delimiters.
if you have never heard of lists being delimited by multiple characters,
you should look back onto cf docs.

but frankly, i do not think that mattered much. i think it was to do
with your cfloop which was not correct, and possibly with having the OR
parts of the where clause in ()...

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Report · Mar 17, 2008

well, your code does not do that. your code tries to compare the value
of price_range column to first and last 6 characters of your search phrase.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Report · Mar 17, 2008

i removed the code price code and it is working fine, but how do i clcude that functionality it is required for the website

Report · Mar 17, 2008

i am getting an error where i try to show only the properties which have the status of yes only, but by default it is showing me the all properties whose status is no also:

here is the code i am working on:

<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 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#%'
OR property_features LIKE '%#i#%'
OR suburbname LIKE '%#i#%'
OR profession LIKE '%#i#%'
OR property_layout LIKE '%#i#%'
<cfelseif url.search_string IS "">
AND property_unique_name LIKE '%#i#%'
OR property_features LIKE '%#i#%'
OR suburbname LIKE '%#i#%'
OR profession LIKE '%#i#%'
OR property_layout LIKE '%#i#%'
</CFIF>
</cfloop>

i did not see anything wrong, But it is behaving very awkward
</cfquery>

Report · Mar 17, 2008

which datatype is your status field?

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Report · Mar 17, 2008

where does the value you want to compare your price come from?

take the price comparison code out of your cfloop if it has nothing to
do with submitted search string.

use your db's equivalent of LEFT() and RIGHT() functions to get only the
needed part of the price range and compare to submitted price value.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/

Report · Mar 17, 2008

which datatype is your status field?

it is varchar in mysql database

Report · Mar 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/

Report · Mar 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..

Report · Mar 17, 2008

what's the error??? what is cf complaining about?

do several tables used in your query have a column named 'status'? if
so, fully qualify the column by using tablename.columnname: i.e. WHERE
properties_details.status = <cfqueryparam cfsqltype="cf_sql_varchar"
value="Yes">

another possible area to look at is query/template caching: clear cache
in cf admin and see if that helps.

Azadi Saryev
Sabai-dee.com
http://www.sabai-dee.com/