Same query different user_name error

[mapleleaf]

SELECT * FROM articles WHERE FIND_IN_SET(32, second_theme) AND user_name = 'Ram' AND deleted = 'No' ORDER BY date_added ASC

gives no results

SELECT * FROM articles WHERE FIND_IN_SET(32, second_theme) AND user_name = 'John' AND deleted = 'No' ORDER BY date_added ASC

gives results

 

 

Ram's second_theme:

 

58, 32

49, 46

49, 32

20, 1, 58

1, 58, 32

58, 32, 41

46, 32

48, 32

9, 41, 32

48, 32, 41

48

20, 41

48, 32

14, 8, 32

 

John's second_theme:

2, 4, 41

18

 

14

45, 9, 8

48,32,8

12

6

2, 9, 48

8

9, 48, 41

5, 13, 34

30, 3, 48

9, 3

14, 61, 8

52

52

46,15,3

2

12,32

 

 

Any ideas why this might happen?

 

 

[PFMaBiSmAd]

It's probably because of the space characters that are in the string data.

 

Jonh's data does not have any spaces with the 32 values. Ram's does.

 

I'll bet if you try 41 with John's data that it won't match anything.

[mapleleaf]

Spot on  PFMaBiSmAd!!

A good pair of eyes are hard to come by.

Much appreciated.

Now I have the fun of fixing all the records. Code that made them was the easy bit

[PFMaBiSmAd]

You can use a mysql REPLACE() function to remove all the spaces in the data -

 

REPLACE(str,from_str,to_str)

 

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

 

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww');

        -> 'WwWwWw.mysql.com'

 

 

[mapleleaf]

SELECT REPLACE( 'second_theme', ', ', ',' )
FROM articles

is what I am running in phpmyadmin but it doesn't seem to do anything yet it says all rows have been affected

[mapleleaf]

UPDATE `articles` SET second_theme = REPLACE(second_theme,', ',',')

is what I needed.

Again thanks to PFMaBiSmAd