在PHP中合并来自不同数据库的两个mysql查询

问题描述：

I need to merge a pair of MySQL queries from two databases each located on different servers into a single multidimensional associative array. Below is an example of the data I'm working.

The issue seems to be related to the resulting $data0 array values lacking a unique key (ideally the corresponding 'sku' value) to serve as an index for merging the data.

The desired output would be:

Array (
    [0] => Array (
        [SKU] => 101
        [Description] => Test Item 1
        [On Hand 1] => 3
        [On Hand 2] => 7
    )
    [1] => Array (
        [SKU] => 102
        [Description] => Test Item 2
        [On Hand 1] => 11
        [On Hand 2] => 15
    )
)

There may be a better way to handle the MySQL queries and desired array. If so, please let me know. Please provide any feedback as I'm a rank beginner and will take all the help I can get. Thanks!

<?
$query1 = "SELECT sku, description, on_hand as 'On Hand 1' FROM database1"

$queryresult1 = mysql_query($query1, $connection1);

$data1 = array();
while ($resultrow1 = mysql_fetch_assoc($queryresult1)) {
    array_push($data1, $resultrow1);
}

$query2 = "SELECT sku, on_hand as 'On Hand 2' FROM database2"

$queryresult2 = mysql_query($query2, $connection2);

$data2 = array();
while ($resultrow2 = mysql_fetch_assoc($queryresult2)) {
    array_push($data2, $resultrow2);
}

$data0 = array_merge($data1, $data2);

//example code for troubleshooting
$data1 = array('101' => array('SKU' => '101', 'Description' => 'Test Item 1', 'On Hand 1' => 3), '102' => array('SKU' => '102', 'Description' => 'Test Item 2', 'On Hand 1' => 11));
print_r($data1);
echo "<br /><br />";
$data2 = array('101' => array('SKU' => '101', 'On Hand 2' => 7), '102' => array('SKU' => '102', 'On Hand 2' => 15));
print_r($data2);
echo "<br /><br />";
$data0 = array_merge($data1, $data2);

print_r($data0);
?>

答

By looping trough each key in $data1, you can retreive the value from $data2 by using the current $key. This will give you the result you want:

$data1 = array('101' => array('SKU' => '101', 'Description' => 'Test Item 1', 'On Hand 1' => 3), '102' => array('SKU' => '102', 'Description' => 'Test Item 2', 'On Hand 1' => 11));
$data2 = array('101' => array('SKU' => '101', 'On Hand 2' => 7), '102' => array('SKU' => '102', 'On Hand 2' => 15));

$data0 = array();
foreach($data1 as $key => $data){
    $data['On Hand 2'] = $data2[$key]['On Hand 2'];
    $data0[] = $data;
}
unset($data1, $data2);

print_r($data0);

答

You'd probably be better off using a single UNION query for this:

SELECT sku, description, on_hand as 'On Hand 1' FROM database1
UNION ALL
SELECT sku, null       , on_hand as 'On Hand 2' FROM database2

Note the use of null for the description field in the database2 query. UNION member queries must have the same number of columns, and the same types.

Then it's a simple matter of:

while($row = mysql_fetch_assoc($result)) { 
    $data[] = $row;
}

without needing two queries, two fetch loops, and a later merge.

答

You can do that by joining the two tables in your query using the JOIN syntax: https://dev.mysql.com/doc/refman/5.0/en/join.html

So then your query would look like this:

SELECT
    table1.sku,
    table1.description,
    table1.on_hand as 'On Hand 1',
    table2.on_hand as 'On Hand 2'
FROM
    database1.table1 JOIN
    database2.table2 ON table1.sku = table2.sku

The rows from the two tables will be joined to each other where the sku field value corresponds with each other. You can make a LEFT JOIN if you want all the results from table1 and from table2 where possible.

答

<?php
// connection 1
$connection1= mysql_connect("localhost","root","");
//selecting database 1
mysql_select_db("database1",$connection1);
// select query from the database1 and table 1
$query1 = "SELECT sku, description, on_hand as 'On Hand 1' FROM database1.table1";
$queryresult1 = mysql_query($query1, $connection1);

$data1 = array();
while ($resultrow1 = mysql_fetch_assoc($queryresult1)) {
    array_push($data1, $resultrow1);
}
//connection 1
$connection2= mysql_connect("localhost","root","");
//selecting database 2
mysql_select_db("database2",$connection2);
// select query from the database2 and table 2
$query2 = "SELECT sku, on_hand as 'On Hand 2' FROM database2.table2";

$queryresult2 = mysql_query($query2, $connection2);

$data2 = array();
while ($resultrow2 = mysql_fetch_assoc($queryresult2)) {
    array_push($data2, $resultrow2);
}

$data0 = array_merge($data1, $data2);

print_r($data0);
?>

在PHP中合并来自不同数据库的两个mysql查询

相关推荐