What?
I have a mySQL database table of room assets that has a field containing the ID numbers of images relevant to this room.

The Problem?
When I select specifying the statement "WHERE IN (c.RoomImages)", this is interpreted as a string and when converted to a number only retrieves the first value before the first comma. Consider the following, the first query is how MySQL interprets the query and the second is what I want it to do:

SELECT value FROM my_table WHERE my_id IN ('1, 2, 3')
SELECT value FROM my_table WHERE my_id IN ('1', '2', '3')

The Solution
I must confess that the bulk of this work is not mine and comes from a clever function by Chris Stubben in the MySQL Forums:

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

Note: You need to change the words in red to the column, table and the ID field of the database values you'll be using. In my case, ImageFileName is the full name of each image; custombu_roomassets_images is the table containing my Image IDs and their filenames; ImageID is the ID of each image.

Previously
Here's what was happening before using this query without the function (example: c.RoomImages='1,2,3'):

SELECT
	c.RoomName,
	(SELECT GROUP_CONCAT(i.ImageFilename) FROM custombu_roomassets_images i WHERE i.ImageID IN (c.RoomImages)) AS "ImageFiles"
FROM
	custombu_roomassets c
WHERE
	c.Display=1
ORDER BY
	c.RoomName


// Yielded:
RoomName	ImageFiles		
=============== ======================= 
B101		B101a.png		
B102		B102a.png

Now
Now here's the same query with the function (example: c.RoomImages='1,2,3'):

SELECT
	c.RoomName,
	splitAndTranslate(c.RoomImages, ',') AS "ImageFiles"
FROM
	custombu_roomassets c
WHERE
	c.Display=1
ORDER BY
	c.RoomName


// Yielded:
RoomName	ImageFiles		
=============== =============================
B101		B101a.png,B101b.png,B101c.png
B102		B102a.png,B102b.png

Done!

Other Searches

• making the mysql where in comma separated string work
• making the mysql where in work
• mysql convert comma separated string into rows
• mysql WHERE in comma separated string

< Prev		Next >