Wsrtjtyk

I have a query to search for three things, a keyword, a category and a region (it's for vacancies). Everything works except for the region since I get that column from my database with an aliased name.

My code now:

$functie = $_POST['functie'];

$branche = $_POST['branche'];

$regio = $_POST['regio'];



$search = "

SELECT cnt.title as content_title, cnt.alias as content_alias, cnt.images, cnt.introtext, cnt.catid, cat.title as cat_title, cat.alias as cat_alias,

MAX(case when f.field_id = 2 then f.value end) as regio

FROM snm_content cnt

LEFT JOIN snm_categories cat

ON cat.id = cnt.catid

LEFT JOIN snm_fields_values f

ON cnt.id = f.item_id

";

// 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 . "%'";

}



if (!empty($regio)) {

    $whr = "f.value LIKE '%" . $regio . "%'";

}



// 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;

$searchcon = $conn->query($search);

But above code is not working for the region.

However this is the endresult query and these are the results I get with it:

SELECT cnt.title as content_title, cnt.alias as content_alias, cnt.images, cnt.introtext, cnt.catid, cat.title as cat_title, cat.alias as cat_alias,

MAX(case when f.field_id = 2 then f.value end) as regio

FROM snm_content cnt

LEFT JOIN snm_categories cat

ON cat.id = cnt.catid

LEFT JOIN snm_fields_values f

ON cnt.id = f.item_id

WHERE cnt.title LIKE '%vrachtwagen%'

regio is what I want to show results for.

But WHERE regio LIKE '%" . $regio . "%'"; is not working, it says unknown column regio because it is an alias. How can I only show all results belonging to the region that was posted? For example above image, show only that row when Drenthe is the posted region.

We cannot use an aliased calculation expression in the Select clause inside the Where clause. Remember that Select is evaluated after Where, so expression evaluation happens afterwards.

You will have to specify the complete expression again in the Where clause.

// check if $regio has some value in input filter

if (!empty($regio)) {

    $whr = "MAX(case when f.field_id = 2 then f.value end) LIKE '%" . $regio . "%'";

}

looking to your code seems you are trying to filter ana ggregated result max(...) as regio

for filter an aggregated result you should use having

HAVING regio  like concat('%', $your_value, '%')

anyway you are using php var inside sql this your code is at risk for sqlinjecttion .. you should take a look at your db driver for prepared statement and binding param

You are also using not aggregated column in select without group by .. this is deprecated in sql, the result for the columns in unpredictable and in most recent version of mysql in not allowed.