Selecting From a MySQL Table where multiple conditions (rows) are found in a different table

Question

I am creating a job search engine, which will support advanced search.

What I want to do is allow someone to search for "sample" and then set payment type to "hour" as a criteria. This would then only show the Sample technical job.

The code below works and outputs the expected result

$result = mysqli_query($con,"SELECT * FROM tnqj3_content LEFT JOIN tnqj3_fieldsattach_values ON tnqj3_fieldsattach_values.articleid = tnqj3_content.id WHERE tnqj3_content.title LIKE '%$q_keywords%' AND tnqj3_fieldsattach_values.value='hour'

However I want to add multiple criteria so they can also request that the pay rate is "10" (id=4). When I add "OR" on the the end of the statement it gets the result twice

$result = mysqli_query($con,"SELECT * FROM tnqj3_content LEFT JOIN tnqj3_fieldsattach_values ON tnqj3_fieldsattach_values.articleid = tnqj3_content.id WHERE tnqj3_content.title LIKE '%$q_keywords%' AND tnqj3_fieldsattach_values.value='hour' OR tnqj3_fieldsattach_values.value='10'

If I change OR to AND then no result is found. How can I set it so it fetches the row once if both criteria are found?

For context this is the code: (Ps. it is all being sanitized):

// Get search paramaters
$q_keywords = $_GET['q_keywords'];

$result = mysqli_query($con,"SELECT * FROM tnqj3_content LEFT JOIN tnqj3_fieldsattach_values ON tnqj3_fieldsattach_values.articleid = tnqj3_content.id WHERE tnqj3_content.title LIKE '%$q_keywords%' AND tnqj3_fieldsattach_values.value='hour' OR tnqj3_fieldsattach_values.value='10' ");

while($row = mysqli_fetch_array($result))
  {
      echo "<div class='search-result'><a href='/index.php?option=com_content&view=article&id=" . $row['id'] . "' class='search-result-title'>" . $row['title'] . "</a>" . $row['introtext'];

      echo "</div>";
  }

Answer 1

Try this:

SELECT * 
FROM tnqj3_content 
JOIN (
  SELECT articleid FROM tnqj3_fieldsattach_values
  GROUP BY articleid
  HAVING SUM(
    CASE value
     WHEN 'hour' THEN 1
     WHEN '10' THEN 1
     ELSE 0
    END 
    ) = 2
  ) t
ON t.articleid = tnqj3_content.id 
WHERE tnqj3_content.title LIKE '%$q_keywords%'

Here you have to add each of your user provided filters in the CASE clause of sql AND also give the count of your filters to compare with the SUM, so in my answer I have used 2 as I have 2 filters, one is 'hour' and second is '10.