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?