CODEWITHSUNDEEP
phpmysqllaravel
GNewton
GNewton

Reputation: 90

Getting the MySQL WHERE IN delimited string

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

Upvotes: 0

Views: 181

Answers (3)

Keshan Fernando
Keshan Fernando

Reputation: 367

AND qa.question_id IN (splitAndTranslate(question_limit, ','))

Put these lines instead of the above line

AND (qa.question_id = question_limit
OR qa.question_id LIKE CONCAT(question_limit,',%')
OR qa.question_id LIKE CONCAT('%,',question_limit,',%')
OR qa.question_id LIKE CONCAT('%,',question_limit))

Then you don't need the splitAndTranslate Function anymore . .

Upvotes: 0

AntG
AntG

Reputation: 1294

What did you do with FIND_IN_SET? Building on spencer7593's answer, it should work if you replace:

AND qa.question_id IN (splitAndTranslate(question_limit, ','))

with 

AND FIND_IN_SET(qa.question_id, question_limit)>0

Upvotes: 1

spencer7593
spencer7593

Reputation: 108450

The commas within a string value are not interpreted as SQL text in the context of a SQL IN comparison. Your query is essentially of the form:

AND qa.question_id IN ('some,long,string,value')

And any comma characters within the string are just data; just characters that are part of the string. This is effectively the same as an equals comparison.

The MySQL FIND_IN_SET function might be a way for you to perform the comparison you want.

http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_find-in-set

Upvotes: 0

Related Questions