Welcome to WebmasterWorld Guest from 107.20.75.63

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Need help on search related sql query

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

     
5:01 pm on Jun 15, 2014 (gmt 0)

Junior Member

joined:Apr 12, 2012
posts: 42
votes: 0


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...!
8:57 am on June 16, 2014 (gmt 0)

Senior Member

WebmasterWorld Senior Member penders is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month

joined:July 3, 2006
posts: 3123
votes: 0


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.
11:32 am on June 16, 2014 (gmt 0)

Junior Member

joined:Apr 12, 2012
posts: 42
votes: 0


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...
4:47 pm on June 18, 2014 (gmt 0)

Junior Member

joined:Apr 12, 2012
posts: 42
votes: 0


Anyone ?
6:18 pm on June 18, 2014 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5550
votes: 24


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?
6:36 pm on June 18, 2014 (gmt 0)

Junior Member

joined:Apr 12, 2012
posts: 42
votes: 0


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...
6:54 pm on June 18, 2014 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5550
votes: 24


That's why I said you'll need to do some customizing.
7:01 pm on June 18, 2014 (gmt 0)

Junior Member

joined:Apr 12, 2012
posts: 42
votes: 0


What if I explode all the words and serch them with "like"
7:05 pm on June 18, 2014 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5550
votes: 24


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