Dids u know that u can search across multiple columns in a database table??

Problem :- There are three columns in a table on MySQL database.

Table Name :- Files

Column Names :- Title, Tags, Description

Lets Say that the search term is “red apple fresh”.

Here is the current wrong query which tries to search this term:

SELECT * FROM files WHERE (files.title LIKE ‘%”red apple fresh”%’ OR files.tags LIKE ‘%red apple fresh”%’ OR files.description LIKE ‘%red apple fresh”%’) ;

The problem with this query is  that it will only select items from the database that have “red apple fresh” together as one term in either the title, tags or description fields. Also if the term “fresh apple red” rather than “red apple fresh” is in either of the fields it will not select the item from the database.

So i needed a query that could select the item if the word “red” is in one field and “apple” is in another field and “fresh” is in the  third field. Also the search should be order insensitive. A one word search like “apple” should result in fetching any item with that word in any of these three fields.

Also with the current query if the person searches “red” then the results will show irrelevant items such as items with anything starting with “red…” such as “reddish” “reddy” ect. This is another error I’s trying to overcome.

Solution:-

Steps of the solution of the above pages is as follows:-

1. The storage engine of the above table should be MyISAM for the coming query to work. So we need to fire the following query to change the Storage engine of the table.

Alter table Files ENGINE=MyISAM;

2. Also the FULLTEXT search shall be enabled where the FULLTEXT is constituting all the three columns where the search term is supposed to occur.

Alter table files add FULLTEXT(title, tags, description);

3. Now the following query will solve the above problem.

query = Select * from files where (match(files, tags, description) Against (‘+red+apple+fresh’);

Cheers!!!

Advertisements