0

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.
7938f748bd02de5247f37aaa10a5f0c2

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

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

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.
59b919e08f3a70fbc174531f6e4a641f

Any help will be really really apreciated.
Thank you.

Edited by pritaeas: Moved to MySQL.

Attachments erm_table.jpg 8.93 KB
3
Contributors
7
Replies
41
Views
4 Years
Discussion Span
Last Post by urtrivedi
0

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

0

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..

1

In one query its not possible

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

and loop through it

0

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:

Edited by cereal

0

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?

0

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);
?>
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.