search query with join shows all rows when searched string is empty

Question

I have a query to search in two tables for a vacancy.

The variables for this query are send with a form that has multiple inputs/selects. One is a text input for the title of a vacancy and the other is a dropdown with all categories a vacancy can belong to.

When I leave the text input empty and select only a category, I get all vacancies not just the ones from the selected category.

My query:

$functie = $_POST['functie'];
$branche = $_POST['branche'];
$regio = $_POST['regio'];

$search = "
SELECT cnt.title, cnt.alias, cnt.images, cnt.introtext, cnt.catid, cat.title, cat.alias
FROM snm_content cnt
LEFT JOIN snm_categories cat
ON cat.id = cnt.catid
WHERE ('".$functie."' ='' OR cnt.title LIKE '%".$functie."%')
OR ('".$branche."' ='' OR cat.title LIKE '%".$branche."%')
";

If I echo the query without typing in the text input this is what I get:

SELECT cnt.title, cnt.alias, cnt.images, cnt.introtext, cnt.catid, cat.title, cat.alias
FROM snm_content cnt
LEFT JOIN snm_categories cat
ON cat.id = cnt.catid
WHERE ('' ='' OR cnt.title LIKE '%%')
OR ('logistiek' ='' OR cat.title LIKE '%logistiek%')

snm_content are the vacancies and snm_categories are the categories.

How can I show only the vacancies belonging to the category that was selected?

@MadhurBhaiya Yes I forgot, I'll accept now. – twan Nov 23 '18 at 13:43 — twan, Nov 23 '18 at 13:43

score 1 · Accepted Answer · answered Nov 22 '18 at 09:41

Note that your code is open to SQL injection related attacks. Please learn to use Prepared Statements

Now, we will need to generate the WHERE part of the query dynamically. We can use !empty() function to check whether an input filter value is not empty, and then add its condition to the query dynamically.

$functie = $_POST['functie'];
$branche = $_POST['branche'];
$regio = $_POST['regio'];

$search = "
SELECT cnt.title, cnt.alias, cnt.images, cnt.introtext, cnt.catid, cat.title, cat.alias
FROM snm_content cnt
LEFT JOIN snm_categories cat
ON cat.id = cnt.catid ";

// Collect all the where conditions in an array
$whr = array();

// check if $functie has some value in input filter
if (!empty($functie)) {
    $whr[] = "cnt.title LIKE '%" . $functie . "%'";
}

// check if $branche has some value in input filter
if (!empty($branche)) {
    $whr[] = "cat.title LIKE '%" . $branche . "%'";
}

$where_sql = '';
// Prepare where part of the SQL
if (!empty($whr)) {

    $where_sql = ' WHERE ' . implode(' OR ', $whr);
}

// Append to the original sql
$search .= $where_sql;

search query with join shows all rows when searched string is empty

1 Answers1

Linked