Re: Delete one table and move data to another table

["TG" <tg-php@xxxxxxxxxxxxxxxxxxxxxx>]

You could use an INSERT/SELECT statement in MySQL:

http://dev.mysql.com/doc/refman/4.1/en/insert-select.html


Using the schema you outlined:

INSERT INTO table2(name, address, date_deleted, who_delete)
  SELECT name, address, NOW(), 'whoever deleted'
  FROM table1 WHERE <some condition>;

DELETE FROM table1 WHERE <some condition>;

You can include "id" too if you want.

In PHP, you could make it dynamic (simple example):

$deleter = "Joe";
$SQLcondition = "id > 50 and name like 'Fran%'";

$insertQY  = "INSERT INTO table2(name, address, date_deleted, who_delete)";
$insertQY .= " SELECT name, address, NOW(), '$deleter'";
$insertQY .= " FROM table1 WHERE $SQLcondition";
$insertRS = mysql_query($insertQY);

$deleteQY = "DELETE FROM table1 WHERE $SQLcondition";
$deleteRS = mysql_query($deleteQY);


That way your condition is the same in both statements, reducing potential 
errors.

This is just a quick and dirty example, not adhering to any real design 
scheme.  My normal code is a bit prettier than this, but you get the idea.

-TG


----- Original Message -----
From: "endro mei a." <j4nk3r@xxxxxxxxxx>
To: php-db@xxxxxxxxxxxxx
Date: Wed, 26 Sep 2007 17:26:04 +0700
Subject:  Delete one table and move data to another table

> I have a table1 it contents (3 column) :
> 
> id int(11)
> name varchar(200)
> address varchar(255)
> 
> I want to delete table1 and insert data to table2 it 
> contents (5 column) :
> 
> id
> name
> addres
> date_delete datetime
> who_delete varchar(50)
> 
> 
> Code  on php and mysql  ???
> Anyone can help me.
> 
> 
========================================================================================
> "Sambil berpuasa, ikuti Netkuis Ramadhan 1428 H. Menangkan Laptop, Ipod dan 
> HP Nokia di akhir periode netkuis dan dapatkan Flash disk di tiap 
> minggunya dengan mengikuti Netkuis di http://netkuis.telkom.net/";
> 
> 
========================================================================================
> 
> -- 
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
> 
> 

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php