如何将3个数据库表组合成3个不同的数据库到数据库4中

I am trying to create a separate search site (site4 using database4) that is updated every hour from 3 different websites each having their own database. I want to combine the data from database1, database2, and database3 into database4.

我正在尝试创建一个单独的搜索站点(使用database4的site4),每个小时从3个不同的网站更新,每个网站都有自己的数据库。我想将database1,database2和database3中的数据合并到database4中。

I also want to remove the duplicates during the combining, so I was told to use the MySQL UNION function.

我还想在组合过程中删除重复项,因此我被告知使用MySQL UNION函数。

On the same server I have 4 individual websites with each site having their own MySQL database:

在同一台服务器上,我有4个单独的网站,每个网站都有自己的MySQL数据库:

site1 --> database1, 1 table, 16 fields

site1 - > database1,1表,16个字段

site2 --> database2, 1 table, 16 fields

site2 - > database2,1表,16个字段

site3 --> database3, 1 table, 16 fields

site3 - > database3,1表,16个字段

site4 --> database4, 1 table, 16 fields (currently empty)

site4 - > database4,1表,16个字段(当前为空)

All 4 databases have an identical structure where as each database has only 1 table with 16 fields.

所有4个数据库都具有相同的结构,其中每个数据库只有1个表,包含16个字段。

In all 4 databases, the table names are the same (Post_Data) and all 16 fields are identical. The index field (field 11) is named Post_Date.

在所有4个数据库中,表名相同(Post_Data),所有16个字段都相同。索引字段(字段11)名为Post_Date。

With some forum help I was able to write the following PHP code, but it does not work and I do not get errors.

通过一些论坛帮助,我能够编写以下PHP代码,但它不起作用,我没有得到错误。

Can you see what is wrong in the code below and what needs to be done to fix it?

你能看到下面的代码有什么问题吗?需要做些什么来解决它?

Thanks in advance.

提前致谢。

<?php

   // Website 1
      $host1 = 'site1.com';
      $database1 = 'data_1';
      $username1 = 'user_1';
      $password1 = 'pass_1';
      $TableName1 = 'Post_Data';

   // Website 2
      $host2 = 'site2.com';
      $database2 = 'data_2';
      $username2 = 'user_2';
      $password2 = 'pass_2';
      $TableName2 = 'Post_Data';

   // Website 3
      $host3 = 'site3.com';
      $database3 = 'data_3';
      $username3 = 'user_3';
      $password3 = 'pass_3';
      $TableName3 = 'Post_Data';

   // Website 4 - Search Database
      $host4 = 'site4.com';
      $database4 = 'data_4';
      $username4 = 'user_4';
      $password4 = 'pass_4';
      $TableName4 = 'Post_Data';

   // Connect to all 4 Databases
        $connection1 = mysql_connect($host1, $username1, $password1) or die ('Cannot connect to the database because: ' . mysql_error());
        $connection2 = mysql_connect($host2, $username2, $password2, true) or die ('Cannot connect to the database because: ' . mysql_error());
        $connection3 = mysql_connect($host3, $username3, $password3, true) or die ('Cannot connect to the database because: ' . mysql_error());
        $connection4 = mysql_connect($host3, $username4, $password4, true) or die ('Cannot connect to the database because: ' . mysql_error());

   // Combine all 3 Databases into the Search Database #4
        mysql_select_db ($database1,$connection1);
        mysql_select_db ($database2,$connection2);
        mysql_select_db ($database3,$connection3);
        mysql_select_db ($database4,$connection4);

        mysql_query("USE $database4");
        mysql_query("CREATE TABLE temp AS
         SELECT * FROM $database1.$TableName1
         UNION
         SELECT * FROM $database2.$TableName2
         UNION
         SELECT * FROM $database3.$TableName3
         ");
        mysql_query("CREATE INDEX ix_post_date ON temp.Post_Date");
        mysql_query("RENAME TABLE Post_Data TO backup, temp TO Post_Data");

   // Close databases connections
        mysql_close($connection1);
        mysql_close($connection2);
        mysql_close($connection3);
        mysql_close($connection4);

   // Finished
        $date_time = date('m-d-Y H:i:s');
        echo '<h1>Finished - '.$date_time.'</h1>';

?>

1 个解决方案

#1

You create 4 different connections to the database, but the mysql_query is going to run against one connection.

您创建了4个与数据库的不同连接,但mysql_query将针对一个连接运行。

The query will need to know which database to connect to. You can see How do you connect to multiple MySQL databases on a single webpage? for a good example

查询将需要知道要连接到哪个数据库。您可以看到如何在单个网页上连接多个MySQL数据库?一个很好的例子

You should be able to see that your create table query fails by displaying the error:

您应该能够通过显示错误来查看您的create table查询失败:

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

#1