Searching by words

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
xad001x0w
Forum Newbie
Posts: 6
Joined: Wed Sep 27, 2006 1:55 pm
Location: Oxford, UK

Searching by words

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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[[:>:]]'
(#10850)
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
xad001x0w
Forum Newbie
Posts: 6
Joined: Wed Sep 27, 2006 1:55 pm
Location: Oxford, UK

Post 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
xad001x0w
Forum Newbie
Posts: 6
Joined: Wed Sep 27, 2006 1:55 pm
Location: Oxford, UK

Post by xad001x0w »

bump
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post 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.
xad001x0w
Forum Newbie
Posts: 6
Joined: Wed Sep 27, 2006 1:55 pm
Location: Oxford, UK

Post 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.
Post Reply