How I can cross my two different databases in two different server in PHP?

Question

I execute query that return for me a fields of my first database in HTML table and one of this fields field "number" and i want to query another database using the same field "number" to return a field "price" in my second database.

"number" it's the same filed in my two databases.

How can I do that in php ?

This my Code PHP :

   <?php  
                 if(isset($_POST['date']))
                 {

                    $pro_date = $_POST['date'];
                    $sql = "
                           SELECT 
                             left(list_product_order.number,8) as number,
                             list_product_order.created_at as date,
                             date(list_product_order.received) as received_date,
                             date(list_product_order.deliv) as deliv_date,
                             list_product_order.four_name as name,
                             COUNT(id_product_order) as items,
                             MONTH(list_product_order.created_at) as month,
                             -1 as price

                           FROM `list_product_order`

                           WHERE YEAR(list_product_order.created_at) = $date

                           GROUP BY list_product_order.number,month

                           ORDER BY list_product_order.created_at DESC 

                           ";

                           $datao->exec ( "set names utf8" );
                           $req = $datao->prepare($sql);
                           $req->execute();
                           $fquery = $req -> fetchAll(PDO::FETCH_ASSOC);

                }

                 $sqll = "
                         SELECT 
                         data_command.number as number,
                         data_command.sup_name as sup_name,
                         SUM(data_command_item.value * data_command_item.quantity) as price                                              

                         FROM data_command
                         LEFT JOIN data_command_item on data_command.id_number = data_command_item.fk_number
                         WHERE (data_command_item IN (implode(',',$fquery)))
                         ";

                   $datam->exec ( "set names utf8" );
                   $req = $datam->prepare($sqll);
                   $req->execute();
                   $squery = $req -> fetchAll(PDO::FETCH_ASSOC);
                         ?>

         <div class="panel-body">
             <?php if(isset($_POST['pro_date']))  
             { 

             foreach($fquery as $key => $value)
                      {
                         foreach($squery as $value2)
                         {
                             if($value['number'] === $value2['number'])
                             {
                                $fquery[$key]['four_name'] = $value2['sup_name'];
                                  $fquery[$key]['price'] = $value2['price'];

                             }               
                         }
                       }

                       echo '

                       <table class="table table-striped table-bordered table-hover display" id="table_with_sorting" style="zoom: 85%">
                       <thead>
                       <tr>
                       <th style="font-size:11px">Number</th>
                       <th style="font-size:11px">Date</th>
                       <th style="font-size:11px">Name</th>
                       <th style="font-size:11px">Items</th>
                       <th style="font-size:11px">Received_date</th>
                       <th style="font-size:11px">Deliv_date</th>
                       <th style="font-size:11px">Month</th>
                       <th style="font-size:11px">Price</th>
                     </tr>
                     </thead>
                     <tbody>';
               <?php


                   foreach($fquery as $f)
                    {
                    echo "
                         <tr style='text-align: center;'>
                         <td>".$f['number']."</td>
                         <td>".$f['date']."</td>
                         <td>".$f['name']."</td>
                         <td>".$f['items']."</td>
                         <td>".$f['received_date']."</td>
                         <td>".$f['deliv_date']."</td>
                         <td>".$f['month']."</td>
                         <td>".$f['price']."</td>
                         </tr>";
                  }

                  echo" </tbody>
                   </table>";
                   ?>

                    <?php   }
                        ?>
                                </div>
                            </div>
                        </div>
                            <?php
                        ?>
                </div>

This my query for second database :

SELECT 

      data_command.number as number,
      data_command.sup_name as sup_name,
      SUM(data_command_item.value * data_command_item.quantity) as price                                              

FROM  data_command

LEFT JOIN data_command_item on data_command.id_number = data_command_item.fk_number

It display for me one number with -1 in price.

Thanks for any help.

Answer 1

You can do this by using multiple database instances and load it data from that databases into separate arrays(See $arrayOne and $arrayTwo below).

Your result set will have a relationship(See the "number" field below) which you can use to append $arrayTwo values to $arrayOne using a nested foreach loop.

.You can then loop through $arrayOne and display it in a html Table.

NB!!
The following code:(isset($items['sup_name']) ? $items['sup_name'] : 'No Supplier')
Checks if a supplier exist, if not it will display "No Supplier".

PHP Code

<?php
//ArrayOne is the result from DB 1
$arrayOne = array(
    array("number" => 5, "date" => "12-01-2011", "name"=> "Jack","items" => 30),
    array("number" => 8, "date" => "12-05-2015", "name"=> "John","items" => 20),
    array("number" => 3, "date" => "12-08-2014", "name"=> "Sarah","items" => 320),
);
//Result from DB 2
$arrayTwo = array(
    array("number" => 3, "sup_name" => "Coke","price" =>"25000"),
    array("number" => 8, "sup_name" => "Simba","price" =>"1200"),
);
foreach($arrayOne as $key => $value){
    foreach($arrayTwo as $value2){
        if($value['number'] === $value2['number']){
            //add "sup_name" and "price" to arrayOne
            $arrayOne[$key]['sup_name'] = $value2['sup_name'];
            $arrayOne[$key]['price'] = $value2['price'];
        }               
    }
}
echo '
<table class="table table-striped table-bordered table-hover display" id="table_with_sorting" style="zoom: 85%"> 
<thead> 
<tr> 
<th style="font-size:11px">Number</th> 
<th style="font-size:11px">Date</th> 
<th style="font-size:11px">Name</th> 
<th style="font-size:11px">Items</th> 
<th style="font-size:11px">Supplier Name</th> 
<th style="font-size:11px">Price</th> 
</tr> 
</thead> 
<tbody> ';
foreach($arrayOne as $items)
{
    echo "<tr style='text-align: center;'><td>".$items['number']."</td> 
<td>".$items['date']."</td>
<td>".$items['name']."</td>
<td>".$items['items']." </td>
<td>". (isset($items['sup_name']) ? $items['sup_name'] : 'No Supplier')." </td>
<td>". (isset($items['price']) ? $items['price'] : '0.00')." </td></tr>";
}
echo '</tbody> 
</table> ';   
?>

Result

PHP Fiddle Demo with source code in PHP fiddle

Answer 2

If I understand you right, the result from the first query return a column you named "number".

Now you want to query another database, using the numbers returned from the first query. Right?

When you use a database from the same vendor you can often join across databases. But you can also do it "by hand".

Just store the "number"s from the first query in an array, and use that array to query the second database, using something like:

$ArrMyNumbers = array();
// fill it with numbers from first resultset.
// pseudocode, I don't know your exact situation
foreach ($myFirstResultSet as $one){
  $ArrMyNumbers[] = (int)$one["number"];
}

And then for your second query:

$SQL2 = "SELECT data_command.number as number,
        data_command.sup_name as sup_name,
        SUM(data_command_item.value * data_command_item.quantity) as price                                              
            FROM data_command
WHERE (data_command_item IN (".implode(",",$ArrMyNumbers).")) "

Now you can approach in different ways to combine the data from the first set with the second.

Assuming you can represent the resultset in PHP, I advice you to store the whole resultset in an array, using a key, being "number" in this case. Make sure it is unique! Also, just add to your first query a field named "price", and make it always -1 to indicate that is isn't set yet. (This is only for yourself, so you can easily see you haven't found a price in the second database.)

eg:

    $sql = "
            SELECT 
            left(list_product_order.number,8) as number,
            -1 as price,
            list_product_order.created_at as date,

Next, use something like this:

$firstRS = array();
foreach ($data_req as $one){
   $firstRS[$one["number"]] = $one;
}

Now you have your resultset mapped, with "number" being the index. This makes it easy to find the right row.

Now to the second query, once you ran it, you simply loop over the resultset, pick up both "number" and "price", and use them to update the first resultset (named $firstRS here by me)

Something like:

foreach ($RS_Second as $one){
   $firstRS[$one["number"]]["price"] = $one["price"];
}

Now you have $firstRS to work with, including updated prices from the second query.

Please note that this is a general approach that works across different databases. When you are working with the same database-vendor, it might be easier to simply use the (more complicated) join syntax ACROSS databases. Just look it up on google for your database (use words like cross join across database X, where X is your database)