I've been recently stuck on an issue I've been having involving sending a string of comma separated values into a stored procedure. My issue is that when I execute my stored procedure in PHP it uploads the values with quotes around it like so;
CALL `rankingInformation`('145', '5', '', '37,38,39,40,41')
Failing to add the quotes would make MySQL interpret them as extra parameters.
However it's mean't to be like in the WHERE IN on the query side it's meant to be formatted like so
'37', '38', '39', '40', '41'
Here is the query below, can anyone spot anything I can do? Here is what I've got up to now.
CREATE DEFINER = `root`@` localhost` PROCEDURE` rankingInformation`(IN` surveyId` INT, IN` filterCounting` INT, IN` survey_filter_id` INT, IN` question_limit` TEXT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Gathers all the ranking information for a given ID'
BEGIN
DECLARE sfi int(2);
DECLARE ql TEXT;
IF(survey_filter_id = '') THEN
SET sfi = (SELECT sf2.survey_filter_id FROM survey_filters AS sf2 WHERE sf2.survey_id = 145 AND sf2.survey_filter_id IS NOT NULL LIMIT 1);
ELSE
SET sfi = survey_filter_id;
END IF;
SELECT
COUNT( * ) AS total, CONCAT(su.first_name, ' ', su.last_name) as full_name, sf.survey_filter_id, sf.survey_filter_name, qa.question_id, su.temp_group_1 AS department
FROM questions_answers AS qa
INNER JOIN survey_users AS su ON su.survey_users_id = qa.survey_users_id_answer
INNER JOIN survey_filters AS sf ON sf.survey_id = surveyId
WHERE qa.survey_id = surveyId
AND qa.question_id IN (splitAndTranslate(question_limit, ','))
AND sf.survey_filter_id = sfi
GROUP BY qa.survey_users_id_answer
HAVING total > filterCounting
ORDER BY total DESC;
END
splitAndTranslate Here is a function I found which is mean't to do the job, I am not sure I am far away.
CREATE DEFINER=`root`@`localhost` FUNCTION `splitAndTranslate`(`str` TEXT, `delim` VARCHAR(1))
RETURNS text CHARSET utf8
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Fixes all Where IN issues'
BEGIN
DECLARE i INT DEFAULT 0; -- total number of delimiters
DECLARE ctr INT DEFAULT 0; -- counter for the loop
DECLARE str_len INT; -- string length,self explanatory
DECLARE out_str text DEFAULT ''; -- return string holder
DECLARE temp_str text DEFAULT ''; -- temporary string holder
DECLARE temp_val VARCHAR(255) DEFAULT ''; -- temporary string holder for query
-- get length
SET str_len=LENGTH(str);
SET i = (LENGTH(str)-LENGTH(REPLACE(str, delim, '')))/LENGTH(delim) + 1;
-- get total number delimeters and add 1
-- add 1 since total separated values are 1 more than the number of delimiters
-- start of while loop
WHILE(ctr<i) DO
-- add 1 to the counter, which will also be used to get the value of the string
SET ctr=ctr+1;
-- get value separated by delimiter using ctr as the index
SET temp_str = REPLACE(SUBSTRING(SUBSTRING_INDEX(str, delim, ctr), LENGTH(SUBSTRING_INDEX(str, delim,ctr - 1)) + 1), delim, '');
-- query real value and insert into temporary value holder, temp_str contains the exploded ID
#SELECT ImageFileName INTO temp_val FROM custombu_roomassets_images WHERE ImageID=temp_str;
-- concat real value into output string separated by delimiter
SET out_str=CONCAT(out_str, temp_val, ',');
END WHILE;
-- end of while loop
-- trim delimiter from end of string
SET out_str=TRIM(TRAILING delim FROM out_str);
RETURN(out_str); -- return
END
'\'' . implode( '\',\'', explode (',', '34,35,36,37')) . '\''SQLSTATE[42000]: Syntax error or access violation: 1318 Incorrect number of arguments for PROCEDURE rankingInformation; expected 4, got 8 (SQL: CALL rankingInformation('145', '0', '', '37','38','39','40','41'))