Need help on search related sql query

need help to use 'like' query to get results based on two sql rows

[neo2sxn]

Hi,

I need help, i have a search box by which i want to get results from two different rows.

Table is like this :

id...storename....discription

1...IndiaTimes... Get 50% off on all products
2... Lenskart ... Get 20% off on all product
3... Myntra.com.. Get 50% off on all products

I want to get search results on this search terms : "50% off on Indiatimes". Currently There are two results for "50% off" but i want to fetch only from Indiatimes because the complete search terms is "50% off on Indiatimes".

Currently i am using this sql query :

$Search=$_GET['search_words']

$sql="select * from TABLENAME where ((storename like '%".$search."%') OR (discription like '%".$search."%')) order by id asc";

AND also tried this :

$sql="select * from TABLENAME where ((storename like '%".$storename."%') OR (discription like '%".$storename."%')) order by id asc";



Please help me out Guys...!

[penders]

A couple of things...

1. If you want to search for the literal string "50% off" when using the LIKE operator (ie. a string that contains a percent character, which, as you are aware is a special character in this context), then you need to escape the percent char ie. '\%' - However, in PHP you will also need to escape the escape (unless you are using parameterised queries). So, this becomes '\\%'.

2. "50% off on Indiatimes" is not contained in any one column in your table. Your SQL query is searching for this entire search term in each column, so it's never going to find a record that matches. You're going to have to parse (or split up) your search phrase into component parts and search more "intelligently". For instance you could let your users search for "storename" separately. Either way, you're going to need to know that "Indiatimes" is a storename and only search in the "storename" column for this value.

[neo2sxn]

Ok...

But there is one more problem...
currently i have this sql :
$sql="select * from TABLENAME where ((storename like '%".$search."%') OR (discription like '%".$search."%')) order by id asc";

when i am searching for "50% off" then it showing two results "one form india time" and "second from myntra.com" BUT when i am searching "50% of myntra" its not showing me any results..

It mean it finds every word given in search box, "50% off" is in 2 rows that's why it showing two results but "50% off" ndiatimes" also have "indiatimes" that is not in not in description table so its not picking that.

My question is; is there any sql query that can only search matching words and leave other words...

[neo2sxn]

Anyone ?

[LifeinAsia]

You're going to need to look into full text search and/or creating a custom solution that will parse your search phrase into smaller chunks and search on those.

What DBMS are you using?

[neo2sxn]

mysql server...but full text search will not help me because we can not use stop words and "%" in search terms. For example "20%" off OR "free" etc...

[LifeinAsia]

That's why I said you'll need to do some customizing.

[neo2sxn]

What if I explode all the words and serch them with "like"

[LifeinAsia]

That's a start. Then you have to combine all the results back. That's what I mean by customizing. :)