query search letters to letters?

I am making a search with suggestions offered on basis of letters typed in the search box.

Example:

I have in database this words : salam, salavat, sabos, sandal, sefid, siah and ...

I typed in search box : s --display this words--> salam, salavat, sabos, sandal, sefid, siah

Now if typed : sa --display this words--> salam, salavat, sabos, sandal,

If typed : sala --display this words--> salam, sal开发者_开发百科avat

If typed : salam --display this words--> salam

I am using jQuery event .keyup()(keyup is working fine) and using codeigniter at the backend(query in codeigniter not what that i want).

See my query(In this query, $find contains the sequence already typed in, and i am using like Keyword for searching in the database):

$find = $this->input->post('find');
    $where  =   "date LIKE '$find' OR
            name LIKE '$find' OR 
            star LIKE '$find' OR
            address LIKE '$find' OR
            number_phone LIKE '$find' OR 
            type LIKE '$find' OR
            service LIKE '$find' OR
            units LIKE '$find' OR
            useradmin LIKE '$find'";

$query = $data['results'] = $this->db->query("SELECT @rownum:=@rownum+1 rownum, t.*
        FROM (
        SELECT *
        FROM my_table
        WHERE 
            $where
        ORDER BY id desc
        LIMIT $offset, $coun_page       
        ) t,
        (SELECT @rownum:=0) r");

The following query works, but i can not use of it because of rownum:

$this->db->order_by("id", "desc")->like('name', $name)->get('my_table')

How do I get suggestions with rownum?

For matching a substring with a LIKE query, you need to use a wildcard character, which in SQL is the percentage-character %.

E.g.

WHERE foo LIKE '%bar%' would find all rows where the column foo contains the substring 'bar'.

WHERE foo LIKE 'bar%' would find all rows where the column foo begins with the substring 'bar'.

WHERE foo LIKE '%bar' would find all rows where the column foo ends with the substring 'bar'.

PS. it's always a good idea to contain column names in back ticks to avoid collisions with (potentially) reserved names, such as date and type.

SELECT `column1`, `column2` FROM `table` WHERE `column3` = 'value'