I have a database with two tables
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?