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!!!
-
Recent
- interesting fact about identityHashCode() method in java.lang.System Class
- Interesting inconsistency with java String pool
- did u know u can find the location of the .class file from the instance??
- Cases for Shortcomings of Single Request-Response model
- Information asymmetry
- Another bad mark on My Image in Boss’s Mind……:(
- Sharing the same connection or transaction space between derby sql and java.
- Did u know that u can shutdown someone’s machine(windows) using java????
- Utilizing JAVA’s power in Derby Database Triggers
- Dids u know that u can search across multiple columns in a database table??
- Did you know that you can compare RAW columns in a DB?
-
Links
-
Archives
- July 2008 (2)
- April 2008 (2)
- September 2007 (5)
- August 2007 (1)
- May 2007 (1)
-
Categories
-
RSS
Entries RSS
Comments RSS