PHP script, selecting and inserting into mysql without duplicates

[thenorman138]

We have a phone system database on one server but we want an updated clone on our production server.

 

I've dumped the original and imported it to our local production server, which is great, but now I have to go about keeping it updated with any daily changes on the other server. 

 

I'm trying to create a php script that will simply check each table on the other server and then insert any new records in each table into the cloned table on our production database. 

 

Below is a test select/insert block. The select query works with no errors but my insert returns an error as I expected, because I believe I'm using ```NOT EXISTS``` incorrectly. I don't know if that's even the best  route to take.

 

The session table has about 35 columns so I'm looking for the best way to go about this without having to declare every column if possible. The only thing I though of is the ```SESSION.SESSIONID``` field which is the primary key. So my logic below was, ```if the sessionid from the phone server doesn't exist yet in the prod server, insert the whole record```.

 

So obviously my syntax is wrong on the insert, just looking for some guidance on how to insert all new records from server1.session to server2.session when this script is run every 15 minutes.

 

 

  ```

    // 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 sloppy pseudo code I was trying to use for the original insert statement, but is uses where not exists incorrectly

    $stmt1 = mysqli_prepare($conn2, "insert into ambition.session a where not 

    exists(a.SESSIONID)");

    mysqli_stmt_execute($stmt1) or die(mysqli_error($conn2));

```

 

 

[ginerjm]

Why not just copy the table once again each day?

[thenorman138]

Well another script runs every 15 minutes that uses this data so we want this script to also run every 15 minutes to stay up to date. Also, I don't want to dump or import, I just want it update with the records that don't exist.

[ginerjm]

If you have a "production server", why do you not run this update script on that server?  Why is your 'other' server the primary source of your data and your "production server" is not?  Kind of a misnomer there, isn't it?

 

Ok - so you have two db servers.  Do you run your scripts all from one server or on both?  Can one script access both db servers?  Do you have any kind of common repository that can be accessed from both script servers?  It's either that or you will probably need to send mail from one to the other so that two identical scripts residing on each separate server can get the 'other' data and use it on the 'production' db.  

 

When you figure that question out, then we/you can begin an approach.

[thenorman138]

I'm not sure, I feel like some of those questions are over the scope of what I'm trying to do. First of all, the first server is a server specifically for the database of our phone system and it's read only. So we need to copy the data into our daily use production server so that we can start creating reports locally. Also, the script i posted above does access both servers, as do my other scripts. I have one in particular that selects/joins data on the phone server, and inserts the results on our server. We're just trying to update the tables in our local server from the phone server (which is a read only database) so that the other scripts which preform calculations on some of the data can take place locally. We just want the data in our local admin database so we can control it. And All I'm missing at this point is how to properly structure an insert statement within my foreach loop to take what's already being selected and insert only the new records that don't yet exist.

[ginerjm]

I must be misunderstanding you.  You just said that your 'phone' server is 'read-only'.  Now I have never heard of a 'read-only server' but you say it is so that's that.

 

But if it is a RO server, then how does anything ever get written to that database?  And if is RO why would you need to copy it to the production server?

 

 

Forgetting all of this RO stuff.  If the script that accesses both servers is doing one update, why then can it not repeat the very same operation on the other database server?  That's the solution if everything is truly as you have just said.

 

Here's hoping.....

[thenorman138]

The server itself isn't read only. Our phone database, which is on that server, only has read permissions granted to us. Our physical phone system updates the table with call data. The other script I mentioned selects data and then inserts the explicit columns which I name in the script. This is different because I essentially want to select all from database on one server and insert all into an identical table on another database and server. I've just never had to do an operation like this in php where the select and insert data are on two different connections. If they were on the same server, I would just INSERT IGNORE and select * in one single query.

[ginerjm]

And why can you not just use your RO database as the source for these reports you need?

 

Your design is relying on a copy of the data that appears to be safely stored already.  Why do you need a copy if your can write scripts that can access the original?  Are you afraid of sapping the resources of the phone system?

[Barand]

Maybe they don't want to expose their main database server to the ravages of the internet.

[thenorman138]

Our team manager would like to have the data on our local server for many purposes: Local speeds, testing and manipulation without worrying about how clean the data is, and the fact that if I want to do a simple test report on the data I have to dump it and import it to our DB and then create a test table with queries. WE'd like to leave the phone DB where it is and have our own local version for a multitude of reasons. This has also already been decided as the method for my team, I'm just trying to complete a task that can be done multiple ways and I'm not experienced enough to say which might be better and I'd like some guidance on that if possible. 

[ginerjm]

It seems rather odd to try and keep your database 'copy' up to date at 15 min. intervals if all you want to do is non-realtime reporting or testing.  If real-time is not that critical, then why not just make it a cron job that can be scheduled once or twice a day at a certain time (or perhaps once during non-business hours) that is simply a table copy exercise?

[Sepodati]

Now that that's established, how do you determine what is new in the phone database? Is there any date information or is it only because it's not in your local copy?

[thenorman138]

There is already info. I dumped the whole phone database to clone/import into our local. So the structure is identical and it has all data up to yesterday morning. At this point, anything new in the 'session' table is any record with a SESSIONID after 15345. So each table has an Auto-Increment ID and I can use that not existing in the local table as the basis for what needs to be inserted. 

[ginerjm]

Is that SESSIONID actually a mysql auto-increment data type or is it just a sequence number assigned by your phone system?   If the former I think it would eliminate the possibility of making table copies as I last suggested. 

 

Sounds more and more like a query could be easily setup to do inserts using a sub-query select based upon your SESSIONID value.  ASSUMING that you can access both databases in a single query as I believe you said you can do.

[Sepodati]

Sounds like you first want to get the max SESSIONID from your local table, then select all the rows from the remote table with a SESSIONID greater than than that value. Then loop through and create an INSERT to put them into the local table.

 

EDIT: Or, I remember being able to select into a file, at which point you could then import that file into the local database. Still all within PHP, but rather an looping and creating inserts, you're just dumping & importing.

Edited September 19, 2017 by Sepodati

[thenorman138]

In MYSQL it's an INT with an AI and PK applied to it. Any I can access both in the script because I've established connections to the two IP addresses, but I'm not sure how I can access both in a single query necessarily

[thenorman138]

Sounds like you first want to get the max SESSIONID from your local table, then select all the rows from the remote table with a SESSIONID greater than than that value. Then loop through and create an INSERT to put them into the local table.

That sounds closer to my original idea. Can you assist me a bit on how I would do this since My current SELECT from the remote table is working?

[ginerjm]

If you have an AI as your PK it may be difficult to simply copy recs from one table to another.  Have to read up on how AI fields can/could be updated manually.

 

Or - since your phone server is responsible for assigning the SESSIONID, simply let the production server designate the SESSIONID in it's table as the PK and remove the AI setting.

[thenorman138]

I'm not really sure where to apply some of this, but I do think I've found a solution by creating the foreach loop after my select on the remote server, then doing a select on the production server to check for the ID in my array rows. If the server id isn't there then I insert it. I just declared each column in my insert

[ginerjm]

Doing queries inside of loops is definitely to be avoided.  Your situation does not necessitate breaking the rules here.

 

If you modify the structure of your prod. server's table to eliminate the AI setting on the PK, then you can simply do the query I suggested earlier since the keys from the phone server data will not conflict with the production server's db structure.  

 

Get your Sql query writer to write your queries for you.

 

1 - Either keep track of (somehow) the id that was last added to your prod server.  That or do a query on it to get the max value of id there.

2 - Write a query to insert into the prod server using the sub-query results that a query against the phone server where id > the last id inserted into the prod server (from #1)

 

That's all there is.

 

 

// first get the last id on the prod table
$q1 = "select max(SESSIONID) as last_id from proddb.tablename";
$q1_ans = $pdo->query($q1);
if (!$q1_ans)
{
    echo "Could not get last inserted id from proddb";
    exit();
}
$row = $q1_ans->fetch();
$last_id = $row[0];
 
//  now do the insert query
$q2 = "INSERT into proddb.tablename (specify every column name in the order of the phone server's table structure here)
                SELECT *  FROM phonedb.tablename
                  WHERE phonedb.SESSIONID > $last_id";
$q2_ans = $pdo->query($q2);
if (!$q2_ans)
    echo "Could not do insert query";
else
   echo "Insert query ran successfully";

 

Note:  not exactly sure about the syntax of the #2 query.  You may have to specify all of the phone server fidnames in the sub-query instead of the *.

Plus - I have never done queries that access separate servers before.  Don't know how that is done but this is how it would work if using separate dbs on a single server.  There may have to be some research into that.  If it can't be done, then you could output the results of a query on the phone server to a table and use it in your #2 query after getting it to the prod server.

[thenorman138]

THank you that gives me some great info to incorporate into what I have now to make it more future proof and more of a best practice!

[ginerjm]

Just thought of something.  You will have to have separate php connection vars for your dbs.  Like $pdo1 & $pdo2.  How you use them inside of the query #2 thought is new to me.

[Barand]

You cannot access two servers in a single query. Two databases on the same server, yes, as these are on the same connection.

 

I have never had cause to use a FEDERATED table but it might be a solution

 

https://dev.mysql.com/doc/refman/5.7/en/federated-storage-engine.html

[Sepodati]

To borrow from ginerjm's syntax, the first query is going to be

 

Query 1: SELECT MAX(sessionid) FROM proddb.tablename

 

Retrieve that value into say $max. Then the second query would be

 

Query 2: SELECT * FROM phonedb.tablename WHERE sessionid > $max

 

Now, you'll check if there are any returned rows and if there are, start looping through them. Your goal would be to create a query in this syntax:

 

Query 3: INSERT INTO proddb.tablename (sessionid, name, phone, ...) VALUES (1234, 'John', '555-555-5555'), (1235, 'Bob', '666-666-6666'), (1236, 'Roger', '777-777-7777')

 

where you build each "value" set as you loop through the results of query 2 and execute a single query when you're all done.

 

This is basically the same as what ginerjm is saying, but I don't think you can select from two different servers in the same query, as his example does. Two databases on the same server, yes, but not two servers.

 

-John

[thenorman138]

To borrow from ginerjm's syntax, the first query is going to be

 

Query 1: SELECT MAX(sessionid) FROM proddb.tablename

 

Retrieve that value into say $max. Then the second query would be

 

Query 2: SELECT * FROM phonedb.tablename WHERE sessionid > $max

 

Now, you'll check if there are any returned rows and if there are, start looping through them. Your goal would be to create a query in this syntax:

 

Query 3: INSERT INTO proddb.tablename (sessionid, name, phone, ...) VALUES (1234, 'John', '555-555-5555'), (1235, 'Bob', '666-666-6666'), (1236, 'Roger', '777-777-7777')

 

where you build each "value" set as you loop through the results of query 2 and execute a single query when you're all done.

 

This is basically the same as what ginerjm is saying, but I don't think you can select from two different servers in the same query, as his example does. Two databases on the same server, yes, but not two servers.

 

-John

This actually makes the most sense. The only thing is I'm not sure of is the 'values' part of the insert. Since I've already retrieved everything in the first select statement, I would basically need to insert the values based on index or column name and loop it. What would be the syntax there, as opposed to explicit values like you showed?