Building dynamic sql statement in PHP

Question

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?

A questionable style... how do you want to escape the values this way? You'd need to add an array_walk call processing that `$keywords` array. But as always it is better to use parameter binding here too... — arkascha, Jan 04 '17 at 14:39

Robin Mackenzie · Accepted Answer · 2017-01-05T00:31:30.663

1

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.

edited Jan 05 '17 at 00:31

answered Jan 04 '17 at 14:42

Robin Mackenzie

18,801
7
38
56

Thousand Thanks! That helps a lot. – swapfile Jan 04 '17 at 14:57
One little Question regarding the count Clause. Within the " $sql.="count(case when shorturl = '".$key."' then 1 end) ".$key.", "; " i always have a "," in the last row. When there's no more column after that i get an error. Should i solve that with substring within the $sql. or is there a better way ? – swapfile Jan 04 '17 at 16:40
1

Sure - should have done that in the first place. I added a line in the sample: `$sql=rtrim($sql, ", ")." ";` HTH – Robin Mackenzie Jan 05 '17 at 00:31

Building dynamic sql statement in PHP

1 Answers1