Searching MySQL database

Question

I'm making a movie website and you can search through the database to find a wanted title but I have a problem... How to search a database with PDO? I have something like this:

$search=$_GET['search'];
$search='%'.$search.'%';
$db = new mysqli($DBhost, $DBuser, $DBpassword, $DBdatabase);
$sql="SELECT id, title, year, front_image, description FROM a358_filmovi WHERE title LIKE ?";
$sl=$db->prepare($sql);
$sl->bind_param('s',$search);
$sl->execute();
$sl->bind_result($id, $title, $year, $front_image, $description);
while($sl->fetch()){
?>
    DATA OUTPUT
<?php
}

And the problem is that I have record Harry Potter and the Deathly Hallows: Part 1 and if I search for Harry Potter it will display that movie as a result but if I search for Harry potter, it won't display anything (blank result).

That's just an example movie, it's not working for any movie from the database.

How can I edit this code to match every result from search input, uppercase or lowercase?

Thanks in advance.

possible duplicate of http://stackoverflow.com/questions/8083455/mysql-like-case-sensitive — Jason Fingar, Apr 17 '14 at 17:37
@Devon Well isn't 's' standing there for 'string', 'i' for 'integer' etc.? — valek, Apr 17 '14 at 17:38
Your code isn't PDO. Or, are you asking how to do it with PDO? — Funk Forty Niner, Apr 17 '14 at 17:41
One thing to point out is this really won't be very efficient under any sort of load with any sort of large data set. You'll be doing a full table scan each query. You might be better off looking at actual full text search engines that can do this sort of thing incredibly fast. Though, as a learning exercise this is of course perfectly fine. — Mike Christensen, Apr 17 '14 at 17:43
@Vlada903, you're using mysqli not PDO. As suggested, any _ci encoding will be case insensitive. — Devon Bessemer, Apr 17 '14 at 17:43
SQL is SQL, no matter how you slice it. The methods/APIs used will all use the same basic syntax, whether it be mysql, mysqli, PDO, other. All that changes really is how you bind and fetch the data. — Funk Forty Niner, Apr 17 '14 at 17:46
Having looked over to the right under "Related", found this Q&A on SO that will get you started http://stackoverflow.com/q/17674369/ — Funk Forty Niner, Apr 17 '14 at 17:47
@Fred-ii- Ok, but what's the fastest way to search through the database to find title with MySQLi? — valek, Apr 17 '14 at 17:47
Fastest way? I'd say try and limit your search to specific columns. Am sure there are other methods, but as far as I know (so far), would be one way of avoiding having to go through multiple columns. — Funk Forty Niner, Apr 17 '14 at 17:49
@Fred-ii- Actually, my search is limited only to the 'title' column so that's not a problem. Thanks anyway! — valek, Apr 17 '14 at 17:51

Devon Bessemer · Accepted Answer · 2014-04-17T17:54:44.133

1

If you are asking how to do this in PDO, it is fairly similar. You can bind the param in the execute() function to avoid using bindParam() also.

$db = new PDO('mysql:host=localhost;dbname=testdb;charset=utf8', 'username', 'password');
$sql="SELECT id, title, year, front_image, description FROM a358_filmovi WHERE title LIKE ?";
$sl=$db->prepare($sql);
$sl->execute(array('%'.$_GET['search'].'%');

Should work.

Instead of using SQL or PHP to do a case insensitive search, your best to change the column encoding to _ci (ci stands for case insensitive) in the schema.

edited Apr 17 '14 at 17:54

answered Apr 17 '14 at 17:38

Devon Bessemer

34,461
9
69
95

Won't this still be case-sensitive? – Mike Christensen Apr 17 '14 at 17:38
what is the difference? – Jason OOO Apr 17 '14 at 17:38
I've updated it specifying more about PDO and column encoding. – Devon Bessemer Apr 17 '14 at 17:48
@Devon Encoding changed and now it works either with mysqli or pdo but what if I search for 'harry hallows'? It will return blank result although I have a record with a title 'Harry Potter....Hallows: Part 1'. How to find these two word if they are not one next to other? – valek Apr 17 '14 at 17:56
1

@Vlada903 Good question. I'm not sure how you would do that with SQL, I'm sure there is a way, especially with full text. With PHP, I could think of splitting the words with `explode(' ')` and adding a `LIKE %word% AND` for every word. – Devon Bessemer Apr 17 '14 at 18:01
1

@Vlada903 - The `LIKE` operator simply doesn't allow fuzzy searching like this. You need to be using a full text search engine. This is why they were invented. – Mike Christensen Apr 17 '14 at 18:03

Searching MySQL database

1 Answers1