Best way of searching by word and sorting the result by number of matching in MySql database

This is a wonderful search technique on MySql data base, I came across while I was in my last internship program at WowWay labs Pvt. Ltd.

The Problem statement was something like this:
 I had a table with all the product details and along with name of the products. I have to search the table by field product_name, and the search result should be prominent, like, if you search "Nokia Lumia 800", then at first all the Nokia Lumia 800, then all the Nokia Lumia products and after that all the Nokia product, as per the requirement. There was lot of algorithms to follow, but none of them came with the exact result all the time, some flaws were there. After working a lot on it I found out about this:
Suppose we got a string "Nokia Lumia 800"
We can simply split it into three part that is, "Nokia", "Lumia", "800", then search for each and every token in product_name, the maximum match found will be sorted to the upper level. For example, if I have a product with product_name "Nokia Lumia 800", it got 3 match found, if there's product named "Nokia Lumia 510", then 2 match found and so on.

So the sql query for this operation will be:

SELECT * FROM (
                                SELECT * ,(
                                       CASE WHEN product_name LIKE '%Nokia%' THEN 3 ELSE 0 END                                   + CASE WHEN product_name LIKE '%Lumia%' THEN 2 ELSE 0 END
                                    + CASE WHEN product_name LIKE '%800%' THEN 1 ELSE 0 END
                                  ) AS numMatches
                                FROM Production ) AS t
                  WHERE numMatches >= 1
 ORDER BY numMatches DESC;

What it is doing basically, creating a virtual field "numMatches", where it's storing the number of matching values for every corresponding product_name.
In this case, the product_name got a match with "Nokia" it'll add 3 to the numMatches, in the same way matching "Lumia" will give it a got for 2 more credit total 3+2 = 5, and for "800" will give 1 credit and all total 5+1 = 6. Now the table will look like something:

product_name                                     numMatches

Nokia Lumia 800                                      6
Nokia Lumia 500                                      5
Nokia Lumia 510                                      5
Nokia 100                                                1

so on..

So basically who have at least one match that can fill up the result but in a order, who has maximum string matched comes fast.

This is faster than any other easy approach and this is also easy indeed. You can modify the algorithm as per your requirement. Hope this will be helpful to you.

Thank you 

Google Drive Form

How to make google form more useful ?

Well, using database is a common fashion for all the technical works, and google makes it easier since when it launched google doc with spreadsheet data base and connected form facility. That makes the database access easier to common people. Not explaining so much about what it exactly does, I should directly say how you can make it more useful for kind of daily work.

How did I get into it?

In my 1st year, I had to make a website for our college technical fest with some of my friends. We were using google docs and it's spreadsheet connected form to make registration for the participants from different colleges. When ever they registered on my form the spreadsheet got updated and I used to give that person a serial ID number manually and email back to that person as his registration was successful with his ID. It was really very time consuming and sometime confusing also as number of registration was increasing day by day. As I'm always a lazy guy, I decided to make a script such a way that it can automatically add a ID number to each participants and mail them back whenever they submit the form. I searched a lot, learned some new things, that's what I'm gonna share now.

This is how my spreadsheet look like:

You also can check out the form here.

How to write a script for google form:

You have to write the script on a component of google app engine connected with that spreadsheet. Follow the steps on spreadsheet:
Tools -> Script editor

a script editor will be opened on a new tab, now you need to write some java script function to modify the behavior of the form.

Take a look at the image, there are a additional column at the right most called ID, that is auto generated, that means every time a new participant will fill the form corresponding ID column will be getting filled up automatically increasing the number with 1.

To make it working I wrote function on script editor:

function onFormSubmit(e) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var row =  SpreadsheetApp.getActiveSheet().getLastRow();
  sheet.getRange(row,7).setValue('sn'+(row-1));
} 

Now,  set the trigger for that function to on form submit so that whenever the user will submit the form it add a ID to the column.

How does the code works:


If you are from a good programming background it wont be too hard to find out what the code does actually, even if you are not from a Java Script back ground. I'm also not from a Java Script side. :) So not a problem, in the row variable, it'staking the number of  last row submitted to the form by  SpreadsheetApp.getActiveSheet().getLastRow()  this function. Then from sheet.getRange(row,7), it's taking the position of the 7th column cell of the last row, and sheet variable is declared before where the active sheet is stored. With that instance it's setting the value with ('sn'+(row-1)).

So, finally sheet.getRange(row,7).setValue('sn'+(row-1));  sis setting the (x,7) cell to the value of 'snY' where x is the current row number and Y=x-1.