如何在编码点火器中使用 mysql substring_index 计算 JSON 格式数据的总和

Question

我有 3 行每个员工的月薪详细信息 4 月、5 月、6 月。Payslip_earnings列以 JSON 格式保存详细信息，例如

{"basicpay":40000,"hra":16000,..etc }

我可以在phpmyadmin中单独执行查询时获取json数据变量的总和

sum(substring_index ( substring_index (payslip_earnings,'{"basicpay":',-1),'," ',1) ) as basic.

面对空白页错误/语法错误，同时在代码编辑器中选择基本内部 模型页

$query = $this->db->query("SELECT payslip.payslip_empcode, 
sum(substring_index ( substring_index (payslip_earnings,'{"basicpay":',-1),'," ',1) ) as basic
FROM payslip LEFT OUTER JOIN employee_official_master ON
payslip.payslip_empcode=employee_official_master.employee_code 
WHERE payslip.payslip_fyear = '2018' and  
payslip.payslip_empcode IN ('MAR015','MAR010') AND 
( payslip_month = 'April' OR payslip_month = 'May' OR payslip_month = 'June') 
GROUP BY payslip.payslip_empcode "
);

如何在代码点火器模型页面中实现相同的目标？

Answer 1

Try this One..
$colname  = '{"basicpay":' ;            
$seperator = ',';  
$query = $this->db->query("SELECT payslip.payslip_empcode, 
sum(substring_index (substring_index (payslip.payslip_earnings,'".$colname."',-1),'".$seperator."',1) ) as basicpay
FROM payslip LEFT OUTER JOIN employee_official_master ON
payslip.payslip_empcode=employee_official_master.employee_code 
WHERE payslip.payslip_fyear = '2018' and  
payslip.payslip_empcode IN ('MAR015','MAR010') AND 
( payslip_month = 'April' OR payslip_month = 'May' OR payslip_month = 'June') 
GROUP BY payslip.payslip_empcode "
);