I am using below mysql query using php to run my report for users
SET @p_yyyy_1 = ".$year[0].";
SET @p_q_1 = '".$quarter[0]."';
SET @p_yyyy_2 = ".$year[1].";
SET @p_q_2 = '".$quarter[1]."';
SET @p_yyyy_3 = ".$year[2].";
SET @p_q_3 = '".$quarter[2]."';
SET @p_yyyy_4 = ".$year[3].";
SET @p_q_4 = '".$quarter[3]."';
select pr.profile_id
, max(pr.fname) fname, max(pr.lname) lname
, max(case when p.year = @p_yyyy_1 and p.quarter = @p_q_1 then p.grade else null end) PPT_Q1
, max(case when p.year = @p_yyyy_2 and p.quarter = @p_q_2 then p.grade else null end) PPT_Q2
, max(case when p.year = @p_yyyy_3 and p.quarter = @p_q_3 then p.grade else null end) PPT_Q3
, max(case when p.year = @p_yyyy_4 and p.quarter = @p_q_4 then p.grade else null end) PPT_Q4
, max(case when b.year = @p_yyyy_1 and b.quarter = @p_q_1 then b.grade else null end) BPET_Q1
, max(case when b.year = @p_yyyy_2 and b.quarter = @p_q_2 then b.grade else null end) BPET_Q2
, max(case when b.year = @p_yyyy_3 and b.quarter = @p_q_3 then b.grade else null end) BPET_Q3
, max(case when b.year = @p_yyyy_4 and b.quarter = @p_q_4 then b.grade else null end) BPET_Q4
, max(case when bo.year = @p_yyyy_1 and bo.quarter = @p_q_1 then bo.grade else null end) BOAC_Q1
, max(case when bo.year = @p_yyyy_2 and bo.quarter = @p_q_2 then bo.grade else null end) BOAC_Q2
, max(case when bo.year = @p_yyyy_3 and bo.quarter = @p_q_3 then bo.grade else null end) BOAC_Q3
, max(case when bo.year = @p_yyyy_4 and bo.quarter = @p_q_4 then bo.grade else null end) BOAC_Q4
, max(case when f.year = @p_yyyy_1 and f.quarter = @p_q_1 then f.grade else null end) FIRING_Q1
, max(case when f.year = @p_yyyy_2 and f.quarter = @p_q_2 then f.grade else null end) FIRING_Q2
, max(case when f.year = @p_yyyy_3 and f.quarter = @p_q_3 then f.grade else null end) FIRING_Q3
, max(case when f.year = @p_yyyy_4 and f.quarter = @p_q_4 then f.grade else null end) FIRING_Q4
from profile_header pr
cross join (
select @p_yyyy_1 yyyy, @p_q_1 q
union select @p_yyyy_2, @p_q_2
union select @p_yyyy_3, @p_q_3
union select @p_yyyy_4, @p_q_4
) yq
left join ppt_header ph on ph.profile_id = pr.profile_id
and ph.delete_flag = 'n'
left join ppt p on ph.ppt_header_id = p.ppt_header_id
and p.year = yq.yyyy
and p.quarter = yq.q
left join bpet_header bh on bh.profile_id = pr.profile_id
and bh.delete_flag = 'n'
left join bpet b on bh.bpet_header_id = b.bpet_header_id
and b.year = yq.yyyy
and b.quarter = yq.q
left join baoc_header boh on boh.profile_id = pr.profile_id
and boh.delete_flag = 'n'
left join baoc bo on boh.baoc_header_id = bo.baoc_header_id
and bo.year = yq.yyyy
and bo.quarter = yq.q
left join firingqt_header fh on fh.profile_id = pr.profile_id
and fh.delete_flag = 'n'
left join firing_qt f on fh.firingqt_header_id = f.firingqt_header_id
and f.year = yq.yyyy
and f.quarter = yq.q
left join lookup l1 on pr.group_id = l1.lookup_id
left join lookup l2 on pr.hit_no = l2.lookup_id
where pr.profile_id IN ($commando)
group by pr.profile_id
ORDER BY 2 ASC, 3 ASC
When I am running this query directly in phpmyadmin query tab, it's working perfectly, but when I run this query through my php file and script I get this mysql error :
MySQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET @p_q_1 = 'Q1'; SET @p_yyyy_2 = 2014; SET @p_q_2 = 'Q4'; SET @p_y' at line 2