Martin Fric Martin Fric - 4 months ago 12
SQL Question

PHP SQL not passing sum parameters

i have following script

select left(m.last_local_call_time,10) as date
,sum(case when m.status="ZP" and o.el_fa="ano" then 1 else 0 end) as elfa
,sum(case when m.status="ZP" then 1 else 0 end) as zaujem
,sum(case when m.status="NI" then 1 else 0 end) as nezaujem
,sum(case when m.status="INAOS" then 1 else 0 end) as inaos
,sum(case when m.status="NK" then 1 else 0 end) as nk
,sum(case when m.status="ADC" then 1 else 0 end) as adc
,sum(case when m.status="CBHOLD" or m.status="CALLBK" then 1 else 0 end) as cb
,sum(case when m.status="N" or m.status="NA" then 1 else 0 end) as na
,sum(case when m.status="A" or m.status="B" then 1 else 0 end) as odk
,sum(case when m.status="ZP" and o.HBO_balik="on" and o.Zaujem_o_sluzbu_HBO_na_="záujem o službu – 24 mesiacov" then 1 else 0 end) as hbo24
,sum(case when m.status="ZP" and o.HBO_balik="on" and o.Zaujem_o_sluzbu_HBO_na_="záujem o službu – bez viazanosti" then 1 else 0 end) as hbobv
,sum(case when m.status="ZP" and o.Maxi_____viazanost_na_24_mesiacov="on" and o.Zaujem_o_maxi="záujem o službu – 24 mesiacov" then 1 else 0 end) as maxi24
,sum(case when m.status="ZP" and o.Maxi_____viazanost_na_24_mesiacov="on" and o.Zaujem_o_maxi="záujem o službu – bez viazanosti" then 1 else 0 end) as maxibv
,sum(case when m.status="ZP" and o.extra_sport_plus="on" and o.zaujem_o_extra_sport_plus="záujem o službu – 24 mesiacov" then 1 else 0 end) as sp24
,sum(case when m.status="ZP" and o.extra_sport_plus="on" and o.zaujem_o_extra_sport_plus="záujem o službu – bez viazanosti" then 1 else 0 end) as spbv
,sum(case when m.status="ZP" and o.extra_hu_plus="on" and o.zaujem_o_extra_hu_plus="záujem o službu – bez viazanosti" then 1 else 0 end) as hpbv
from db_DIGI_upsell_20160711_ o
join vicidial_list m on o.lead_id = m.lead_id where m.last_local_call_time is not NULL
group by left(m.last_local_call_time,10) DESC;


In heidiSQL everything looks fine, but when i run it with my php script

$mysqli = new mysqli('ip', 'user', 'pw', 'db');
if ($mysqli->connect_error) {
die('Connect Error (' . $mysqli->connect_errno . ') '
. $mysqli->connect_error);
}
$result =mysqli_query($mysqli, 'select left(m.last_local_call_time,10) as date
,sum(case when m.status="ZP" and o.el_fa="ano" then 1 else 0 end) as elfa
,sum(case when m.status="ZP" then 1 else 0 end) as zaujem
,sum(case when m.status="NI" then 1 else 0 end) as nezaujem
,sum(case when m.status="INAOS" then 1 else 0 end) as inaos
,sum(case when m.status="NK" then 1 else 0 end) as nk
,sum(case when m.status="ADC" then 1 else 0 end) as adc
,sum(case when m.status="CBHOLD" or m.status="CALLBK" then 1 else 0 end) as cb
,sum(case when m.status="N" or m.status="NA" then 1 else 0 end) as na
,sum(case when m.status="A" or m.status="B" then 1 else 0 end) as odk
,sum(case when m.status="ZP" and o.HBO_balik="on" and o.Zaujem_o_sluzbu_HBO_na_="záujem o službu – 24 mesiacov" then 1 else 0 end) as hbo24
,sum(case when m.status="ZP" and o.HBO_balik="on" and o.Zaujem_o_sluzbu_HBO_na_="záujem o službu – bez viazanosti" then 1 else 0 end) as hbobv
,sum(case when m.status="ZP" and o.Maxi_____viazanost_na_24_mesiacov="on" and o.Zaujem_o_maxi="záujem o službu – 24 mesiacov" then 1 else 0 end) as maxi24
,sum(case when m.status="ZP" and o.Maxi_____viazanost_na_24_mesiacov="on" and o.Zaujem_o_maxi="záujem o službu – bez viazanosti" then 1 else 0 end) as maxibv
,sum(case when m.status="ZP" and o.extra_sport_plus="on" and o.zaujem_o_extra_sport_plus="záujem o službu – 24 mesiacov" then 1 else 0 end) as sp24
,sum(case when m.status="ZP" and o.extra_sport_plus="on" and o.zaujem_o_extra_sport_plus="záujem o službu – bez viazanosti" then 1 else 0 end) as spbv
,sum(case when m.status="ZP" and o.extra_hu_plus="on" and o.zaujem_o_extra_hu_plus="záujem o službu – bez viazanosti" then 1 else 0 end) as hpbv
from db_DIGI_upsell_20160711_ o
join vicidial_list m on o.lead_id = m.lead_id where m.last_local_call_time is not NULL
group by left(m.last_local_call_time,10) DESC;');
while ($row = mysqli_fetch_assoc($result))
{ echo $row['date'];
echo $row['hbo24'];
echo $row['hbobv'];
echo $row['maxi24'];
echo $row['maxibv'];
echo $row['sp24'];
echo $row['spbv'];
echo $row['hpbv'];
}


everything except date is '0' (date is normal '2016-07-12','2016-07-13')

In HeidiSQL i have numbers.

Can anyone explain it please?

Thanks in advance

UPDATE 2



"date" "elfa" "zaujem" "nezaujem" "inaos" "nk" "adc" "cb" "na" "odk" "hbo24" "hbobv" "maxi24" "maxibv" "sp24" "spbv" "hpbv"
"2016-07-13" "16" "43" "115" "2" "2" "86" "91" "207" "22" "4" "6" "5" "7" "1" "2" "19"
"2016-07-12" "24" "53" "133" "7" "5" "0" "41" "5" "18" "9" "4" "2" "13" "1" "1" "24"


This is result from HEIDI SQL. In PHP everywhere except date there is 0;

Answer

Ok I have sloved it. It was about charset...

i just made this :

$mysqli->set_charset("utf8"); 

And everything works. Since i had utf8 set in php, when i made echo of query it looked ok. But then after some researches i finished with this.

Thanks to all.

Comments