I'm trying to finish a php script that selects all from a table on one server connection and insert all into an identical (dumped clone) table on our second server. My connections are successful and my select query works, but now I just need to figure out my INSERT portion. This is meant to update a table on server 2 from an identical table on server 2, so I only want to insert new records that have occurred since the last insert.
The one way I know I can check if a record exists or not is by checking for SESSIONID which is an Auto Incrementing Primary Key. I feel this should be pretty simple for someone who knows there way around an issue like this. Again, I want to connect to server 1, select all from cdrdb.session, fetch results, connect to server 2 and insert those into the identical ambition.session table if they don't already exist.
The route I'm attempting to take by selecting all from the table on server 1 and looping the fetched rows to insert on server 2:
// Create connection
$conn = new mysqli($servername, $username, $password);
$conn2 = new mysqli($servername2, $username2, $password2);
//Query to select * from Session table on server 1
$query1 = "select * from cdrdb.session";
$results = mysqli_query($conn1, $query1);
foreach ($results as $r => $result) {
//This is where I would create my insert statement for each numRow from the fetch but I
would need to check ```SESSIONID``` to see if it exists on server 2. If not, insert it.
}
If I didn't do the fetch/foreach loop, and I just did the select query, this is the insert statement I would have originally used even though I know where not exists is incorrectly used in syntax here:
$stmt1 = mysqli_prepare($conn2, "insert into ambition.session a where not
exists(a.SESSIONID)");
mysqli_stmt_execute($stmt1) or die(mysqli_error($conn2));
I don't know that it's possible to do it in that fashion though, without doing the foreach loop. Lastly, if I knew the PHP syntax I could just do my select query and then use INSERT IGNORE INTO ambition.session. However, since cdrdb.session has 35 columns I wouldn't know how to insert all. I don't know if there's a way to do that without putting VALUES(?,?,?,?) with 35 question marks.
I really think the first option with the foreach loop is the way to go, I just need help finishing the loop and constructing my INSERT.
Can someone confirm that is the best way and help me flesh it out?
