Re: Delete one table and move data to another table
| From: | TG | Date: | Wed, 26 Sep 2007 13:31:11 +0000 |
| Subject: | Re: Delete one table and move data to another table | ||
| Groups: | php.db | ||
| Request: | Send a blank email to php-db+get-44058@lists.php.net to get a copy of this message | ||
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@telkom.net>
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
>
>