我从PHP发送一个逗号分隔的列表到我的存储过程。存储过程如下:
UPDATE RolesMenus SET Enabled=1 WHERE MenuID IN(prmMenusList) AND RoleID = prmRoleID;
现在的问题是,当我通过代码传递菜单列表(prmMenusList)时,它只是更新列表中第一个元素的值。我认为,这是由于列表中的一些单引号造成的。查询可以形成如下格式:
UPDATE RolesMenus SET Enabled=0 WHERE MenuID IN('1,7,19,20,21,26') AND RoleID = 74;
我怎样才能避免这种情况?
这里我添加了CodeIgniter(PHP)代码:Public function enableMenus($selectedMenus, $roleID){
$menusList = "";
foreach($selectedMenus as $item){
$menusList .= $item.",";
}
$menusList = substr($menusList, 0,strlen($menusList)-1); // to remove the leading comma
$result=$this->db->query("call uspEnableMenus('".$menusList."',".$roleID.")");
$tempResult = $result;
$result->next_result();
return $tempResult->result();
}
我已经解决了问题。:)
刚才使用的查询如下:
UPDATE RolesMenus SET Enabled=1 WHERE FIND_IN_SET(MenuID,prmMenusList) AND RoleID = prmRoleID;
或:
UPDATE RolesMenus SET Enabled=1 WHERE FIND_IN_SET(MenuID,'1,2,3,4') AND RoleID = 1;
将prmMenusList
作为整数列表而不是字符串发送:
UPDATE RolesMenus SET Enabled=0 WHERE MenuID IN(1,7,19,20,21,26) AND RoleID = 74;
如果你的存储过程只做那个更新,我认为你可以这样写你的整个函数:
Public function enableMenus($selectedMenus, $roleID){
$this->db->where_in('MenuID', $selectedMenus);
$this->db->where('RoleID', $roleID);
$result = $this->db->update('RolesMenus', array( 'Enabled' => 0 ) );
$tempResult = $result;
$result->next_result();
return $tempResult->result();
}
就我目前所见,没有直接的方法将数组传递给存储过程。您可以创建另一个存储过程,它解析包含id的字符串,创建一个存储数组值的临时表,然后将该临时表作为参数发送给实际的存储过程。但是,在我看来,这更令人头痛:
DELIMITER $$
DROP PROCEDURE IF EXISTS `YourDB`.`sp_parseIntList` $$
CREATE DEFINER=`hotstuff`@`%` PROCEDURE `sp_parseIntList`(
_intlist TEXT )
BEGIN
DECLARE comma INT DEFAULT 0;
DECLARE mylist TEXT DEFAULT _intlist;
DECLARE temp TEXT DEFAULT '';
DECLARE strlen int DEFAULT LENGTH(_intlist);
CREATE TEMPORARY TABLE TempTable (num int) TYPE=INNODB;
SET comma = LOCATE(',',mylist);
WHILE strlen > 0 DO
IF
comma = 0
THEN
SET temp = TRIM(mylist);
SET mylist = '';
SET strlen = 0;
ELSE
SET temp = TRIM(SUBSTRING(mylist,1,comma));
SET mylist = TRIM(SUBSTRING(mylist FROM comma+1));
SET strlen = LENGTH(mylist);
END IF;
IF CAST(temp as UNSIGNED) != 0
THEN
INSERT INTO TempTable VALUES(CAST(temp as UNSIGNED));
END IF;
SET comma = LOCATE(',',mylist);
END WHILE;
SELECT * FROM TempTable;
DROP TEMPORARY TABLE IF EXISTS TempTable;
END $$
DELIMITER ;