PHP search 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?

What framework are you using here? Why aren't you using placeholder values for things like $query? Putting that directly in the query string is extremely dangerous. — tadman
– tadman, Commented Dec 7, 2016 at 23:07

Robbie · Accepted Answer · 2016-12-07 22:14:25Z

2

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.

answered Dec 7, 2016 at 22:14

Robbie

17.7k4 gold badges40 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

buli Over a year ago

I did SELECT * FROM films` WHERE MATCH (film_name) AGAINST ('+Raj' IN BOOLEAN MODE)` And it didn't return any matches

buli Over a year ago

I'm using PHP my admin. When I select table, do I click 'Full text' to ALTER TABLE films` ADD FULLTEXT(` film_name );

buli Over a year ago

Ok if I search for Raj: wiara it does return matches. But when I type only "raj" or even "raj:" it doesn't.

Robbie Over a year ago

Some gotchas hidden in the docs: Did you add the indexes? Try just SELECT * FROM films WHERE MATCH (film_name) AGAINST ('Raj') first, before building in the Boolean. Also, if you're using an older version of mySQL then you need myISAM tables - it only works with innoDB in 5.7+ (I think that's the right version).

Robbie Over a year ago

Can't say, and obviously depends on the size of your database/index. You'll have to suck it an see. You might find it's more optimal to drop search terms of 2 or less characters, get a bigger pool of results, and match those results in PHP against the short search terms and drop those that don't fit - but you'll need to test to find out which is best.

|

Community · Accepted Answer · 2017-05-23 11:52:58Z

1

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.

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered Dec 7, 2016 at 22:13

Phiter

15k14 gold badges52 silver badges87 bronze badges

1 Comment

buli Over a year ago

Thanks for help! Could be good, but I will use the fulltext answer, it has implemented search score etc... which suits me better :) Anyway thank you!

Collectives™ on Stack Overflow

PHP search MySQL

2 Answers 2

10 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related