MySQL search function join on multiple conditions

Question

I have a database with two tables studies and elements. I've made a website where users can perform search queries to check if a particular study has used particular elements. If it does, I want the code to return those studies. For example, if the users typed in "Barney Likert" in the search query, it should return 1 row because "Barney" can be found in studies and "Likert" can be found in elements. However, if I type it in it returns zero rows. If I type in "Test", it does return rows because "test" can be found in both tables. What am I doing wrong? The key part of the code is this one:

$sql = "SELECT * FROM studies join elements
                   on studies.S_Element_ID = elements.E_Element_ID 
              WHERE ((studies.Author LIKE '%$search%'
                   OR studies.Study_Name LIKE '%$search%'
                   OR studies.Abstract LIKE '%$search%') 
                AND (elements.Element_Name LIKE '%$search%'
                  OR elements.Element_Author LIKE '%$search%'
                  OR elements.Element_Source LIKE '%$search%'
                  OR elements.Element_Manipulation LIKE '%$search%'
                  OR elements.Element_Scale LIKE '%$search%'))";

As you can see, I have the values Element_ID in both, these need to match. Moreover, the keyword search needs to be found in both tables. Why does the query not return the right result?

Answer 1

At the moment you're searching for e.g. "Barney Likert" (the whole string) in both the studies and the elements columns you've specified.

You need to either have two search strings, one for searching studies, and one for searching elements. Or if your users can't be bothered to use two separate fields, then you could parse the string before it's sent to the database, split it up if it consists of two words, and execute a particular query depending on what the search string is. E.g. you could have one query for input of one word, and another query for input of two words.

You may also want to consider changing the JOIN to LEFT OUTER JOIN, otherwise you will only get results from studies that have at least one element. If you know that all studies have at least one element, then you can keep it as is, as this type of join is faster.