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')
- 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
- 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
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
- 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
- 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