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
0
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
0
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);
}