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, Dec 07 '16 at 23:07

score 2 · Accepted Answer · answered Dec 07 '16 at 22:14

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 07 '16 at 22:14

Robbie

17,605
4
35
72

I did `SELECT * FROM `films` WHERE MATCH (film_name) AGAINST ('+Raj' IN BOOLEAN MODE)` And it didn't return any matches – buli Dec 07 '16 at 22:39
I'm using PHP my admin. When I select table, do I click 'Full text' to `ALTER TABLE `films` ADD FULLTEXT(` film_name `);` – buli Dec 07 '16 at 22:41
Ok if I search for Raj: wiara it does return matches. But when I type only "raj" or even "raj:" it doesn't. – buli Dec 07 '16 at 22:45
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 Dec 07 '16 at 22:45
If you have a problem with "Raj" vs "raj" check the case insensitivity. By default the file is case-insensitive (`general-ci`) but if you've changed it to `general-cs` or `something-else-ending-in-cs`then it'll be case sensitive; just change to `ci` – Robbie Dec 07 '16 at 22:48
Yeah I've added the fulltext index to film_name. I'm using utf8_general_ci. I'm using mysql 5.5.53, but I'm using MyISAM so fulltext should work. Other searches work, but what I'm testing now is that if I input into AGAINST a query with less than 4 characters (4 still work) it doesn't return any matches. (but why then "raj:" didn't work? if I type "obcy" it works for another film. doesn't ":" count as character? – buli Dec 07 '16 at 23:01
Oh, yes. Should have twigged - <4 characters doesn't work by default. Sorry. But, if you need this, you can tweak settings to allow 2/3 character words (http://dev.mysql.com/doc/refman/5.7/en/fulltext-fine-tuning.html) but there is a hit/drop in performance and size of index. – Robbie Dec 07 '16 at 23:16
Been a while since I actually used Full Text Search, and remember going on the same journey you're on! It works well once you have it running. Other options would be to look at a special search engine, such as SOLR (http://lucene.apache.org/solr/) – Robbie Dec 07 '16 at 23:18
Changed that value to 1 (some movies have words like "an" or part "1" etc) repaired my table and it works great now. Even sorts the results like it should, giving me best results first! Thank you very, very much for help! Now I have the start and can optimize it further. You said it would hit performance, will it greatly? – buli Dec 07 '16 at 23:46
1

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. – Robbie Dec 07 '16 at 23:49

score 1 · Answer 2 · edited May 23 '17 at 11:52

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 '17 at 11:52

Community

1
1

answered Dec 07 '16 at 22:13

Phiter

14,570
14
50
84

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! – buli Dec 07 '16 at 23:07

PHP search MySQL

2 Answers2