mysql update command

in the field "data" value is "12,17,18,"
i want to add 21 there...so it will be "12,17,18,21,"

i tried:

$abc = "21,";
UPDATE myTable SET data = data.$abc WHERE id=1

PHP:

not working

help ...!

 

Try this

UPDATE myTable SET data = data.'$abc' WHERE id=1

 

UPDATE myTable SET data = CONCAT_WS(',',data,'$abc') WHERE id = 1;

should give the result: 12,17,18,21

CONCAT_WS function defination is CONCAT_WS(seperator, string1, string2, ...)

 

thank you very much

but still a problem.
by this method...if there's nothing in mysql field...it start with a comma (,)
so later it will be looks like: ,12,14,16,55
is there any way ??

 

The whole algorithm is not that bright
but if u want to carry on
let the , remain in mysql field...
and use substr after retrive....or may be substr before mysql insert...

 

but cant understand how to do it with substr()
becoz the length is unknown
it cud be ,12,15,97,44 or ,15,24 or ,14,15,16,24,54,86,35,.......
so the question is how to remove the beginning comma only

 

MySQL supports IF and ELSE

UPDATE myTable SET IF data = '' THEN data='$abc' ELSE data = concat(',',data,'$abc') WHERE id=1

PHP:

you can find more information on how to use IF and ELSE withing MySQL => http://dev.mysql.com/doc/refman/5.0/en/if-statement.html

 

try this
$first_comma_out=substr($row['field_with_commas'], 1);

 

Remove the beginning comma:

$string = ",1,2,3";
$string = substr($string, 1, strlen($string)); // Return everything from 1st character to end of string

PHP:

 

guys i found it & i think its the best solution

UPDATE myTable SET data = IF(data = '','$abc',CONCAT_WS(',',data,'$abc')) WHERE id=1

PHP:

 

yes,use the CONCAT_WS