i have that part of code and want to build a dynamic SQL Statement:
//Build IN Clause
$sql_IN="'".implode("','", $keywords)."'";
//Build COUNT Clause
//tbd
//Get Clicks from YOURLS LOG
$sql_get_clicks="
SELECT
count(case when shorturl ='u0lg' then 1 end) u0lg,
count(case when shorturl ='3a8h' then 1 end) 3a8h,
count(case when shorturl ='abcd' then 1 end) abcd,
DATE_FORMAT((click_time),'%d.%m.%Y') Datum
FROM
`yourls_log`
WHERE
DATE(click_time) BETWEEN '20170102' AND '20170104' AND
shorturl IN (".$sql_IN.")
Group By Datum";
The variable $keywords
is an array with keywords. The keywords are used in the IN Clause and must also be used within the "count case part". The quantity of keywords within the $keywords Array is variable. Any tips how i can build the count(case..
in an easy and good programming style?
You can iterate the $keywords
array and put each value into a clause that you append after the SELECT
and before the FROM
etc.
$keywords = array("foo", "bar", "baz");
$inClause = "'".implode("','", $keywords)."'";
$sql = "SELECT ";
foreach($keywords as $key) {
$sql.="count(case when shorturl = '".$key."' then 1 end) ".$key.", ";
}
$sql=rtrim($sql, ", ")." ";
$sql.= "FROM `your_logs`
WHERE
DATE(click_time) BETWEEN '20170102' AND '20170104' AND
shorturl IN (".$inClause.")
Group By Datum";
Where a var_dump($sql);
gives:
string(301) "SELECT count(case when shorturl = 'foo' then 1 end) foo, count(case when shorturl = 'bar' then 1 end) bar, count(case when shorturl = 'baz' then 1 end) baz, FROM
your_logs
WHERE DATE(click_time) BETWEEN '20170102' AND '20170104' AND shorturl IN ('foo','bar','baz') Group By Datum"
Regards programming style - you should most definitely look into prepared statements.