php - 如何在Codeigniter中使用mysql substring_index计算JSON格式数据的总和

我有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.

面对空白页在codeiginter模型页中选择基本内部时发生错误/语法错误

$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 "
           );

如何在codeigniter模型页面中实现相同？

最佳答案

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 "
           );

关于php - 如何在Codeigniter中使用mysql substring_index计算JSON格式数据的总和，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52109554/