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?

  • Before you get into performance problems, redesign the query to use FULLTEXT indexing. This will also let you simplify the query. Then, if still necessary, we can discuss "right result".
    – Rick James
    Apr 17, 2018 at 19:18

1 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.

  • It did not help. Now the query returns values if the keyword search is found in either table, but it should be found in both. If I put in 'random author name' it finds the author name in the studies table and returns those rows, however, if I put in 'random author name' and 'random element name' it returns zero results. The left outer join does not help either. Apr 11, 2018 at 11:54
  • @Vava Sorry, I misread the question. Have updated the answer now.
    – dbdemon
    Apr 11, 2018 at 12:18
  • Thank you for your answer, I will try to implement it and get back to you! Apr 11, 2018 at 12:24
  • Works like a charm. Thank you so much, been stuck on this for 4 days Apr 12, 2018 at 10:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.