dongshubang7816 2017-08-04 12:53
浏览 52
已采纳

用于处理多个连接的PHP脚本

I'm trying to create a php script that I can direct to in Windows Task scheduler because I need this job to run every 15 mintues and insert in a database table.

My issue currently is that I have a working query in MySQL Workbench, but for privilege reasons I now need to select data from a table on a read only database and insert it into a table in our production database (different host IP).

Basically, right now I'm selecting from readTable on host 1 and inserting to writeTable on host 1. However, I need to select/join from readTable on host 1 and insert on writeTable on host 2, if that makes sense.

The problem: I don't know how to use multiple db connections like that in a php file, and I couldn't even find a way to do it in workbench.

Here's the php code with query:

        <?php 

    $servername = "localhost";
    $username = "username";
    $password = "password";

    $servername2 = "localhost";
    $username2 = "username";
    $password2 = "password";

    // Create connection
    $conn = new mysqli($servername, $username, $password);
    $conn2 = new mysqli($servername2, $username2, $password2);

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }
    echo "Connected successfully";

    // Check connection2
    if ($conn2->connect_error) {
        die("Connection failed: " . $conn2->connect_error);
    }
    echo "Connected successfully";

    $data = mysqli_query($conn, "SELECT 
                         c.extension as Extension
                        ,RESPONSIBLEUSEREXTENSIONID as ExtID
                        , sum(Duration) as Total_Talk_Time_seconds
                        , round(sum(Duration) / 60,2) as Total_Talk_Time_minutes
                        , sum(if(LEGTYPE1 = 1,1,0)) as Total_Outbound
                        , sum(if(LEGTYPE1 = 2,1,0)) as Total_Inbound
                        , sum(if(Answered = 1,0,1)) as Missed_Calls
                        , count(DISTINCT b.NOTABLECALLID) as Total_Calls
                      --  , NOW()
                          FROM cdrdb.session a
                          LEFT JOIN cdrdb.callsummary b
                           ON a.NOTABLECALLID = b.NOTABLECALLID
                             LEFT join cdrdb.mxuser c
                              ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID
                               WHERE b.ts >= curdate()
                               AND c.extension IN (7295,7306,7218,7247,7330,7000,7358)
                          group by c.extension");

    foreach ($data as $d) {

    mysqli_query($conn2, "Insert into test.ambition_test(Extension, ExtID, Total_Talk_Time_seconds,Total_Talk_Time_minutes,Total_Outbound, Total_Inbound, Missed_Calls, Total_Calls, Time_of_report )   
                            ON duplicate key update Total_Talk_Time_seconds = values(Total_Talk_Time_seconds), Total_Talk_Time_minutes = values(Total_Talk_Time_minutes), Total_Outbound = values(Total_Outbound), Total_Inbound = values(Total_Inbound)
                  , Missed_calls = values(Missed_Calls), Total_Calls = values(Total_Calls), Time_of_report = values(NOW())");
    }

     ?>

Is it possible to create multiple connections and dictate which ones to use in each part of that query?

  • 写回答

3条回答 默认 最新

  • drt3751 2017-08-04 13:12
    关注

    Make a connection to your first server:

    $conn = new mysqli($servername, $username, $password);
    

    Make a connection to your second server - defined in a separate variable:

    $conn2 = new mysqli($servername2, $username2, $password2);
    

    You can get data (SELECT) from the first server, e.g.

    $data = mysqli_query($conn, "SELECT ...");
    

    Then use that data - held in $data - to write (INSERT) into the database on the second server, e.g.

    mysqli_query($conn2, "INSERT ...");
    

    You will of course need a loop to cycle through $data and bind the appropriate values in the query. In pseudo-code....

    foreach ($data as $d) {
        // $d is an array of rows from the query done on the first server. 
        // Because you're in a loop you can deal with this data 1 row at a time and do whatever you want with it
        // e.g. write it to the second server's database...
        mysqli_query($conn2, "INSERT INTO some_table(v1, v2) VALUES('".$d['v1']."', '".$d['v2']."') ");
    }
    

    I use PDO and am unfamiliar with the mysqli_query syntax. You may need to bind the parameters (as you do in PDO) rather than reference them in the query. But the principle of what I've written is the same - you loop through the data obtained from the first server, and write it to the second server inside a loop. This lets you deal with each row of data coming from the query on the first server.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(2条)

报告相同问题?

悬赏问题

  • ¥15 MATLAB怎么通过柱坐标变换画开口是圆形的旋转抛物面?
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿