CODEWITHSUNDEEP
phpmysql
buli
buli

Reputation: 146

PHP search MySQL

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?

Upvotes: 0

Views: 57

Answers (2)

Robbie
Robbie

Reputation: 17720

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.

Upvotes: 2

Phiter
Phiter

Reputation: 14982

You could indeed explode your string, using this answer's solution.

function searchMovie($query)
    {
        $queries = preg_split('/[^a-z0-9.\']+/i', $query);
        foreach ($queries as $keyword){
             $this->db->where("film_name LIKE  '%$keyword%'");
        }

        $movies = $this->db->get ("films", 40);
        if($this->db->count > 0)
        {
            return $movies;
        }
        return false;
    }

This will create multiple ANDconditions for your db where, so the result will be filtered.

Upvotes: 1

Related Questions