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?
Best Answer
At the moment you're searching for e.g. "Barney Likert" (the whole string) in both the
studies
and theelements
columns you've specified.You need to either have two search strings, one for searching
studies
, and one for searchingelements
. 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
toLEFT 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.