Page 1 of 1

Searching by words

Posted: Sat Jan 13, 2007 4:23 pm
by xad001x0w
I have created a web-site with a very simple search function, currently it looks like this:

Code: Select all

$sql = "SELECT * FROM table WHERE col1 LIKE \"%$searchterms%\" OR shortdesc LIKE \"%$searchterms%\" OR longdesc LIKE \"%$searchterms%\"";
However if you search for "hat" you will get hits on "that", "chat", "phat" etc, whereas I would like it to only hit on "hat". By this i mean that "hat", " hat ", "HaT", "hat;", would all be deemed as a result.

Now before we go any further, I would like to point ouy that I have searched these forums for answers but still have problems :P
First off I tried using this method:

Code: Select all

$sql = "SELECT * FROM table WHERE col1 LIKE \"%{$searchterms}%\" OR shortdesc LIKE \"%{$searchterms}%\" OR longdesc LIKE \"%{$searchterms}%\"";
This produced the same search results as before, then I tried using:

Code: Select all

$sql = "SELECT * FROM table WHERE col1 RLIKE [[:<:]]".$searchterms."[[:>:]] OR shortdesc RLIKE [[:<:]]".$searchterms."[[:>:]] OR longdesc RLIKE [[:<:]]".$searchterms."[[:>:]]";
However this resulted in 0 results no matter what string I searched for.

So basically, I would like some help in resolving my problems or any advice as to other methods by which I can achieve my goal. I will explain further if anything is unclear.
Thanks

EDIT:
Sorry if this is in the wrong forum, I was unsure where to stick it.

Posted: Sat Jan 13, 2007 6:02 pm
by Christopher
The simplest way to search by words might be to just put a space either side of thecol1 LIKE \"% $searchterms %\" and if you use regexp then you need to put the expression in quotes RLIKE '[[:<:]]$searchterms[[:>:]]'

Posted: Sat Jan 13, 2007 7:34 pm
by Ollie Saunders
Make sure you have some way of knowing when the queries fail. This:

Code: Select all

mysql_query($q) or die('query failed');
is the simplest. However don't output $q to the browser even when dieing as that is a seriously bad from a security standpoint.

Posted: Mon Jan 15, 2007 10:18 am
by xad001x0w
Thanks for your replies.
@ arborint,
Your first suggestion doesnt give me the results that I desire, this is because it wont match in these situations:

Search term: "hello"
"Hello charly" - no match
"i said hello!" - no match

I tried reformatting the regex query like you said but then i get a parse error: "unexpected '[', expecting T_STRING or T_VARIABLE or T_NUM_STRING" That is from this query:

Code: Select all

$sql = "SELECT * FROM table WHERE col1 RLIKE '[[:<:]]$searchterms[[:>:]]' OR shortdesc RLIKE '[[:<:]]$searchterms[[:>:]]' OR longdesc RLIKE '[[:<:]]$searchterms[[:>:]]'";
@ ole
Thanks for pointing that out, I just realised that the mysql_num_rows($result) was failing with this query:

Code: Select all

$sql = "SELECT * FROM table WHERE col1 RLIKE '[[:<:]]".$searchterms."[[:>:]]' OR shortdesc RLIKE '[[:<:]]".$searchterms."[[:>:]]' OR longdesc RLIKE '[[:<:]]".$searchterms."[[:>:]]'";
So there is something wrong with this query, but i cant see it.

:( any suggestions guys?
Thanks

Posted: Tue Jan 16, 2007 12:34 pm
by xad001x0w
bump

Posted: Tue Jan 16, 2007 12:45 pm
by Ollie Saunders
I can't see anything wrong with that query but I am getting a bit rusty with SQL therse days. I can only suggest that you escape $searchterms with mysql_real_escape_string(). That will prevent anything in $searchterms breaking the query syntax or people from hacking your database.

Posted: Tue Jan 16, 2007 1:47 pm
by xad001x0w
Thanks for that ole, i will do so. I think I am going to re-write my whole search system, its far too messy at the moment. I will post here again if I continue to have problems.

Thanks guys.