Merging two mysql queries from different databases in PHP

Question

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);
?>

You're just going to have to do two separate queries, save both results to an array and then push those arrays into another array. I think you already have the right idea, and I can't really think of a better way to do it. You can't merge the queries since they're separate databases. Is the method you seem to already be using not working? — Shane Lessard, Aug 24 '15 at 18:36
@ShaneLessard The method extracts the database data as needed. The issue seems to be the merging of the data into an array. the array_merge() function seems to append the data in the second array onto the data in the first array when handling multidimensional arrays. Thanks! — T W, Aug 24 '15 at 18:52
If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Aug 24 '15 at 19:03
Yeah definitely look into PDO. It'll take you like an hour to get used to (that's how long it took me and I'm quite slow on the uptake). As far as a method to merge the arrays, why not create a new array and use array_push() to push each array to it? — Shane Lessard, Aug 24 '15 at 19:08

score 1 · Accepted Answer · answered Aug 24 '15 at 19:46

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

Thanks for helping out on this problem. The solution works well! Thanks again! ... Fin for the Win! — T W, Aug 24 '15 at 23:33

score 0 · Answer 2 · answered Aug 24 '15 at 18:38

<?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);
?>

This seems to append the results of query2 onto the results of query1. The goal is to merge the results, indexed by sku, into a single arrary with keys SKU, On Description, On Hand 1, and On Hand 2. Thanks! — T W, Aug 24 '15 at 19:04

score -1 · Answer 3 · answered Aug 24 '15 at 18:28

-1

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.

answered Aug 24 '15 at 18:28

Marc B

356,200
43
426
500

1

Thanks @marc-b. I'm not sure if the UNION sql function will work as the two databases I'm querying are located on different servers. – T W Aug 24 '15 at 18:32
1

oh, in that case, yeah... you'd need two queries, unless they're both mysql servers and you can set up a federated table. – Marc B Aug 24 '15 at 18:33

score -2 · Answer 4 · answered Aug 24 '15 at 18:38

-2

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.

answered Aug 24 '15 at 18:38

Fin

386
1
15

The JOIN sql function isn't an option as the tables are located on different hosts. Please correct me if I'm wrong. – T W Aug 24 '15 at 18:54
That's right, so i lookt a bit further. You can FEDERATE table2 from the second server on the first server: http://dev.mysql.com/doc/refman/5.0/en/federated-use.html Then you can JOIN the two. – Fin Aug 24 '15 at 18:57
Unfortunately, I only have read-only access to these databases as the software provider won't provide write access to minimize their support exposure. I think I'm limited to parsing the two arrays in PHP. Thanks! – T W Aug 24 '15 at 19:07

Merging two mysql queries from different databases in PHP

4 Answers4