Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Jan 2012
    Location
    Latvia
    Posts
    65
    Thanks
    8
    Thanked 2 Times in 2 Posts

    Thumbs up [PHP\MySql] Transfer data between two tables..

    SOLVED!
    Solution posted below..

    Hello guys..!

    I'm having some trouble transferring some data dynamicly from one table of the database to another.

    The Database and Table Structure is IDENTICAL. But the data, and entry KEYS are DIFFERENT.

    What I need to do is to transfer data from `DB_1.DATA_TABLE` to `DB_2.DATA_TABLE`, but the Entry KEY must be specific and I want to create that Key by my own.

    I need to do this loop for about 20 different tables, and every table have different structure.. Only the Source\Target DBs ar Identic.

    I am doing this in a loop, I am trying like this now, but cant get it working:
    Note: All the Variables are passed correctly, I just need a correct syntax to get this working.


    PHP Code:
    while($TransferData mysql_fetch_array($SqlSource)) {
                                                            
        
    KOV::reconnect();                            
        
    mysql_query("INSERT INTO `wow_characters_target`.`account_data` (`accountId`) VALUES ('" .$SqlTarget['accountId']. "') SELECT * FROM `wow_characters_source`.`account_data` WHERE `accountId` = '" .$TransferData['accountId']. "'");
        } 
    I made a solution my self, and the problem is solved. Thank you!

    PHP Code:

    # This code here is located in another loop..

    $SqlGetStructure    mysql_query("DESCRIBE `" .$table_name"`");
    $ConstructedData    = array();        
    $ConstructedColumns '';
    $ConstructedValues  '';

    # Make an Array with the data needed to insert into the Target Table..

        
    while($DescribeData mysql_fetch_array($SqlGetStructure)) {
                                    
            if(
    $DescribeData['Field'] != 'accountId'){ 
                                        
                
    $ConstructedData[$DescribeData['Field']] = $TransferData[$DescribeData['Field']];
            }
        }

    # Set the Unique data..

            
    $ConstructedData['guid']      = $SqlLastId['guid']+1;
            
    $ConstructedData['account'] = $target_key;

    # Construct the Structure and Values to be Mysql Query Ready..

        
    foreach($ConstructedData as $key => $data) {
                                                    
            
    $ConstructedColumns .= '`' .$key'`,';
            
    $ConstructedValues  .= "'" .addslashes($data). "',";
        }

            
    $ConstructedColumns substr_replace($ConstructedColumns ,"",-1);    
            
    $ConstructedValues  substr_replace($ConstructedValues ,"",-1);

    # Execute the Query

             
    $Execute mysql_query("INSERT INTO `characters`(" .$ConstructedColumns") VALUES (" .$ConstructedValues")"); 
    Thank you for your help,
    Ralph!
    Last edited by WingTsun; 10-17-2012 at 11:40 PM.
    "Be formless... shapeless, like water. Now you put water into a cup, it becomes the cup. You pour water into a bottle; it becomes the bottle. You put water into a teapot; it becomes the teapot. Now water can flow, or creep or drip or crash! Be water, my friend..." by Bruce Lee

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    INSERT. . . SELECT commands do not allow you to specify values for individual fields. You need to specify all the fields and then apply any constant data to the select. So no select *. See the api documentation for the insert. . . select here: http://dev.mysql.com/doc/refman/5.1/...rt-select.html
    I don't know why you need to bother with this though tbh. The key should never need to change.

  • #3
    New Coder
    Join Date
    Jan 2012
    Location
    Latvia
    Posts
    65
    Thanks
    8
    Thanked 2 Times in 2 Posts
    So, I will need to make an individual query for every table I want to merge?
    "Be formless... shapeless, like water. Now you put water into a cup, it becomes the cup. You pour water into a bottle; it becomes the bottle. You put water into a teapot; it becomes the teapot. Now water can flow, or creep or drip or crash! Be water, my friend..." by Bruce Lee

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    I don't understand your question. You'd need to create a new query for anything that differs, yes, but that would be required as something has changed anyways (fields, tables, whatever). In the above case, you'll likely need to issue an insert on each record anyways (use a combined insert or a batch using a prepared statement and bind them) unless there is an automated fashion in which a new key can be generated. If there is a way to automate the key such as UUID or autoincrement for a primary key, then you can simply issue a single INSERT. . .SELECT call.

  • #5
    New Coder
    Join Date
    Jan 2012
    Location
    Latvia
    Posts
    65
    Thanks
    8
    Thanked 2 Times in 2 Posts
    The problem is solved now. Thank you! I posted the solution in the Original Post, and set the status as "resolved".
    "Be formless... shapeless, like water. Now you put water into a cup, it becomes the cup. You pour water into a bottle; it becomes the bottle. You put water into a teapot; it becomes the teapot. Now water can flow, or creep or drip or crash! Be water, my friend..." by Bruce Lee


  •  

    Posting Permissions

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