Process Optimization for SELECT query

[phporcaffeine]

Okay - Here's the skinny:

I have a table database that stores records of user data - it has an autonum column and 13 othe cols.

Now I have an area of a script where a user can select "archive" . What I'm going to have happen is for php/MySQL to SELECT the record first from the "active" database, then "move" it to an archive database (which is an identical database structure) via INSERT. The last step will be to DELETE the record from the original database which I am calling the "active" database.

Basiacally, when you strip away all the fluff - what we're really doing is SELECT 'ing an entire record from a DB then INSERT 'ing that record data into a seperate (but identical) DB, then DELETE 'ing the record from the first DB.

That's the only way I know how to do it, I am hoping that there is a php/MySQL deity out there that will tell me MySQL has a canned method for something like this? PLEASE?!@?, lol - It's seems like a common enough task that MySQL would provision it but I couldn't find anything in the docs....


TIA

[toplay]

The way you have it is the safest way to do it. Off the top of my head, there is at least the "insert into xx select" which would help you add the row to the archive table, then you would then delete it as you say.

[a href=\"http://dev.mysql.com/doc/refman/4.1/en/insert-select.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/insert-select.html[/a]

[a href=\"http://dev.mysql.com/doc/refman/4.1/en/delete.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/4.1/en/delete.html[/a]

[phporcaffeine]

Here is the solution I came up with, based on toplay's recommendations and it works perfectly -
[code]
    $record = $get[1]['data'];
    
    $sql = mysql_query("SELECT * FROM record WHERE id='$record'");
    
    $row = mysql_fetch_assoc($sql);
    
                //This first col is an autonum type, so I can't keep it cause the archive DB will make its own
                //autonum
    array_shift($row);
    
    foreach ($row as $key => $value) {
        
        $tables[] = $key;
    
    }
    
mysql_query("INSERT INTO archive (" . implode(", ", $tables) . ") SELECT " . implode(", ", $tables) . " FROM record WHERE id='$record'");

unset($tables);

mysql_query("DELETE FROM record WHERE id='$record'");[/code]

Now thats doing three queries to accomplish one task, it seems a bit clunky but I can't see a more efficent way.

P.S - Tanks toplay!

[toplay]

You're welcome.

Don't forget to always check for any MySQL errors after every query. In this case, make sure that the insert occurred alright before deleting the row!

Of course, it's a good to check if the delete worked as well.

Happy coding.