SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help with SQL Code (for basic site search)


Need help with SQL Code (for basic site search)

Author
Message
scott231us
scott231us
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1
Hi, the basic search on my site is too specific.. Meaning it's too exact with word order and phrases.

For example, if I search Shoes Red it won't show results for Red Shoes.

If you search for Red Shoe Laces it won't show results for Red Laces (as the word shoe throws off this order). Below is the code that I believe the search box is using. Can anyone tell me how to "loosen" up this search so it's either more broad or so exact word order isn't necessary?

This is the current code:

case 'search':
if (intval($filter) != 0) {
$filter = JString::strtolower($filter);
$id = intval($filter);
$search .= $temp."(a.id = $id OR LOWER(a.ad_headline) LIKE '%".$this->_db->getEscaped($filter,true)."%' OR LOWER(a.ad_text) LIKE '%".$this->_db->getEscaped($filter,true)."%')";
} else {
$filter = JString::strtolower($filter);
$search .= $temp."(LOWER(a.ad_headline) LIKE '%".$this->_db->getEscaped($filter,true)."%' OR LOWER(a.ad_text) LIKE '%".$this->_db->getEscaped($filter,true)."%')";
}
break;
}
}
}
return $search;
}


Any ideas here?
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28076 Visits: 39926
Scott i suspect you posted some code for MYSQL and not SQL Server.

In SQLServer, the best solution is to enable Full Text Indexing, and use that;
for exact word match, but any order, i posted this example a long time ago, which was using dynamic SQL to build a query for search terms; unfortunately, this kind of query isslow, since it requires a table scan. That might be acceptable for small tables, but the more data, the more this would suffer performance wise:

example results:

WHERE CHARINDEX('civil',YOURTABLE.COLUMNNAME) > 0
OR CHARINDEX('war',YOURTABLE.COLUMNNAME) > 0
OR CHARINDEX('memorabilia',YOURTABLE.COLUMNNAME) > 0
OR CHARINDEX('equipment',YOURTABLE.COLUMNNAME) > 0


--better format
DECLARE @SEARCHSTRING VARCHAR(8000),
@ALLSEARCHTERMS VARCHAR(8000),
@SOMESEARCHTERMS VARCHAR(8000),
@COLUMNNAME VARCHAR(128),
@vbCrLf CHAR(2)
SET @SEARCHSTRING='civil war memorabilia equipment'
SET @COLUMNNAME = 'YOURTABLE.COLUMNNAME'
SET @vbCrLf = CHAR(13) + CHAR(10)

SELECT
@ALLSEARCHTERMS = 'WHERE CHARINDEX(''' + REPLACE(@SEARCHSTRING,' ',''',' + @COLUMNNAME + ') > 0 ' + @vbCrLf + ' AND CHARINDEX(''') + ''',' + @COLUMNNAME + ') > 0' + @vbCrLf,
@SOMESEARCHTERMS = 'WHERE CHARINDEX(''' + REPLACE(@SEARCHSTRING,' ',''',' + @COLUMNNAME + ') > 0 ' + @vbCrLf + ' OR CHARINDEX(''') + ''',' + @COLUMNNAME + ') > 0' + @vbCrLf
SELECT @ALLSEARCHTERMS
SELECT @SOMESEARCHTERMS



Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

scott231us
scott231us
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 1
Oh yes you're right, it's code for MySQL, so don't think your fix would work for that unfortunately?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search