PHP script to handle multiple connections

Question

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?

Your script is only making 1 connection (as defined in $conn). Define another connection (e.g. $conn2) and then reference that when making a query, e.g. (mysqli_query($conn2, "...")); — Andy
– Andy, Commented Aug 4, 2017 at 12:58
But if I make 2 connections, can I reference both of them? My query is selecting from one database and inserting to another — Geoff_S
– Geoff_S, Commented Aug 4, 2017 at 12:59
Do you mean reference them both in one query, or in separate queries? The first is impossible but the latter is possible, i.e. you can select from one database, then insert to another. — Andy
– Andy, Commented Aug 4, 2017 at 13:08
I was hoping to one query, but I can select all from the first one where current date is today and the extension matches. But would I then need to store those results somehow and pass them to be used in an UPSERT statement for the second db — Geoff_S
– Geoff_S, Commented Aug 4, 2017 at 13:10

Andy · Accepted Answer · 2017-08-04 13:18:21Z

3

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.

edited Aug 4, 2017 at 13:18

answered Aug 4, 2017 at 13:12

Andy

5,43813 gold badges67 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Geoff_S Over a year ago

This is extremely helpful, thank you! I just need to refine my code and then I'll be able to test it by running the script in task scheduler. I also meant to mention, this is just local so I didn't have to worry about injections or anything but I do appreciate the note on PDO

Andy Over a year ago

Glad it helped. I'd strongly recommend you pay attention to injection, even if it is just local. The reason I wrote that my last code sample was "pseudo-code" is because you should never write it in that way where you're just inserting values directly into the query - as that is where injection attacks can come from. PDO makes that a lot easier with bound parameters, but I'm not sure what the options are in mysqli_ for this. But please look into it for your own sake :)

Geoff_S Over a year ago

So I updated my code to reflect that answer slightly, but I have a confusion on the foreach loop. You can see in my insert statement that I'm inserting and updating on duplicate key so I have value for each row, and then in Time_Of_Report I need to insert a NOW() timestamp. How exactly should I be modifying that for data?

Andy Over a year ago

I'm unclear what you're trying to do. The code doesn't look right though because you haven't referenced $d anywhere, and that is the source data from the first database, at least once it's inside your foreach

Andy Over a year ago

$d is an array of all the data in one row of your SELECT query from the first server. If you put in var_dump($d); die; and run it, you'll see what that array looks like - it has ALL the values in it from the SELECT. You can then use them where needed, e.g. $d['some_column_name']. How you bind them to the query is something you'll have to look up as I don't know what the syntax for mysqli_ is. I know it for PDO but it's not the same.

|

rebru · Accepted Answer · 2017-08-04 13:00:51Z

2

Sure is that possible, but you have to bind it. If you want to use selfmade connections (not over a framework) then you have to build multiple connections like ....

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

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

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

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

// Create connection
$conn2 = new mysqli($servername, $username, $password);

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

Then you can use each connection with accessing the right $conn or $conn2 variable.

answered Aug 4, 2017 at 13:00

rebru

5193 silver badges10 bronze badges

2 Comments

Geoff_S Over a year ago

That makes sense, but one thing (sorry, I'm just a bit confused): If my one query posted above is selecting from one and inserting/updating in another connection, how can I declare both connections in the query?

rebru Over a year ago

That isnt possible in my view. Then you have to select first from one connection and then inserting it with the second connection. if you have multiple rows from the selecting database, then you have to loop for inserting (single or bulk inserts).

Moiz Shafqat Husain · Accepted Answer · 2017-08-04 13:41:39Z

0

$conn = new mysqli($host, $username, $password); // connection 1
$conn2 = new mysqli($host, $username, $password); // connection 2

user $conn1 and $conn2 alternatively as per your purpose...

answered Aug 4, 2017 at 13:41

Moiz Shafqat Husain

2192 silver badges14 bronze badges

Collectives™ on Stack Overflow

PHP script to handle multiple connections

3 Answers 3

7 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related