Need help with an SQL statement within PHP

[Jin597]

I know this isn't the "exact" place to ask this, but wondering if anyone can help out.

What I want to do is update a column within a row concatenating what is there with some user input....

 

so... 

Field1  Field2  Field3
1        text     text
2        text     text        

 

So, I want to select Field2 from row 1 and update to:

Field1  Field2            Field3
1        textmoretext     text
2        text             text

 

So far, in my php script I have the following:

$sql = "UPDATE tablename SET fieldname3 = CONCAT(fieldname3, '".$input_to_concat."') WHERE tablename.fieldname = 1";
mysql_query($sql,$con);

 

any thoughts? 

[Lumio]

UPDATE `tablename` SET `Field2` = 'testmoretext' WHERE `Field1` = 1 LIMIT 1;

[Jin597]

very nice!  thanks for the quick reply Lumio!

[Jin597]

I just realized that the code offered entirely replaced the content rather than appending the current content.  adding CONCAT(fieldname3, '".$input_to_concat."') made the append work   Thanks again Lumio for checking into it

[igor berger]

I have never used concat, but I use select, manupulate the record, put it in a variable, by addiding what ever information to the variable and then update record!

 

Maybe concat does the same thing and saves on recources!

 

But one thing, when you use concat don't you need to put a space or something between old data and new data in a record?

 

Otherwise you will have

 

old datanew data.......

 

Correct me if I am wrong.

[Jin597]

Hi igor,

yes... you are right about the space.  I just tried with and without the space in there, and it performed in the manner you mentioned.  in my case, no space is ideal, so I payed no attention to that fact... but if you want a space, you definately will need to add one in the text to be inserted.

[igor berger]

[Lumio]

Maybe that works:

UPDATE `tablename` SET `Field2` = `Field2`+'moretext' WHERE `Field1` = 1 LIMIT 1;

[igor berger]

That is what I said. field2=field1+x

But he likes to concat......

 

Never used the function.....I am fromthe old school.........who knows what other goodies are there........

[per1os]

After searching mysql.com I do not think the CONCAT feature works with an UPDATE clause.

 

A posible work around would be this if you are using mysql 5:

UPDATE `tablename` SET `Field2` = (SELECT CONCAT(`Field2`+'moretext') FROM `tablename` WHERE `Field1` = 1 LIMIT 1) WHERE `Field1` = 1 LIMIT 1;

 

If you are not chances are the mysql does not support subquerys. This would work

<?php
$sql = "SELECT Field2 FROM `tablename` WHERE `Field1` = 1 LIMIT 1;";
$row = mysql_fetch_assoc(mysql_query($sql));
$field = $row['Field2'];

mysql_query("UPDATE `tablename` SET `Field2` = '" . $field . " moretext' WHERE `Field1` = 1 LIMIT 1;");

 

The code above is untested.

[Jin597]

In testing of the UPDATE with the CONCAT feature, I found that it did work.  I'm currently using MySQL5.0 and PHP5...  as I am no expert, there will likely be cases where this will not work, but at the moment, it seems to work in the manner I wanted.

$sql = "UPDATE `my_groups` SET `groups_members` = CONCAT(`groups_members`,'".$input_to_concat."') WHERE `groups_id` = 1 LIMIT 1";