如何在codeigniter中使用mysql子串索引计算json格式数据的总和

kqhtkvqz 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(331)

我有3行每个员工的月薪明细，分别为4月、5月、6月。payslip\u earnings列以类似json的格式保存了详细信息

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

我可以在phpmyadmin中使用

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

在codeiginter model page中选择basic inside时出现空白页错误/语法错误

$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模型页？

mysql php phpmyadmin codeigniter

来源：https://stackoverflow.com/questions/52109554/how-to-calculate-sum-of-json-formatted-data-using-mysql-substring-index-in-codei

1条答案

按热度按时间

zour9fqk1#

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

赞(0）回复(0）举报 2021-06-20

我来回答

如何在codeigniter中使用mysql子串索引计算json格式数据的总和

1条答案

相关问题

热门标签

最新问答