Search Query with SQL

The search system is a system used to search for data that has been stored previously in the database, for a little data may not be necessary, but as more search data is very important to find the data we want, to save time and exact destination / data sought According to what we need, For example: Google, who does not know google? This biggest search engine is one such example, Here are some examples of queries to search:Example 1:

    SELECT table1.field1 FROM table1
    WHERE table1.field1 LIKE "% search%"
    ORDER BY table1.field1 DESC LIMIT 20

In the query "SELECT table1.field FROM table1" so that is shown only field1 in table1 of table1 only to display all the filed can be replaced tabel1.fieldnya with *, then WHERE table1.field1 LIKE "% search%" so with filed1 conditions in table1 We search on the search according to the word we write by replacing the word "search" with the word we want to search, ORDER BY table1.field1 DESC LIMIT 20 sorted by filed1 in table1 by descending (az) with limit displayed / limited by 20 data.But with the query sometimes we use like "% keyword%" is in SQL Statement. What we look for does not appear the example of the scenario, we want to find the top 20 who have the name "eka" because there will be a lot of names, and not to mention the name of the first name or bleakangnya name such as eka haryanto, haryanto eka etc, Finally searched eka does not appear in the top 20 list / top, then the solution using the query model as follows.Example 2:

    SELECT tbl_a.username FROM tbl_a
    WHERE tbl_a._ama_anggota LIKE "% eka%"
    ORDER BY case
    When tbl_a.nama_anggota like "eka" then 1
    When tbl_a.nama_anggota like "eka%" then 2
    When tbl_a.nama_anggota like "% eka" then 3
    Else CONCAT (4, tbl_a.name_members) end LIMIT 20

In the query "SELECT a.name_member FROM tbl_a" so that is displayed only the name of the member in table A of table A only, then WHERE tbl_a.nama_anggota LIKE "% eka%" so with condition nama_anggota in table A that we search on search according to word We write with the word "eka" the word we want to search, then ORDER BY case when tbl_a.nama_anggota like "eka" then 1 when tbl_a.nama_anggota like "eka%" then 2 when tbl_a.nama_anggota like "% eka" then 3 else CONCAT (4, tbl_a.name_members) end LIMIT 20 hereby forces the name of the member whose last name appears at the top followed by the rear variant, the front and the remaining variants, with the limit displayed / limited by 20 data.Thus Query search with SQL there are 2 case examples, where example 1 is a common case example, whereas example2 is a special case example,If any suggestions / maybe there is another statement better please free berargumen but with good manners ya, Happy Coding, And Fighting :)