Jump to content


Photo

Want to add a comment but keep existing


  • Please log in to reply
6 replies to this topic

#1 kenwvs

kenwvs
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 20 August 2006 - 07:08 PM

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

#2 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 20 August 2006 - 07:48 PM

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.

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

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

#3 kenwvs

kenwvs
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 20 August 2006 - 09:38 PM

I have tried your suggestion, but am getting a syntax error that says:

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


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

$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>

{
$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!!";


#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 August 2006 - 05:32 PM

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. 
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 kenwvs

kenwvs
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 21 August 2006 - 05:37 PM

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

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 21 August 2006 - 05:39 PM

You can issue a multiple UPDATE statement, though I don't remember what version is required.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 kenwvs

kenwvs
  • Members
  • PipPipPip
  • Advanced Member
  • 194 posts

Posted 21 August 2006 - 05:40 PM

thank You I will look into it and see if I can make it happen.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users