Using commas in a CONCAT php mysql update

Question

I have to perform a query similar to:

<?php
//....connect to database
$old = "a,b,c,d";
$new = "e,f,g,h";

$insert = "UPDATE TABLE SET FIELD = CONCAT(" . $old . ", " . $new . ") WHERE something = 'something';
mysql_query($insert);
?>

So basically, I want to append the current database entry with the 'new' string which contains commas. But since the CONCAT function uses commas I'm having trouble.

Does anyone have any tips to accomplish this?

kjones · Accepted Answer · 2012-03-31T06:10:35.593

5

Change this line

 $insert = "UPDATE TABLE SET FIELD = CONCAT(" . $old . ", " . $new . ") WHERE something = 'something'";

to this

$insert = "UPDATE TABLE SET FIELD = CONCAT('$old', '$new') WHERE something = 'something'";

Edit:

And if you want a comma between the $old and $new strings you are concatenating, use CONCAT_WS (http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat-ws)

Like so:

 $insert = "UPDATE TABLE SET FIELD = CONCAT_WS(',', '$old', '$new') WHERE something = 'something'";

edited Mar 31 '12 at 06:10

answered Mar 31 '12 at 06:05

kjones

1,339
1
13
28

Do I not need to do the good 'ol: " . $variable . " thing? I can just do: CONCAT('$variable',$variable')? That's pretty useful! – d-_-b Mar 31 '12 at 06:10
@Learning you missed the quote *again* – Your Common Sense Mar 31 '12 at 06:12
1

Correct. Since you are initializing your `$insert` variable with double quotes, it will perform variable expansion on any variables such as `$old` and `$new` and any single quotes will be interpreted literally as well. – kjones Mar 31 '12 at 06:13
I know haha...I was asking if in other examples without commas in my variables, is it unneccessary to break it up by using $variable = "function(" . $something . ")"; or like Kjones said simply do: $variable = "function($something)"; Although it probably depends on what function I'm using... – d-_-b Mar 31 '12 at 06:15
@Learning it depends on what *quotes* you are using. Learing the basic syntax of the language you are consumer of is quite a good idea. http://php.net/types.string – Your Common Sense Mar 31 '12 at 06:17

score 3 · Answer 2 · edited Nov 02 '12 at 10:37

3

Use the function mysql CONCAT_WS() -> Concatenate With Separator

UPDATE TABLE_NAME SET FIELD_NAME = CONCAT_WS(",",FIELD_NAME,FIELD_NAME2) WHERE CONDITON

edited Nov 02 '12 at 10:37

Aoi Karasu

3,730
3
37
61

answered Mar 31 '12 at 06:19

Anand

1,011
1
9
13

score 2 · Answer 3 · answered Mar 31 '12 at 06:05

2

strings in SQL queries has to be delimited by quotes.

$insert = "UPDATE TABLE SET FIELD = CONCAT(FIELD,',','$new') WHERE ...";

there is also no point in breaking a PHP string, adding only useless noise.

Also, I smell a case for the database normalization

answered Mar 31 '12 at 06:05

Your Common Sense

156,878
40
214
345

score 0 · Answer 4 · answered Mar 31 '12 at 06:02

0

You need to add quotes around the variables so that you get strings, like this (I also added a trailing double quote):

$insert = "UPDATE TABLE SET FIELD = CONCAT('" . $old . "', '" . $new . "') WHERE something = 'something'";

answered Mar 31 '12 at 06:02

Waynn Lue

11,344
8
51
76

Using commas in a CONCAT php mysql update

4 Answers4