Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update using Concat

    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:


    PHP Code:
    $sql="UPDATE 'workorder' SET Remarks CONCAT (Remarks, NRemarks)";
    ?> 

    PHP Code:
    $sql="UPDATE 'workorder' SET Remarks CONCAT ($Remarks, $NRemarks)FROM workorder WHERE Work = '$Contact'";
    ?> 


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

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    Concat() is what you use for this, just use it like this:

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


  • #3
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,631
    Thanks
    4
    Thanked 147 Times in 138 Posts
    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.

  • #5
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Ken

  • #6
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    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.

    Edit:
    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.
    Last edited by Fumigator; 08-27-2006 at 12:21 AM.

  • #7
    Regular Coder
    Join Date
    Jul 2006
    Posts
    168
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.

    Ken

  • #8
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    This arcticle puts it all out there in black and white:

    http://dev.mysql.com/tech-resources/...alization.html


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •