Jump to content

Archived

This topic is now archived and is closed to further replies.

kenwvs

Want to add a comment but keep existing

Recommended Posts

I had a look at the mysql page, but couldn't find the right word to use instead of INSERT, UPDATE, etc. that will add additional information to a DB column, without deleting the existing data.

If I have a form where people put comments, and then they add additional comments, I want to add the new comments to the column that has existing comments...

thanks,

Ken

Share this post


Link to post
Share on other sites
You can concatenate the contents of the record with the new contents, or select it out, concatentate in php, then update with the new contents.

[code]UPDATE table SET contents = CONCAT(SELECT comment FROM table WHERE id = '$id', ' ', '$newcomments') WHERE id = '$id';[/code]

I think that will work, depending on your version of MySQL (> 4.0.11), although I haven't tested it.

Share this post


Link to post
Share on other sites
I have tried your suggestion, but am getting a syntax error that says:

[quote]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT Remarks FROM workorder WHERE Work = '08194151', ' ', 'This is a test') WH' at line 1[/quote]

The coding I am using is as follows (I have commented out the original code I had for the update, except it would just overwrite the exisiting comment)

I am using Remarks for the existing Remarks (and this is the column name in the DB, and have used NRemarks for the remarks being added and need to be updated to the DB

[code]$query=mysql_query("SELECT Work, Sched, Name, Site, Serial, Hours, Starts, Issue, Severity,
Resolution, Assistance, PartsA, PartsB, PartsC, PartsD, PartsE, PartsF, PartsG, Safety, Purchase,
Comment, Remarks FROM workorder WHERE Work='$Contact'");
if (!$query) {
    echo "YOU HAVE AN ERROR IN YOUR QUERY!<br>\n";
    echo mysql_error();
} else {
    $workorderInfo = mysql_fetch_assoc($query);
}
//this is just for testing
if (mysql_num_rows($query) == 0) {
    echo "'$Contact'is NOT a valid Work Order. Please Try Again<br>\n";
}
?>
<?php
if (!isset($_POST['Submit']))
{
?>
<form action="" method="post" enctype="multipart/form-data">
<center><div>Purchase Order:<input type="text" value="<?php echo $workorderInfo['Purchase'];?>" size="10" id="Purchase"></div><BR><BR>
<div><B>Work Order Update:</B><BR>(Remarks should start with the date (mm/dd/yy))<textarea rows="4" name="NRemarks" cols="80"></textarea>
</div><BR><BR>
<div><B>Previous Updates:</B><BR>
<textarea id="Remarks" rows="4" cols="80"><?php echo mysql_result($query, 0, 'Remarks') ?></textarea></div><BR>[/code]

[code]{
$Remarks = $_POST["Remarks"];
$NRemarks = $_POST["NRemarks"];
$sql="UPDATE `workorder` SET Remarks = CONCAT(SELECT Remarks FROM workorder WHERE Work = '$Contact', ' ', '$NRemarks') WHERE Work = '$Contact'";
//$sql="UPDATE `workorder` set Remarks = '$Remarks' WHERE Work = '$Contact'";
//echo "$sql";
mysql_query($sql)or die(mysql_error());
echo " Work Order # '$Contact' has been successfully Updated!!";[/code]

Share this post


Link to post
Share on other sites
Sounds like you don't have subquery support... you can "cheat" by creating a new temporary column in the target table, copy over the values from the source table, and then UPDATE / concat without a subquery. 

Share this post


Link to post
Share on other sites
When you say create a temporary column and then copy the new data to the main category, would I have to move them manually or would I be able to put a command in that it would do it automatically?

Ken

Share this post


Link to post
Share on other sites
You can issue a multiple UPDATE statement, though I don't remember what version is required.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.