MySQL: Qoute single issue when passing parameter to stored procedure

Question

I am sending a list with commas to my stored procedure from PHP. The stored procedure is as follows:

UPDATE RolesMenus SET Enabled=1 WHERE MenuID IN(prmMenusList) AND RoleID = prmRoleID; 

Now the problem is that when I pass the menu list (prmMenusList) through the code, it just updates the value of only the first item in the list. This, I think, is due to some single quote from the list. The request can be formed as follows:

 UPDATE RolesMenus SET Enabled=0 WHERE MenuID IN('1,7,19,20,21,26') AND RoleID = 74; 

How can I avoid the situation?

Here I added the CodeIgniter code (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(); } 

Answer 1

I solved the problem. :)

Just used the request as follows:

 UPDATE RolesMenus SET Enabled=1 WHERE FIND_IN_SET(MenuID,prmMenusList) AND RoleID = prmRoleID; 

OR

 UPDATE RolesMenus SET Enabled=1 WHERE FIND_IN_SET(MenuID,'1,2,3,4') AND RoleID = 1; 

Answer 2

send prmMenusList as a list of integers instead of the string:

 UPDATE RolesMenus SET Enabled=0 WHERE MenuID IN(1,7,19,20,21,26) AND RoleID = 74; 

If your stored procedure only performs this update, I think you could write your entire function, for example:

 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(); } 

As far as I have seen so far, there is no direct method for passing an array to a stored procedure. You can create another stored procedure that parses your string containing identifiers, creates a temporary table that stores your array values, and then send that temporary table as a parameter to your actual stored procedure. But, in my opinion, this is a much bigger headache:

 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 ;