从一个表中选择多个列，然后将数据插入到PHP-MySQL中另一个数据库中的另一个表中 [英] Select multiple columns from a table and insert data into another table in a different database in PHP-MySQL

问题描述

我必须从数据库中的一个表中选择数据，然后将它们插入另一个数据库中的另一个表中.下面的代码返回$select_query OK，但是$insert_query不是OK.您能更正代码并让我知道您的答复吗?

I have to select data from a table in a database and insert them into another table in a different database. The below code returns the $select_query OK, but the $insert_query is not OK. Could you please correct the code and let me know your response?

$host1 = "localhost";
$user1 = "jackpot";
$pass1 = "jackpot";
$db1 = "pinkapple";
$host2 = "localhost";
$user2 = "jackpot";
$pass2 = "jackpot";
$db2 = "blueberry";

$mysql_connection1 = mysql_connect($host1, $user1, $pass1);
mysql_select_db($db1, $mysql_connection1) or die(mysql_error());
$select_query = mysql_query("SELECT field1, field2, field3 FROM tree WHERE date_entered > '2014-01-01 16:22:00'", $mysql_connection1);
$number = mysql_num_rows($select_query);

    if ($select_query) {
        echo "Select Query is OK <br>";
        echo $number ."<br>";
    } else {
        echo "Select Query is  not OK <br>";
    }

$mysql_connection2 = mysql_connect($host2, $user2, $pass2, true);

mysql_select_db($db2, $mysql_connection2) or die(mysql_error());

    while ($row = mysql_fetch_array($select_query)) {
    $field1 = $row['field1'];
    $field2 = $row['field2'];
    $field3 = $row['field3'];
    $insert_query = mysql_query("INSERT INTO jungle (desk1, chair1, table1) VALUES ('$field1', '$field2', '$field3')", $mysql_connection2);
        if ($insert_query) {
            echo "Insert Query is OK <br>";
        } else {
            echo "Insert Query is not OK <br>";
        }
}
mysql_close($mysql_connection1);
mysql_close($mysql_connection2);

在下面的

中，您可以看到表的示意图.

below you can see the schematic image of the tables.

推荐答案

您需要建立值吗?

"INSERT INTO jungle (desk1, chair1, table1) VALUES (value1, value2, value3)"

您应该从第一次选择中收集数据，然后可以在插入语句中设置相关值

You should collect the data from the first select and then you can set the pertinent values in your insert statement

您可以使用mysql_fetch语句

$mysql_connection2 = mysql_connect($host2, $user2, $pass2, true);
mysql_select_db($db2, $mysql_connection2) or die(mysql_error());

while($row = mysql_fetch_array($select_query))
{
    $field1 = $row['field1'];
    $field2 = $row['field2'];
    $field3 = $row['field3'];
    $insert_query = mysql_query("INSERT INTO jungle (desk1, chair1, table1) VALUES ('$field1', '$field2', '$field3')",$mysql_connection2);

    if ($insert_query) {
      echo "Insert Query is OK <br>";
    } else {
      echo "Insert Query is not OK <br>";
    }

}

此语句遍历您的select语句，并使用您的INSERT语句为每个返回的结果插入一行

This statement loops through your select statement and inserts a row for every returned result using your INSERT statement

但是实际上您还应该研究mysqli，因为mysql已贬值.但是，这是您正在做的事情的基本逻辑

But really you should also look into mysqli because mysql is depreciated. This is however the basic logic behind what you are doing

这篇关于从一个表中选择多个列，然后将数据插入到PHP-MySQL中另一个数据库中的另一个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！