Transfering data over different servers.

[OsirisElKeleni]

Hello everybody!

 

I'm here today with a mystery i can't solve...

The problem i'm having is that i need to be able to transfer mysql data from one server to another one.

Retrieving data from the one database is easy, but making it transfer to another server is not that easy for me.

I have it printing out the data ATM

 

This is how i retrieve data from one server:

<?php 
$dbhost = "";
$dbname = "eg_xenforo";
$dbuser = "";
$dbpass = "";
$conn = mysqli_connect("$dbhost","$dbuser","$dbpass","$dbname") or die("Error " . mysqli_error($conn)); 

$sql=	"SELECT username
		FROM xf_user
		WHERE FIND_IN_SET('9',secondary_group_ids)
        UNION
		SELECT username
		FROM xf_user
		WHERE FIND_IN_SET('10',secondary_group_ids)
        UNION
		SELECT username
		FROM xf_user
		WHERE FIND_IN_SET('12',secondary_group_ids)";
$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
    // output data of each row
    while($row = mysqli_fetch_assoc($result)) {
		$name = $row['username'];
        echo "$name <br>";
    }
} else {
    echo "0 results";
}

?>

This is what i get as result:

CosmonautBob 
ooglebrain 
JamieKG 
Quinnter16 
Jordan 
Zoehamp 
skynet1123 
misscupcake1306 
Sir Crimson

These are usernames that should be added onto another server in a database table.

Any suggestions?

 

Thanks in advance!

[Barand]

Store the data in an array then connect to the second server and write the data

[Alex_]

Depends on the second server, really. Does it have an API? Or some script that can insert the data to that second server's database?

 

You could just convert the data selected from server1 into an array and send that as the body in a POST(or PUT) request to server2 provided server2 has a script to handle that.

[OsirisElKeleni]

Thanks for the quick response guys!

 

However how will i do this? can i connect to another server in the same script?

Store the data in an array then connect to the second server and write the data

 

The second server is a mysql database as well if that is what you mean.

If you could give me a little start to what functions i should use i can work my way from there i think.

Depends on the second server, really. Does it have an API? Or some script that can insert the data to that second server's database?

 

You could just convert the data selected from server1 into an array and send that as the body in a POST(or PUT) request to server2 provided server2 has a script to handle that.

 

Thanks!

[Alex_]

The way I see it you have two options:

 

1. Send a request with the data you want inserted to server2

2. After getting the data from server1 (your result), you setup another Database connection to the database on server2

 

Personally I would opt for the second option, if that is available to you. Then you could simply loop over your results. Pardon me if the code example looks a bit iffy, I genuienly don't use mysqli, but I'm sure you'll get the idea.

 

 

$databaseServer2 = mysqli_connect($host, $user, $pass, $db);
while($row = mysqli_fetch_assoc($result)) {
$name = $row['username'];
$databaseServer2->query('INSERT INTO table(name) VALUES($name)');
}

 

 

As long as I understood what you wanted to do correctly, then this should work

[OsirisElKeleni]

The way I see it you have two options:

 

1. Send a request with the data you want inserted to server2

2. After getting the data from server1 (your result), you setup another Database connection to the database on server2

 

Personally I would opt for the second option, if that is available to you. Then you could simply loop over your results. Pardon me if the code example looks a bit iffy, I genuienly don't use mysqli, but I'm sure you'll get the idea.

$databaseServer2 = mysqli_connect($host, $user, $pass, $db);
while($row = mysqli_fetch_assoc($result)) {
$name = $row['username'];
$databaseServer2->query('INSERT INTO table(name) VALUES($name)');
}

As long as I understood what you wanted to do correctly, then this should work

Alright i think you're saying its possible to connect to 2 different servers in one script?

I've looked up on this and i found this:

<?php
// we connect to example.com and port 3307
$link = mysql_connect('example.com:3307', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);

// we connect to localhost at port 3307
$link = mysql_connect('127.0.0.1:3307', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>

Do you know for sure its possible to connect to 2 different servers at once?

If so i'd love to have a confirmation on that because it all seems not possible ;p

[Barand]

Rather than have a loop running the inserts a multiple insert would be fare more efficient, eg

INSERT INTO table2 (username) VALUES
('CosmonautBob'), ('ooglebrain'),('JamieKG'),('Quinnter16')

So your code would be

$names = array();
while($row = mysqli_fetch_assoc($result)) {
        $name = $row['username'];
        $names[] = "('$name')";
    }

$conn->close();
        //// now connect to the second server
$conn2 = new mysqli(HOST2,USERNAME,PASSWORD,DATABASE);
$sql = "INSERT INTO table2 (username) VALUES\n" . join(',', $names);
$conn2->query($sql);

Edited November 2, 2014 by Barand

[Alex_]

Alright i think you're saying its possible to connect to 2 different servers in one script?

I've looked up on this and i found this:

<?php
// we connect to example.com and port 3307
$link = mysql_connect('example.com:3307', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);

// we connect to localhost at port 3307
$link = mysql_connect('127.0.0.1:3307', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_close($link);
?>

Do you know for sure its possible to connect to 2 different servers at once?

If so i'd love to have a confirmation on that because it all seems not possible ;p

 

 

Of course. You can connect to however many servers you want from a script.

 

The thing you have to take in mind is configure the firewalls so that server2 accepts connections from server1, for instance. And also make sure the mysql user is allowed to come from server1 to server2. After that, you should be fine.

[ginerjm]

Isn't the real question - is the second server in the same Domain?

[OsirisElKeleni]

Isn't the real question - is the second server in the same Domain?

it isn't, thats why i'm referring to it as a different server

[ginerjm]

So - you have to have permission to access it (the domain basically) and either have permissions to reach the database or have use of their API.  Otherwise you out of luck, no?

[OsirisElKeleni]

i can get acces to it the 2 domains. Then what what Alex and Barand suggested is the right thing right?

[ginerjm]

If you have the proper credentials to connect to the second domain's database then it is pretty simple I would think.  First you connect to your db server (db), grab some data, close the conn and open a new conn on the other db server and insert that data.

 

Of course I haven't done this but I'm sure that Barand will guide you thru this if this approach has some pitfall to it.

[OsirisElKeleni]

alright i just thought that if i would get the data and close the connection the data would be lost.

Thanks for the guiding guys!

[ginerjm]

The data will be lost if you close the connection, yes.  But you simply need to do a fetchall to pull it into your own local array variable to retain it.

 

(not sure but if you don't close the conn but simply open the second new conn with a new variable you might hold on to the data)

[Barand]

If, as I suggested in reply #7, your data is stored in an array the data will not be lost. Only data in any unsaved result sets will be lost when the connection closes.

 

There is no problem with two connections. I have done it many times when I needed to connect to a development and production server in the same script or when a script required connections to MySql server and an MS SQL server

[OsirisElKeleni]

If, as I suggested in reply #7, your data is stored in an array the data will not be lost. Only data in any unsaved result sets will be lost when the connection closes.

 

There is no problem with two connections. I have done it many times when I needed to connect to a development and production server in the same script or when a script required connections to MySql server and an MS SQL server

Okido!

I will try it and get back to you with results or errors i can't solve thanks a lot!