How to implode these conditions to achieve this query structure?

Question

Below is my working query that gets into action after a form submit. My query works only if all textboxes are filled, so currently everything is required.

WORKING QUERY

SELECT behaviour.hash, 
       Sum(behaviour.timespent) AS timeSpent, 
       new_table.percentile_rank, 
       Count(*) AS total 
FROM   behaviour, 
       audience, 
       new_table 
WHERE  ( $url ) 
       AND behaviour.timestamp >= Date_sub(Curdate(), INTERVAL $last_visit day) AND behaviour.timestamp < Date_add(Curdate(), INTERVAL 1 day) 
       AND behaviour.hash = audience.hash 
       AND behaviour.hash = new_table.hash 
       AND audience.country = '$from_country' 
GROUP  BY behaviour.hash 
HAVING Count(*) >= $more_than 
       AND timespent >= $time_spent 
       AND new_table.percentile_rank >= $lead_scoring 

What I want to achieve is not to require the user to fill all the textboxes in order to submit, but only those he prefers. So I built the following, but it has some errors.

My problem is that my query has a having clause so not every condition is connected with AND as I have for now (look below my code). So, the first of the $more_than or $time_spent or $lead_scoring textboxes that submitted, it must have HAVING instead of AND.

How to edit my code in order to achieve this "special conditions" ?

MY CODE

$url= 'url="'.implode('" OR url="', $vals).'"';

$conditions = array();

if (!empty($last_visit)) $conditions[] = "behaviour.TIMESTAMP >= DATE_SUB( CURDATE( ) , INTERVAL '".$last_visit."' DAY) AND behaviour.TIMESTAMP < DATE_ADD( CURDATE( ) , INTERVAL 1 DAY ) ";
if (!empty($from_country)) $conditions[] = "audience.country = '".$from_country."'";
if (!empty($more_than)) $conditions[] = "COUNT( * ) >= '".$more_than."'"; 
if (!empty($time_spent)) $conditions[] = "timeSpent >= '".$time_spent."'";
if (!empty($lead_scoring)) $conditions[] = "new_table.percentile_rank >= '".$lead_scoring."'";


$conditionString = implode(' AND ', $conditions);


$sql = "SELECT behaviour.hash, 
       Sum(behaviour.timespent) AS timeSpent, 
       new_table.percentile_rank, 
       Count( * ) AS total 
FROM   behaviour, 
       audience, 
       new_table 
WHERE  ($url) AND ".$conditionString;

CURRENT OUTPUT

In the example below, all textboxes except more_than have been filled. The thing is that instead AND timespent >= '20' should be HAVING timespent >= '20'

SELECT behaviour.hash, 
       SUM(behaviour.timespent) AS timeSpent, 
       new_table.percentile_rank, 
       Count(*) AS total 
FROM   behaviour, 
       audience, 
       new_table 
WHERE  ( url = "/10369" ) 
       AND behaviour.timestamp >= Date_sub(Curdate(), interval '3' day) 
       AND behaviour.timestamp < Date_add(Curdate(), interval 1 day) 
       [missing]     AND behaviour.hash = audience.hash
       [missing]     AND behaviour.hash = new_table.hash
       AND audience.country = 'it' 
       [missing]     GROUP BY behaviour.hash
       [wrong]       AND timespent >= '20' ////// it should be HAVING /////
       AND new_table.percentile_rank >= '30'

Answer 1

First, you must ensure SQL injection is not possible. To do that, lets use PDO.

Next, to solve your actual problem, you simple need to create two lists with conditions. One with the conditions you want to have in the WHERE part of the query, and one with the conditions that need to go in the HAVING part of the query.

    $pdo = new PDO(/* See http://php.net/manual/en/pdo.construct.php */);

    $whereConditions = [];
    $havingConditions = [];
    $parameters = [];

    if (!empty($last_visit)) {
        $whereConditions[] = "behaviour.TIMESTAMP >= DATE_SUB( CURDATE( ) , INTERVAL :last_visit DAY) AND behaviour.TIMESTAMP < DATE_ADD( CURDATE( ) , INTERVAL 1 DAY ) ";
        $parameters['last_visit'] = $last_visit;
    }
    if (!empty($from_country)) {
        $whereConditions[] = "audience.country = :from_country";
        $parameters['from_country'] = $from_country;
    }
    if (!empty($more_than)) {
        $havingConditions[] = "COUNT( * ) >= :more_than";
        $parameters['more_than'] = $more_than;
    }
    if (!empty($time_spent)) {
        $havingConditions[] = "timeSpent >= :time_spent";
        $parameters['time_spent'] = $time_spent;

    }
    if (!empty($lead_scoring)) {
        $havingConditions[] = "new_table.percentile_rank >= :lead_scoring";
        $parameters['lead_scoring'] = $lead_scoring;
    }

    if (count($vals)) {
        $escapedUrlList = implode(', ', array_map(function ($url) use ($pdo) {
            return $pdo->quote($url);
        }, $vals));
        $whereConditions[] = "url IN($escapedUrlList)";
    }

    $whereClause = count($whereConditions) ? ' AND ' . implode(' AND ', $whereConditions) : '';
    $havingClause = count($havingConditions) ? ' HAVING ' . implode(' AND ', $havingConditions) : '';

    $statement = $pdo->prepare("
        SELECT behaviour.hash, 
            Sum(behaviour.timespent) AS timeSpent, 
            new_table.percentile_rank, 
            Count(*) AS total 
        FROM behaviour, 
            audience, 
            new_table 
        WHERE behaviour.hash = audience.hash 
            AND behaviour.hash = new_table.hash 
            {$whereClause}
        GROUP  BY behaviour.hash
        {$havingClause}
    ");

    $result = $statement->execute($parameters);

Answer 2

Here's a bit "tricky" approach (looks clean though) that is using prepared statements. I've added some general purpose "features" in case of future changes. Read the comments with explanations (will be more convenient this way I think):

//assume established PDO connection - example:
try {
    $pdo = new PDO("mysql:dbname={$database_name};host=localhost", $user, $password);
} catch (PDOException $e) {
    echo 'Connection failed: ' . $e->getMessage();
}

//static: conditional strings without parameters check (no keys required)
//conditional: assoc - keys should match both query placeholders and variable names
$static_where = [];
$optional_where = [
    'last_visit'   => 'behaviour.TIMESTAMP >= DATE_SUB(CURDATE(), INTERVAL :last_visit DAY) AND behaviour.TIMESTAMP < DATE_ADD(CURDATE(), INTERVAL 1 DAY)',
    'from_country' => 'audience.country = :from_country'
];

$static_having = [];
$optional_having = [
    'more_than'    => 'COUNT(*) >= :more_than',
    'time_spent'   => 'timeSpent >= :time_spent',
    'lead_scoring' => 'new_table.percentile_rank >= :lead_scoring'
];

//params: query parameters array - assigned manually + resolved from optional variables
$params = [];

//resolve condition from $urls array
if (count($urls) == 1) {
    $static_where[] = 'url = :url';
    $params['url'] = reset($urls);
} else if (!empty($urls)) {
    foreach ($urls as $idx => $url) {
        $params['url' . $idx] = $url;
    }
    $static_where[] = 'url IN(:' . implode(', :', array_keys($params)) . ')';
}

//filtering existing params used in query
//empty() is not a good idea for general purpose though,
//because some valid values might be recognised as empty (int 0, string '0')
$params += array_filter(
    compact(array_keys($optional_where), array_keys($optional_having)),
    function ($value) { return !empty($value); }
);

//concatenating conditional strings
//with corresponding params that weren't filtered out
//or these without params (static)
$where_clause = implode(' AND ', $static_where + array_intersect_key($optional_where, $params));
$having_clause = implode(' AND ', $static_having + array_intersect_key($optional_having, $params));

//don't need clauses without conditions - same as if (!empty($where)) {...}
empty($where_clause) or $where_clause = 'WHERE ' . $where_clause;
empty($having_clause) or $having_clause = 'HAVING ' . $having_clause;

$sql = "SELECT 
          behaviour.hash,
          Sum(behaviour.timespent) AS timeSpent,
          new_table.percentile_rank,
          Count( * ) AS total 
        FROM behaviour,
        INNER JOIN audience ON behaviour.hash = audience.hash,
        INNER JOIN new_table ON behaviour.hash = new_table.hash 
        {$where_clause}
        GROUP BY behaviour.hash 
        {$having_clause}";

//PDO part
$query = $pdo->prepare($sql);
$result = $query->execute($params);
//...

Answer 3

Here is a less complicated way using string concatenation instead of implode. The "trick" is to start the conditions with 1=1. So every following condition can begin with AND.

$andWhere  = '';
$andHaving = '';
$params = [];


if (!empty($last_visit)) {
    $andWhere .= " AND behaviour.TIMESTAMP >= CURDATE() - INTERVAL :last_visit DAY AND behaviour.TIMESTAMP < CURDATE() + INTERVAL 1 DAY";
    $params['last_visit'] = $last_visit;
}
if (!empty($from_country)) {
    $andWhere .= " AND audience.country = :from_country";
    $params['from_country'] = $from_country;
}
if (!empty($more_than)) {
    $andHaving .= " AND COUNT( * ) >= :more_than";
    $params['more_than'] = $more_than;
}
if (!empty($time_spent)) {
    $andHaving .= " AND timeSpent >= :time_spent";
    $params['time_spent'] = $time_spent;
}
if (!empty($lead_scoring)) {
    $andHaving .= " AND new_table.percentile_rank >= :lead_scoring";
    $params['lead_scoring'] = $lead_scoring;
}

$urlPlaceholders = [];
foreach ($vals as $key => $val) {
    $urlPlaceholders[] = ":url_$key";
    $params["url_$key"] = $val;
}
if (count($vals) > 0) {
    $inUrl = implode(',', $urlPlaceholders);
    $andWhere .= " AND url IN ($inUrl)";
}

$sql = "
    SELECT behaviour.hash, 
           Sum(behaviour.timespent) AS timeSpent, 
           new_table.percentile_rank, 
           Count(*) AS total 
    FROM   behaviour 
    JOIN   audience  ON behaviour.hash = audience.hash
    JOIN   new_table ON behaviour.hash = new_table.hash
    WHERE  1=1 {$andWhere}
    GROUP  BY behaviour.hash 
    HAVING 1=1 {$andHaving}
";

#var_export($sql);
#var_export($params);

$sth = $dbh->prepare($sql);
$sth->execute($params);
$data = $sth->fetchAll(PDO::FETCH_ASSOC);

#var_export($data);

Having sample data like

$last_visit   = '';
$from_country = 'UK';
$more_than    = '5';
$time_spent   = '3';
$lead_scoring = '';
$vals = ['u1', 'u2'];

You would get the following query:

SELECT behaviour.hash, 
       Sum(behaviour.timespent) AS timeSpent, 
       new_table.percentile_rank, 
       Count(*) AS total 
FROM   behaviour 
JOIN   audience  ON behaviour.hash = audience.hash
JOIN   new_table ON behaviour.hash = new_table.hash
WHERE  1=1  AND audience.country = :from_country AND url IN (:url_0,:url_1)
GROUP  BY behaviour.hash 
HAVING 1=1  AND COUNT(*) >= :more_than AND timeSpent >= :time_spent

with these bindings:

array (
  'from_country' => 'UK',
  'more_than' => '5',
  'time_spent' => '3',
  'url_0' => 'u1',
  'url_1' => 'u2',
)

Demo on rextester.com

Answer 4

If having is the only problem why not splitting it into different block like this:

$conditions = array();

if (!empty($last_visit)) $conditions[] = "behaviour.TIMESTAMP >= DATE_SUB( CURDATE( ) , INTERVAL '".$last_visit."' DAY) AND behaviour.TIMESTAMP < DATE_ADD( CURDATE( ) , INTERVAL 1 DAY ) ";
if (!empty($from_country)) $conditions[] = "audience.country = '".$from_country."'";


$conditionString = implode(' AND ', $conditions);

$conditions_having = array();
if (!empty($more_than)) $conditions_having[] = "COUNT( * ) >= '".$more_than."'"; 
if (!empty($time_spent)) $conditions_having[] = "timeSpent >= '".$time_spent."'";
if (!empty($lead_scoring)) $conditions_having[] = "new_table.percentile_rank >= '".$lead_scoring."'";

$conditionString .= " GROUP  BY behaviour.hash" 
if(count($conditions_having))
$conditionString .= " HAVING ".implode(' AND ', $conditions_having); 

$sql = "SELECT behaviour.hash, 
       Sum(behaviour.timespent) AS timeSpent, 
       new_table.percentile_rank, 
       Count( * ) AS total 
FROM   behaviour, 
       audience, 
       new_table 
WHERE  ($url) AND ".$conditionString;