SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Thread: Moving Records?

  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 1999
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Moving Records?

    Hi,

    I've run into a little snag on the progamming side of my site. I have two databases with identical structures, but different names.

    Now, unfortunetly, I entered a great amount of information into one database that should be in the other. Is there any way of moving records from one database to the other when their structures are identical?

    I hope so.

  2. #2
    :) delemtri's Avatar
    Join Date
    Jun 2001
    Posts
    579
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php
    $result 
    mysql_query("select * from table1"); while ($row mysql_fetch_assoc($result)) {
    $var1 $row["var1"]; $var2 $row["var2"]; $var3 $row["var3"];
    $var4 $row["var4"]; $var5 $row["var5"]; $var6 $row["var6"];
    $var7 $row["var7"]; $var8 $row["var8"]; $var9 $row["var9"];
    $result mysql_query("insert into table2 (var1, var2, var3, var4, var5, var6, var7, var8, var9) values ('$var1', '$var2', '$var3', '$var4', '$var5', '$var6', '$var7', '$var8', '$var9')");
    $result mysql_query("delete from table1 where var1='$var1' AND var2='$var2' AND var3='$var3' AND var4='$var4' AND var5='$var5' AND var6='$var6' AND var7='$var7' AND var8='$var8' AND var9='$var9'"); }
    ?>
    Last edited by delemtri; Aug 11, 2001 at 01:14.

  3. #3
    The Hiding One lynlimz's Avatar
    Join Date
    Jul 2000
    Location
    Singapore
    Posts
    2,103
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahh..if you have phpMyAdmin..it would be a breeze..to do that.
    "Imagination is more important than knowledge. Knowledge is limited. Imagination encircles the world."
    -- Albert Einstein

  4. #4
    SitePoint Enthusiast
    Join Date
    Jul 1999
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Another solutions needed

    Thank you for the script, it means a lot.

    Unfortunetly, it moves data from one table to another, and in my case, I need to move data from one database to another..

    How do I modify the script to get about this?

  5. #5
    SitePoint Addict kunal's Avatar
    Join Date
    Oct 2000
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    canuk,
    php cant connect to 2 dbases at once, so you would have to connect to one database, extract the information, disconnect, connect to the other, write info, disconnect... and go on and on...

    I would recommend reading all your data from table in batches, into an array, and moving from there... it would put less load on your server, and would be faster..
    i dunno...

  6. #6
    [Call me Bram] iBram007's Avatar
    Join Date
    Feb 2001
    Location
    Belgium
    Posts
    339
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Can't you take a drop of your entire database and import it in your other database?

    If you have access to the mysql folder of your host, you can look in the folder "data", copy the folder with your database name and past it in the data folder of your new mysql folder... Hope you understand it

  7. #7
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by kunal
    php cant connect to 2 dbases at once, so you would have to connect to one database, extract the information, disconnect, connect to the other, write info, disconnect... and go on and on...
    you don't need to disconnect at all. you can use as many DBs as you want at the same time.

    Canuk, can you recreate the tables w/ the correct columns in the newly named DB? if so, i think you can do something like this:

    PHP Code:
    $sql "INSERT INTO new_db_name.table_name (col1, col2, col3, and_so_on)
    SELECT col1, col2, col3, and_so_on FROM old_db_name.table_name"

    and you'd do that for each table you want to "move." just make sure you list the cols in the same order in the INSERT and SELECT portions.


    EDIT: what the heck am i talking about? you could also just use mysqldump to backup the tables in the old DB and then restore them to the new one.
    Last edited by DR_LaRRY_PEpPeR; Aug 11, 2001 at 14:34.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •