Returning rows from database based on search query

Question

I am having a product table with following schema:

products (itemname VARCHAR(50), itemid INT(10));

Sample records are shown below (itemname, itemid):

blackberry curve 3g (black), 1
samsung ace, 3
blackberry curve 3g (white), 2
apple iphone 4 32gb (black), 4

Now suppose user enters the query into the search box of the website and accordingly results are displayed. i.e. if user enters blackberry then Blackberry mobiles should be displayed

I am looking answers for the following questions:

How can search text be matched with the items? For example: user may enter blackberry in the search box then how it can be searched? Should I search for blackberry word as a sub-string of itemname column of the above table?
If the user enters two words like: apple 32gb. Now if I use the above mentioned method of sub-string then it wont work because there is no row in the table having apple 32gb as sub-string. How to search in this case?
If the user enters a very generic search text like: mobiles. In this case, all the mobile phones should be displayed. Matching the itemname as a sub-string will not work in this case also.
User may enter search text: apple 32gb black color. In this case, apple products of 32GB capacity and black color should be displayed.

I know that implementing the search 100% correct is not possible. I am just looking for hints/how to proceed. I am using PHP, MySQL, Apache.

Jim · Accepted Answer · 2011-08-13 21:38:41Z

1

What kind of database is this, if MySQL you can enabled fulltext search on the field and then just a simple query will get you the results you expect.

answered Aug 13, 2011 at 21:38

Jim

7056 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sumit Over a year ago

Hi Jim, Thanks for the answer. Will it help in searching for two-word-search-text like samsung mobiles? Also I don't think full text search will be able to generate results for generic queries like: mobiles, black color mobiles etc.

Jim Over a year ago

I added full text and searching to a site recently and it is searching article names and it has been returning great results in testing so far. There are lots of examples but check into this post for a good base, devzone.zend.com/article/1304.

Martín Coll · Accepted Answer · 2011-08-13 22:08:13Z

1

for many word searching I would do something like this:

$searchArray = explode(" ", $searchString);
foreach ($searchArray as $word)
{
    //add an OR clause to your sql query like the following
    $query .= "OR itemname like %".$word."%";
}

the other questions are far more complicated, but I hope this helps

answered Aug 13, 2011 at 22:08

Martín Coll

3,8405 gold badges40 silver badges57 bronze badges

Collectives™ on Stack Overflow

Returning rows from database based on search query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related