PHP搜索MySQL

Question

I've got this code:

function searchMovie($query)
    {
        $this->db->where("film_name LIKE  '%$query%'");
        $movies = $this->db->get ("films", 40);
        if($this->db->count > 0)
        {
            return $movies;
        }
        return false;
    }

Javascript code from my submit form button strips all special characters like ; : ' / etc. from query string, and then redirects user to search uri (szukaj/query). So for example if film_name is Raj: wiara, and user searches for raj: wiara, the query looks like raj wiara and user doesn't get any results. I was thinking about exploding query into single words and then foreach word do a SELECT from db, but it would give multiple results of same movie. Don't want to change the javascript code, and I think I can't make that film names without the special characters like :. Or maybe create another column in db for film_keywords and add there all words of movie separated by , or something and then search this column?

Answer 1

MySQL's Full Text Search functions are your friend here:

http://dev.mysql.com/doc/refman/5.7/en/fulltext-search.html

Will return a series of matches and give a score so you return in best-match order.

---

Warning: $this->db->where("film_name LIKE '%$query%'"); is open to SQL injection. Anyone can circumnavigate the JavaScript so you must always clean up input server-side. This is best done using the DB functions as well, not just stripping characters - so check whatever library you are using in order to do this.