Using LIKE with multiple WHERE clauses

Question

I'm fairly new to PHP and database management, but I'm picking it up pretty quickly. I'm trying to create a search bar, I have the searchbar working it just doesn't quite return the results I would like. Essentially, I want to look for a film in my database based on its title (name), subtitle and release date. Here's the code I currently have:

if(isset($_POST['search'])){
    $resultArray = '';
    $searchTerm = $_POST['searchTerm']; 
    if($getSearchResults = $conn->query("SELECT type, name, subtitle, release_date, poster_url, slug FROM media WHERE (name OR subtitle OR release_date) LIKE '%$searchTerm%' LIMIT 5")){   
        $resultArray = array();
        while($data = $getSearchResults->fetch_assoc()){            
            $resultArray[] = $data;                 
        }
    }else{
        echo 'No Data';
    }
    echo json_encode($resultArray); 
}

This (likely obviously) doesn't work. If the field is empty the returned search results are all the films without a subtitle because that field is empty. However, if I type in a film name, like Avengers, or a release date, like 2018, nothing comes up. It literally only works for the blank fields for some reason?

Ultimately I'd just like your help getting this to return results LIKE the search term, I can live with it searching for blank fields, but if you have a solution for that too then - great!

Thanks

You've got an answer of MHewison, however, pay attention that your query is vulnerable to SQL injection — MyLibary, Feb 23 '19 at 22:23

score 1 · Accepted Answer · answered Feb 23 '19 at 22:22

1

I believe you need to do the following.

SELECT type, name, subtitle, release_date, poster_url, slug 
  FROM media 
    WHERE name LIKE '%$searchTerm%'
    OR subtitle LIKE '%$searchTerm%'
    OR release_date LIKE '%$searchTerm%'
LIMIT 5

answered Feb 23 '19 at 22:22

MHewison

846
10
17

2

I should be noted that `LIMIT` without `ORDER BY` does not really make sense, as it will not produce predictable results. – GMB Feb 23 '19 at 22:25
Hi, I tried this, and also added ORDER BY as GMB suggested, but it's still not returning any results for some reason, not even the empty field results I was getting before... – Connor Handley Feb 23 '19 at 22:27
1

Mind the SQL injections, crackers/hackers would love seeing this code to be used for real.. – Raymond Nijland Feb 23 '19 at 22:28
@ConnorHandley : this query should give you the results you want. If not, then you have another issue out of the SQL code. – GMB Feb 23 '19 at 22:30
@GMB using `ORDER BY` on a non unique column can also give predictable results for that matter, ideally you should `ORDER BY , ` – Raymond Nijland Feb 23 '19 at 22:31
1

Hey, you were right, it was my tacked-on ORDER BY that was breaking it. This was the correct answer and my search function now works. I'm going to try a few ORDER BY options and try to get most relevant results, starting with your suggestion @RaymondNijland . Thanks for all your help! re: the sql injections, do you have some documentation for this so I can look into it, I'm not sure what the risks are or how to counter such a thing. – Connor Handley Feb 23 '19 at 22:38
2

" I'm not sure what the risks are" Well [SQL injection example (post off me)](https://stackoverflow.com/questions/54809948/mariadb-sql-injection/54810875#54810875) "or how to counter such a thing. " see [PHP prevent SQL injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php).. Mine example is pretty "riskless" but it can be used to read data in anny table where the MySQL user has access, And or (blindly) bruteforce geuss (MySQL/Application) admin/users password hashes – Raymond Nijland Feb 23 '19 at 22:40

Using LIKE with multiple WHERE clauses

1 Answers1