我使用MySQL与PHP, Codeigniter。我有一个问题,bluefeet在这里的帖子中回答了这个问题
我为bluefeet的第二个解决方案创建了一个存储过程。但是,当在生产环境中调用该过程时,所有其他用户都会得到错误 。命令不同步;你现在不能运行这个命令
不知道如何克服这个错误。我还尝试在调用过程后关闭连接,但是,在关闭连接之前执行了来自其他用户的查询。有解决这个问题的方法吗?
下面是我使用的存储过程
DROP PROCEDURE IF EXISTS mailbox.circle_pending_p;
CREATE PROCEDURE mailbox.`circle_pending_p`()
BEGIN
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'sum(CASE WHEN maildate = ''',
date_format(mailtime, '%e-%b'),
''' THEN 1 else 0 END) AS `',
date_format(mailtime, '%e-%b'), '`'
)
) INTO @sql
FROM circle_pending_temp
WHERE mailtime >= (select date_sub(max(mailtime), interval 8 DAY)
from circle_pending_temp);
SET @sql
= CONCAT('SELECT coalesce(email_Circle, ''Grand Total'') Circle,
max(`< 9 days`) `< 9 days`, ', @sql, ' ,
count(*) GrandTotal
from
(
select c.email_Circle,
date_format(c.mailtime, ''%e-%b'') maildate,
coalesce(o.`< 9 days`, 0) `< 9 days`
from circle_pending_temp c
left join
(
select email_Circle,
count(*) `< 9 days`
from circle_pending_temp
where mailtime <= (select date_sub(max(mailtime), interval 8 DAY)
from circle_pending_temp)
) o
on c.email_Circle = o.email_Circle
where c.mailtime >= (select date_sub(max(mailtime), interval 8 DAY)
from circle_pending_temp)
) d
group by email_Circle with rollup ');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;
我用来调用这个过程的PHP是
$db = $this->load->database('mailbox',TRUE);
$res = $db->query('Call circle_pending_p()');
echo $db->_error_message();
$db->close();
$db = $this->load->database('mailbox',TRUE);
if ($res->num_rows() > 0) {
return $res->result_array();
} else {
return 0;
}
找到答案了!似乎codeigniter的mysql驱动程序在处理存储过程时存在错误。
我在config/database文件中通过更改 将驱动程序从mysql更改为mysqli$db['default']['dbdriver'] = 'mysql';
$db['default']['dbdriver'] = 'mysqli';
Post,我修改了system/database/drivers/mysqli/mysqli_result.php文件,并添加了以下函数
function next_result()
{
if (is_object($this->conn_id))
{
return mysqli_next_result($this->conn_id);
}
}
,修改模型如下
$db = $this->load->database('mailbox',TRUE);
$qry_res = $db->query('Call circle_pending_p()');
echo $db->_error_message();
$res = $qry_res->result_array();
$qry_res->next_result();
$qry_res->free_result();
if (count($res) > 0) {
return $res;
} else {
return 0;
}
这就解决了问题!
尝试:
<?php
while($dbms->more_results() && $dbms->next_result())
{
$result = $dbms->store_result();
if(is_object($result)){ $result->free(); }
unset($result);
}
?>
在过程调用之后。MySQLi
不能调用另一个过程,而有先前的结果。在任何进一步的过程调用或查询执行之前,您应该对它们中的每个使用free()
。
来源:http://php.net/manual/en/mysqli.query.php
In file
system/database/drivers/mysqli/mysqli_result.php
添加:
function next_result()
{
if (is_object($this->conn_id))
{
return mysqli_next_result($this->conn_id);
}
}
function result($type = 'object')
{
$result = array();
if ($type == 'array') $result = $this->result_array();
else if ($type == 'object') $result = $this->result_object();
else $result = $this->custom_result_object($type);
$this->next_result();
return $result;
}
我在循环中运行存储过程时遇到了类似的问题。我使用的代码如下:
foreach ($month_list as $month_row)
{
$start_date = $month_row->adate;
$end_date = date("Y-m-d", strtotime("last day of " . date("F", strtotime($start_date)) . " " . date("Y", strtotime($start_date))));
$qry_res = $this->db->query("call staff_attendance('$start_date', '$end_date')");
$res = $qry_res->result();
$qry_res->next_result();
$qry_res->free_result();
$compiled_months[] = $res;
}
在这种情况下,我创建的临时表被报错为表已经存在。
所以,用这个方法代替:
foreach ($month_list as $month_row)
{
$start_date = $month_row->adate;
$end_date = date("Y-m-d", strtotime("last day of " . date("F", strtotime($start_date)) . " " . date("Y", strtotime($start_date))));
$compiled_months[] = $this->db->query("call staff_attendance('$start_date', '$end_date')")->result();
$this->db->close();
$this->db->initialize();
}
现在执行是完美的