Why not just create an active bit so that when an employee is hired active = 1, when terminated active=0. Might be easier than having two tables.

If you have no access to MySQL triggered functions, etc, then you are just going to have to manually transfer the data whenever an employee is terminated. Since each table is exactly the same except for a field or two, then you can dump the data from one table, change/remove/add the one or two different columsn, and simple insert it into the other.

Rough example:
"SELECT * FROM active_employees WHERE id_employees = ".employeeIDVariable;

Change/remove needed columns

"INSERT INTO termed_employees VALUES (".select statement data.")";

On a slightly side note, if you have access to use PHP 5.2 or newer I'd suggest using PDO. It's more powerful and seems to be the future of db connections in PHP.

If you used PDO you could read a row as an object, remove the primary key (if you have one), and clone/save it as a new row really easily.

Just curious, does PDO support prepared statements or something similar? That's one thing I really like about mysqli. I know it is vendor specific, but they are wonderful to use, fast, and have some security built in.

EDIT: sorry, it does, just looked it up - should of before I commented.

I know you looked it up but for anyone else who reads this, PDO does support prepared statements and much more. Can anyone say transactions.

I'm with the Bishop on this (though I second Matt's PDO) except I'll make it even simpler:

Just use the one table, no extra fields necessary. If QuitDate is empty, the employee is active, if not, the employee is a past one.

Why transfer records from one table to another when it isn't necessary? It just adds one more possible point of failure. (I'm a big fan of simplicity.)

Thanks Bishop for the script. Quick question though. This script just copies the id from the first table and inserts it in the second. The second time around running this script, isn't it bound to clash? As in 2 rows with the same ID in the second table?

Humm, why would you have two employee's with the same ID number? With MySQL an integer (unsigned) will give you 4294967295 values.

But if it's a problem, create an auto-increment index for your new table then append it to your $field and $value strings with value set to "null".

Yes I was talking about that. Might be helpful to say that I'm a little wet behind the ears in PHP. :)

Here's how I've used your script in my already existing page. This is a mailing list, and the idea is that when someone unsubscribes, they are removed for the mail table and the data copied into the unsub table for future reference.

// copy row

//keeps getting the next row until there are no more to get
while ($row = mysql_fetch_assoc($result2)) {
foreach ($row as $field => $value) {
$fields .= "$field, ";
$values .= "'$value', ";
}
// remove trailing ", " from $fields and $values
$fields = preg_replace('/, $/', '', $fields);
$values = preg_replace('/, $/', '', $values);

}

$sqlme = "INSERT INTO unsub ($fields) VALUES ($values)";
mysql_query($sqlme);

// end copy row

$sql2="delete from mail where email='$email'";
$result2=mysql_query($sql2) or die("
Failed to delete. Try again.");

Of course this isn't good practice. Unsubscribing is usually one email at the time, but I kept the while loop in so as not to mess things up.

Care to help on the ID part? When inserted into the unsub table, it simply goes in with its old ID in the mail table.

Thanks a million in advance.

Humm, I'd really suggest you simply add an "employee active" field for your existing table and when it is 0, the employee is considered terminated, when 1 the employee is active. Then you can maintain old date without having to do two tables.

This is the most common way to do things.

But you can replace

Which should get you going if you have an auto increment field newid. So the new table would store both the old id and have a new id.