Jump to content


Photo

Process Optimization for SELECT query


  • Please log in to reply
3 replies to this topic

#1 phporcaffeine

phporcaffeine
  • Members
  • PipPipPip
  • Advanced Member
  • 361 posts
  • LocationOhio, USA

Posted 30 April 2006 - 04:20 PM

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
Thanks,

Ryan Huff
President & Founder, MyCodeTree
support@mycodetree.com | http://mycodetree.com

#2 toplay

toplay
  • Staff Alumni
  • Advanced Member
  • 973 posts

Posted 30 April 2006 - 07:03 PM

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]

#3 phporcaffeine

phporcaffeine
  • Members
  • PipPipPip
  • Advanced Member
  • 361 posts
  • LocationOhio, USA

Posted 01 May 2006 - 01:50 AM

Here is the solution I came up with, based on toplay's recommendations and it works perfectly -
    $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'");

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!
Thanks,

Ryan Huff
President & Founder, MyCodeTree
support@mycodetree.com | http://mycodetree.com

#4 toplay

toplay
  • Staff Alumni
  • Advanced Member
  • 973 posts

Posted 01 May 2006 - 03:51 AM

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.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users