SkySonny SkySonny - 1 month ago 19
PHP Question

SQL string concatenation with if statement

I have simple query for full text search, data comes from HTML form inputs. If statement checks or it was filled all three inputs with array_key_exists and then concatenate all strings, but if someone didn't fill first or second input and then my query WHERE part is

WHERE AND contract = $var
so in this situation AND is not needed. What is solution for my problem, how i can solve this? When AND is needed just add to query string and when didn't needed don't add.

$sql = "SELECT
slug,
title,
company,
location,
email,
street,
city,
phone,
url,
description,
image,
created_at
FROM jobs
WHERE ";

array_key_exists('paieska', $segment) ? $sql .= "MATCH(title, description) AGAINST('".urldecode($segment['paieska'])."') " : '';
array_key_exists('darbo-laikas', $segment) ? $sql .= "AND contract = '".$segment['darbo-laikas']."' " : '';
array_key_exists('miestas', $segment) ? $sql .= "AND location = '".$segment['miestas']."'" : '';

Answer

The easiest solution is to just add a 1 to the original where statement (And AND before your first $sql if statement). This will always be true, and will return all the records.

If darbo-laikas exists, your query would be ...FROM jobs WHERE 1 AND contract... which would work correctly.

$sql = "SELECT 
            slug, 
            title, 
            company, 
            location, 
            email, 
            street, 
            city, 
            phone, 
            url, 
            description, 
            image, 
            created_at 
            FROM jobs
            WHERE 1 ";

array_key_exists('paieska', $segment) ? $sql .= "AND MATCH(title, description) AGAINST('".urldecode($segment['paieska'])."') " : '';
array_key_exists('darbo-laikas', $segment) ? $sql .= "AND contract = '".$segment['darbo-laikas']."' " : '';
array_key_exists('miestas', $segment) ? $sql .= "AND location = '".$segment['miestas']."'" : '';