Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help with SQL Code (for basic site search) Expand / Collapse
Author
Message
Posted Wednesday, February 6, 2013 9:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 6, 2013 10:06 AM
Points: 2, 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?
Post #1416605
Posted Wednesday, February 6, 2013 9:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:48 AM
Points: 12,915, Visits: 32,075
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1416616
Posted Wednesday, February 6, 2013 10:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 6, 2013 10:06 AM
Points: 2, Visits: 1
Oh yes you're right, it's code for MySQL, so don't think your fix would work for that unfortunately?
Post #1416632
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse