mysql - 如何在 Codeigniter 中运行以分号分隔的多个查询
问题描述
我有一个考勤表,如果不退出,我想在其中插入数据,如果存在则更新,在循环中查询工作正常,但需要太多时间。因此,为了减少时间,我将所有查询连接为用分号分隔的字符串。结果在 phpMyAdmin 中连接工作后得到,但在 Codeigniter 中没有,你猜为什么?提前致谢。
function set_attendance()
{
$today = date('Y-m-d');
$data = $this->input->post('member');
$str='';
foreach ($data as $value)
{
$query = $this->db->select('*')
->from('attendance')
->where('user_id',$value['user_id'])
->where('date',$today)
->get();
if($query->num_rows()>0)
{
$obj = array(
'attendance' => $value['status'],
'leavetype' => $value['leavetype'],
);
$where = "user_id = '{$value['user_id']}' AND date = '{$today}'";
$str .= $this->db->update_string('attendance', $obj, $where);
$str.=';';
}
else
{
$obj = array(
's-no' => null,
'user_id' => $value['user_id'],
'date' => $today,
'regby'=>'t',
'leavetype' => $value['leavetype'],
'attendance' => $value['status'],
);
$str .= $this->db->insert_string('attendance', $obj);
$str.=';';
}
}
$this->db->query($str);
$output = array('result'=>true);
return $output ;
}
解决方案
好的,我得到答案只需运行 php 本机 multi_query() 函数
function set_attendance()
{
$today = date('Y-m-d');
$data = $this->input->post('member');
$str='';
foreach ($data as $value)
{
$query = $this->db->select('*')
->from('attendance')
->where('user_id',$value['user_id'])
->where('date',$today)
->get();
if($query->num_rows()>0)
{
$obj = array(
'attendance' => $value['status'],
'leavetype' => $value['leavetype'],
);
$where = "user_id = '{$value['user_id']}' AND date = '{$today}'";
$str .= $this->db->update_string('attendance', $obj, $where);
$str.=';';
}
else
{
$obj = array(
's-no' => null,
'user_id' => $value['user_id'],
'date' => $today,
'regby'=>'t',
'leavetype' => $value['leavetype'],
'attendance' => $value['status'],
);
$str .= $this->db->insert_string('attendance', $obj);
$str.=';';
}
}
$link = mysqli_connect('localhost','root','','yoga_panel');
if(mysqli_multi_query($link,$str))
{
$output=array('result'=>true);
return $output;
}
}