Move a record from one table to another table in a database using php

[dewhickey]

Using PHP, I have a button to delete the current record from the database. This takes the user to a confirm delete page. When the user confirms the delete, I want the record to be deleted from the table and also moved to another table that functions as an archive table.

Does anyone have the PHP code to do this?
Thanks

[kkeith29]

why don't you just use a field in the table telling whether or not its archived. just add an archive column with a 0 or 1. where 0 means not archived and 1 means it is.

[dewhickey]

It sounds like you're talking about testing to see if it's archived. This will always come back a false because I don't know how to archive the record in a table that is a different table from the one it is currently in, which is the record I want to delete.

[kkeith29]

i don't think i understand what you are trying to do correctly. please give a more detailed explaination.

[sikka_varun]

Hii...
Use the following function to copy the data from one table to the archived table..
When someone deletes the current record, you must be having its ID... So you can use its ID in the query string..

So once someone deletes this record.. you can write the following code on the page (after the confirm delete page):

<?php

$id=$_GET['id'];

$sql="Select * from <tablename> where id=".$id;
$result=mysql_query($sql);
$row=mysql_fetch_array($result);

//Call the function to archive the table
//Function definition is given below
archive_record(<tablename>,$row);

//Once you archive, delete the record from original table

$sql = "Delete from <tablename> where id=".$id;
mysql_query($sql);


function archive_record($archived_tablename,$row)
{
	$sql = "insert into $archived_tablename values(";
	$i=0;
	while($i<(count($row)-1))
	{
		$sql.="'".$row[$i]."',";
	}
	$i=$i+1;
	
	$sql.="'".$row[$i]."'";
	$sql.=")";

	mysql_query($sql);
	return true;
}
?>

[xarz]

I think I agree with kkeith29..

This might not answer your question directly on how to move a certain data from one table to another but instead, you can add a new column in the database which indicate whether it is archived or not. Well, let's assume that archived data are indicated by 1.

if you're using mySQL and php perhaps you can get the list of the archived files by doing this:

$query=mysql_query("SELECT * FROM _table_ WHERE archive='1'");

just replace the _table_ with your current table name.

[nikesh.yadav]

also u can make a flag that show data is archive if flag is false and non- archive if flag is true

[freeonlinedatin]

hi

This is harrison i agree with xarz,you just create a separate column ie for archive its some 0 and unarchive its 1,let's try and i think its work for you

[Fest3er]

To strictly answer the OP's question:

LOCK TABLES active WRITE, archive WRITE;
INSERT INTO archive
       SELECT * FROM active
                WHERE active_rec_id='$confirmed_id';
DELETE FROM active
       WHERE active_rec_id='$confirmed_id';
UNLOCK TABLES;

The LOCK and UNLOCK are shown in case you want/need 'atomic' updates.

This worked for a simple table. It might not for a complex table. I believe the two tables must have identical schemas; if not, making them identical makes the programmers life a little easier.

[fedrik]

I am also searching for the same. please let me know if u get the answer.

[dewhickey]

Thank you all for your help. The addition of a field in each record I called "active" solved the problem. On the insert new record page, I added a hidden field that sets "active" to the value of "0" (zero). Then, on both the front-end (public) and back-end (edit) pages, which are dynamically created, I added to the database query a bit of code like this:

$query_rsTableName = "SELECT * FROM TableName WHERE active = '0' ;

I created another page on the back end where a logged in user can retrieve the archived records with a "SELECT * FROM TableName WHERE active = '1';

Of course, instead of deleting records along the way, that process is now an "update" that merely changes the value of active from "0" to "1." And, also of course, the user can change the value of an archived record back to "0" to make it active again.

Thanks again.

[almostbob]

Thank you all for your help. The addition of a field in each record I called "active" solved the problem. On the insert new record page, I added a hidden field that sets "active" to the value of "0" (zero). Then, on both the front-end (public) and back-end (edit) pages, which are dynamically created, I added to the database query a bit of code like this:

$query_rsTableName = "SELECT * FROM TableName WHERE active = '0' ;

I created another page on the back end where a logged in user can retrieve the archived records with a "SELECT * FROM TableName WHERE active = '1';

Of course, instead of deleting records along the way, that process is now an "update" that merely changes the value of active from "0" to "1." And, also of course, the user can change the value of an archived record back to "0" to make it active again.

Thanks again.

Hidden form fields are not hidden securely, and can be viewed and messed with
instead use php to set the variable on the create new record page,
or set the default value of the field in phpmydadmin