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

Re: Delete one table and move data to another table

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 ;

DELETE FROM table1 WHERE ;

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."
To: php-db@lists.php.net
Date: Wed, 26 Sep 2007 17:26:04 +0700
Subject: [PHP-DB] 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