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>";
}
Try this:
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 theSUM
, so in my answer I have used2
as I have 2 filters, one is 'hour' and second is '10.