PHP query using multiple database mysql and mssql

[ImZick]

Hi All Daniweb PHP Group,

I have a big favor to ask (well for me this is big lol).

I have 3 databases first (LOCAL),(AIMIS),(ERM).

Here's the table (namely account_manager) of LOCAL.

and Here's the table (namely customer_call) of AIMIS.

and Here's the table (namely quotations_header) of ERM.

i want to count all customer_call(created_by) and quotations_header(salesman_code) that match the localhose(erm_code) and (aimis code) so the output will be like this.

Any help will be really really apreciated.
Thank you.

[cereal]

Not sure if this is what you're searching for:

• http://dbperf.wordpress.com/2010/07/22/link-mysql-to-ms-sql-server2008/

According to the article, you run the query from MSSQL, but I don't have experience on this, I don't know if you can do the reverse action, i.e. query MSSQL from MySQL.

[ImZick]

Yes i forgot to mention it.
(LOCAL is in MYSQL),(AIMIS also MYSQL),(ERM is in MSSQL).

i will check the link and update this post. Thanks

[ImZick]

Hi @cereal.. i look into that site and unfortunately no luck at all, that is not what I'm looking for.

I need to create a query code for php for that..

Hope someone who can help me..

[urtrivedi]

In one query its not possible

You have to create 2 connections , one for mysql and another for mssql

and loop through it

[cereal]

I agree, IMO urtrivedi's suggestion is the only pratical solution.

Otherwise, if you can use MariaDB in the local installation, you could use the Connect storage engine, this enables you to create connections with remote sources, as concept it's similar to the federated engine:

• https://mariadb.com/kb/en/connect/

[ImZick]

In one query its not possible
You have to create 2 connections , one for mysql and another for mssql
and loop through it

hi @urtrivedi thanks for the reply, that's what exactly i need but can you give a sample code for that?

[urtrivedi]

this is not complied code, You need to check and correct if needed

<?php
    //mssql
    $link = mssql_connect('dbhost', 'username', 'password'); 
    mssql_select_db('database', $link); 

    $result = mssql_query("select salesman_code , count(*) ermcount from erm  group by salesman_code", $link); 


    while (($row = mssql_fetch_array($result, MSSQL_BOTH))) 
    { 
        $arrerm[$row['salesman_code']]=$row['ermcount'];//set emp code as array key and count as array value
    } 

    mssql_free_result($result); 
    mssql_close($link); 


    //mysql
    $con=mysqli_connect("example.com","peter","abc123","my_db");

    $result = mysqli_query($con,"SELECT a.full_name, a.erm_code, a.aimis_code,count(*) aimiscount FROM local a left outer join aimis b on a.aimis_code=b.created_by group by a.full_name, a.erm_code, a.aimis_code");

    echo "<table>";
        echo "<tr>";
        echo "<td>Fullname</td>";
        echo "<td>Customer call</td>";
        echo "<td>quotations</td>";       
        echo "</tr>";

    while($row = mysqli_fetch_array($result))
     {
        echo "<tr>";
        echo "<td>{$row['full_name']}</td>";
        echo "<td>{$row['aimiscount']}</td>";
        echo "<td>{$arrerm[$row['erm_code']]}</td>"; //using mssql array with code as key and fetch value
        echo "</tr>";
     }
    echo "</table>";
    mysqli_close($con);
?>