Resolved [PHP\MySql] Transfer data between two tables..

[WingTsun]

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!

[Fou-Lu]

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.

[WingTsun]

So, I will need to make an individual query for every table I want to merge?

[Fou-Lu]

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.

[WingTsun]

The problem is solved now. Thank you! I posted the solution in the Original Post, and set the status as "resolved".