Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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 a the 'new' string which contains commas. But since the CONCAT functions uses commas I'm having trouble..

Anyone have any tips to accomplish this?

Thanks everyone!

share|improve this question
2  
What kind of trouble? – juergen d Mar 31 '12 at 6:01

4 Answers

up vote 2 down vote accepted

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'";
share|improve this answer
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 6:10
@Learning you missed the quote again – Your Common Sense Mar 31 '12 at 6: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 6: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 6:15
Thanks kjones! you're very helpful! – d-_-b Mar 31 '12 at 6:16
show 2 more comments
up vote 0 down vote

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'";
share|improve this answer
up vote 2 down vote

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

share|improve this answer
up vote 2 down vote

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

UPDATE TABLE_NAME SET FIELD_NAME = CONCAT_WS(",",FIELD_NAME,FIELD_NAME2) WHERE CONDITON
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.