Page 1 of 1

storing old record in another table

Posted: Thu May 21, 2009 2:39 am
by vikela
i am trying to store old record in another table whenever new record is being recorded.i have a form that can update a table but i would like to capture the old record before the new record is recorded and so far i am have the following code which can add a new row but without the record.

Code: Select all

.......if ((isset($_POST["submit"])) {   
$backup="Insert into Member_old SET member_id='$theid', description='$mydesc',
 
favor_music='$favmusic',favor_website='$favweb'";
$Result1 = mysql_query($backup, $moving) or die(mysql_error());
$update = sprintf("UPDATE Member SET description=%s, favor_music=%s, favor_website=%s WHERE
 
member_id = $theid",
                       GetSQLValueString($_POST['description'], "text"),
                       GetSQLValueString($_POST['favor_music'], "text"),
                       GetSQLValueString($_POST['favor_website'], "text"),
 
       $Result = mysql_query($update, $moving) or die(mysql_error());
   mysql_select_db($database_moving, $moving);
}
$query_updater = "SELECT * FROM Member WHERE member_id = $theid";
$updater = mysql_query($query_updater, $moving) or die(mysql_error());
$row_updater = mysql_fetch_assoc($updater);
$totalRows_updater = mysql_num_rows($updater);
$mydesc=$row_updater['description'];
$favmusic=$row_updater['favor_music'];
$favweb=$row_updater['favor_website'];
$favact=$row_updater['favor_activity'];
 
<form method="post" name="formb" action="<?php echo $_SERVER['PHP_SELF'];?>">
  <table border="0" align="left">
    <tr valign="baseline">
      <td colspan="3" align="right" valign="top" nowrap>&nbsp;</td>
    </tr>
    <tr valign="baseline">
      <td colspan="2" align="right" valign="top" nowrap><span
 
class="style13">Description:</span></td>
      <td align="left"><strong>
        <textarea name="description" cols="50" rows="3"><?php echo $mydesc; ?></textarea>
      </strong></td>
    </tr>
    <tr valign="baseline">
      <td colspan="2" align="right" nowrap><span class="style13">Favourite Music:</span></td>
      <td align="left"><input name="favor_music" type="text" value="<?php echo 
 
$row_updater['favor_music']; ?>" size="32"></td>
    </tr>
    <tr valign="baseline">
      <td colspan="2" align="right" nowrap><span class="style13">Favourite Website:</span></td>
      <td align="left"><input name="favor_website" type="text" value="<?php echo 
 
$row_updater['favor_website']; ?>" size="32"></td>
<input type="submit" value="Update Profile  " name="submit"></td>
    </tr>
  </table>
   <input type="hidden" name="MM_update" value="formb">
 
</form>........  
:cry: :cry:

Re: storing old record in another table

Posted: Thu May 21, 2009 3:01 am
by onion2k
What version of MySQL are you using? If it's 5.0 or higher, try looking at the triggers. It'll be something like;

[sql]CREATE TRIGGER 'backup' BEFORE UPDATE ON `table` FOR EACH ROW BEGIN INSERT INTO `backup_table` SET row1 = OLD.row1, row2 = OLD.row2END[/sql]

Whenever a row is updated in `table` a copy of the values before the update will automatically be inserted into `backup`.

More info: http://dev.mysql.com/doc/refman/5.0/en/ ... igger.html

Re: storing old record in another table

Posted: Thu May 21, 2009 7:42 am
by jaoudestudios
If only a few columns are changing, you dont want to duplicate the entire row. To avoid this you could use a change log table (id,user_id,name,value_before,value_after,date,type), that records all the changes. So a kind of simplified version control.