Jump to content

Process Optimization for SELECT query


phporcaffeine

Recommended Posts

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
Link to comment
Share on other sites

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]
Link to comment
Share on other sites

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!
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.