Move record to another table before deleting

[drennan]

Hi all. I'm new to php and am trying to get past something that's probably quite simple. I've got a form with a while loop showing a list of tasks. The task can be deleted with this button:

<a href="delete.php?recordID=<?php echo $row_tasks['id']; ?>">
<input type="submit" name="submit" id="submit" value="" />
</a>

It posts to delete.php where I've got this code to delete the record.

if ((isset($_GET['recordID'])) && ($_GET['recordID'] != "")) {
  $deleteSQL = sprintf("DELETE FROM tasks WHERE id=%s",
                       GetSQLValueString($_GET['recordID'], "int"));

  mysql_select_db($database_test, $test);
  $Result1 = mysql_query($deleteSQL, $test) or die(mysql_error());

  $deleteGoTo = "index.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $deleteGoTo .= (strpos($deleteGoTo, '?')) ? "&" : "?";
    $deleteGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $deleteGoTo));
}

mysql_select_db($database_test, $test);
$query_tasks = "SELECT * FROM tasks";
$Recordset2 = mysql_query($query_tasks, $test) or die(mysql_error());
$row_tasks = mysql_fetch_assoc($tasks);
$totalRows_tasks = mysql_num_rows($tasks); 

I need to edit this code so the record being passed inserts into another table before it gets deleted. Can anyone point me in the right direction? Thanks guys

[NogDog]

My first suggestion would be to not move it, but instead mark it as deleted. This could either be a "status" field which can have different values to indicate what status applies, including, for example, 'D' for deleted. Or you could just have a separate column only for indicating when it is deleted. This would mean you would update your regular listing queries to include a check of that column's value, e.g.:

. . . WHERE status != 'D' . . .

Why I like such an approach is that it makes it easy to generate reports and such since all the historical data is in that one table, plus I just don't like having more tables than I really need.

If you do want/need to go ahead with the idea of moving it to another table, probably the simplest approach is:

INSERT INTO new_table (col1, col2, col3)
SELECT col1, col2, col3 FROM old_table WHERE col1='foo'; -- assuming for this example `col1` is the primary key

Obviously you want to do that before you then delete that record from `old_table`.

[drennan]

Thanks a lot for the tip. I've done what you suggested and updated the record's status instead and it works great

[nicksaiz]

Hi all. I'm novel to php as well as am annoying to get past amazing that's almost certainly quite easy.





**Links removed by Site Administrator so it doesn't look like you're spamming us. Please don't post them again.**