View Full Version : Update using Concat

08-26-2006, 01:06 PM
I have a form that I call for the data from a database table. Once the table is filled with data, the viewer has the option to add data to one field in the form. I want to be able to add the new comments without losing the old comments in the db column.

Field A has the existing data ($Remarks)
Field B has the new comments ($NRemarks)

I have tried various CONCAT statements but none of them work, and I am thinking that this may be because Field B is not a column in the DB, as it is new information being added.

Is there a way to keep the data in the DB column called Remarks and add the information in Field B to it on a seperate line.

I don't know if there is a command like append or edit or add to, that will add information to the existing information.
I have tried the following:

$sql="UPDATE 'workorder' SET Remarks CONCAT (Remarks, NRemarks)";

$sql="UPDATE 'workorder' SET Remarks CONCAT ($Remarks, $NRemarks)FROM workorder WHERE Work = '$Contact'";

$sql="UPDATE `workorder` SET Remarks = CONCAT(SELECT Remarks FROM workorder
WHERE Work = '$Contact', '', '$NRemarks') WHERE Work = '$Contact'";

08-26-2006, 04:47 PM
Concat() is what you use for this, just use it like this:

$sql = "
UPDATE workorder
SET Remarks = concat(Remarks, '$NRemarks')
WHERE Work = '$Contact'";

08-26-2006, 06:00 PM
That worked, amazing what a pair of ' can do..... would there be a way to have the new remarks appear on a new line.....by using a \n somewhere in the formula?

08-26-2006, 06:43 PM
No, you would add that to your newline but use a <p> or <br> or whatever you want to use when you display it. you won't get it to display on two lines within mysql though.

08-26-2006, 08:18 PM
What I am doing is having the two lines joined together in the database. So I am assuming there is no way of having them appear on separate lines when they are called back into the form.

Correct me if I am wrong.


08-27-2006, 01:18 AM
Why not break out the comments to a separate table? You could store each comment in its own row, select the rows separately, and normalize your data. I saw your table layout from another post of yours you have a serious need to normalize your data. For example, columns like image1, image2, image3 should be stored in an image table instead.

Oops I meant to respond to your question-- you can store "\n" in a table and then perform the nl2br() function on it when you display it.

08-27-2006, 10:52 PM
I am a little slow on following up on this.....

To normalize the table, images, comments, employees, etc....all those type of things should be in seperate tables.... is this more effective for the speed in which it can call the data opposed to it all being in one table? To my newbie mind, this appears backwards as now it needs to look in different tables to find everything.....I am not arguing, just trying to rationalize this.

I will try to create some new tables to accomodate all of this, and I am assuming I would put the work order #, which is the common denominator to all of these items, into each table.

Time to do some more reading on normalization and how to set it all up.


08-28-2006, 12:05 AM
This arcticle puts it all out there in black and white: