php update field from one server to another

[paulvz]

Hi there. I need help to update fields on another server from my server.

 

<?php

$dbhost1 = "dedi212.jnb2.host-h.net";

$dbuser1 = "gilan";

$dbpassword1 = "********";

$db1 = "risingfs_rcjan2010";

$connection1 = mysql_connect($dbhost1,$dbuser1,$dbpassword1) or die (mysql_error());

mysql_select_db($db1,$connection1);

 

$dbhost2 = "41.66.140.74";

$dbuser2 = "*********";

$dbpassword2 = "**********";

$db2 = "edutoy";

$connection2 = mysql_connect($dbhost1,$dbuser1,$dbpassword1) or die (mysql_error());

mysql_select_db($db2,$connection2);   

 

 

$mysql_query ="

INSERT INTO risingfs_rcjan2010.aa(risingfs_rcjan2010.aa.a,risingfs_rcjan2010.aa.b)

 

SELECT edutoy.products.id,edutoy.products.NAME FROM edutoy.products.products

 

}

?>

 

 

[trq]

Your forgot to ask a question.

[paulvz]

Hi there. I need help to update fields on another server from my server.

 

<?php

$dbhost1 = "dedi212.jnb2.host-h.net";

$dbuser1 = "gilan";

$dbpassword1 = "********";

$db1 = "risingfs_rcjan2010";

$connection1 = mysql_connect($dbhost1,$dbuser1,$dbpassword1) or die (mysql_error());

mysql_select_db($db1,$connection1);

 

$dbhost2 = "41.66.140.74";

$dbuser2 = "*********";

$dbpassword2 = "**********";

$db2 = "edutoy";

$connection2 = mysql_connect($dbhost1,$dbuser1,$dbpassword1) or die (mysql_error());

mysql_select_db($db2,$connection2);   

 

 

$mysql_query ="

INSERT INTO risingfs_rcjan2010.aa(risingfs_rcjan2010.aa.a,risingfs_rcjan2010.aa.b)

 

SELECT edutoy.products.id,edutoy.products.NAME FROM edutoy.products.products

 

}

?>

 

I have tried the above statement, but nothing happens? i need to export data from my sugarcrm database to a data warehouse on another server. any help will be appreciated

[jayarsee]

Could you clarify this bit:

 

$mysql_query ="
INSERT INTO risingfs_rcjan2010.aa(risingfs_rcjan2010.aa.a,risingfs_rcjan2010.aa.b)

SELECT edutoy.products.id,edutoy.products.NAME FROM edutoy.products.products

}

 

Your query doesn't have a terminating " or ; and you have a closing } with no opening {, so it's a little unclear what you've tried. In general the best method for doing this depends on how well your solution needs to scale. When the term "data warehouse" is in use there's a possibility that you're working with a lot of data or that this happens often. If that is the case, the best solution involves replication and stored routines for transforming the incoming data (since it looks like it gets stored differently on one server versus the other).

 

If you're using MySQL, some info can be found on that here:

http://dev.mysql.com/doc/refman/5.0/en/replication.html

http://dev.mysql.com/doc/refman/5.1/en/stored-routines.html

 

If it's more of a low volume thing, you'd need to pull the data out with one SQL statement, use PHP to prepare it to be inserted into the other database server, and then insert it with a separate statement. Because of the syntax issues with your example I'm not sure if you're trying to execute two queries at once.

[paulvz]

Thanks for the response.

 

It is not a lot of data i need to send them

about 2600 rows.

 

Yes you are correct i am trying to do the 2 at once.

 

Read from my local servers mysql, and then inserting into the warehouse the data wich is on another server.

 

Regards

 

Paul

[jayarsee]

In that case you simply need to do this as separate queries, one per database, and it should work fine.

[paulvz]

thanks for the quick reply - i should have mentioned i am a php newbie!!!!!!

 

If you say 2 seperate queries? tot php files, or split my code up?

And how di i then tel what should be imported?

 

Sorry for the questions but this confuses the hell outa me.

 

Regards

 

Paul.

 

Ps one up for PHPFreaks. Out of 20 posts on 20 different forums this is the first response and within an hour. This is great

[paulvz]

Do you mean something like this

 

<?php

 

//MySQL Server 1

$dbhost1 = "dedi212.jnb2.host-h.net";

$dbuser1 = "******";

$dbpassword1 = "*********";

$db1 = "risingfs_rcjan2010";

$connection1 = mysql_connect($dbhost1,$dbuser1,$dbpassword1) or die (mysql_error());

mysql_select_db($db1,$connection1);

 

 

//The 1st SQL statement

$mysql_query ="

INSERT INTO risingfs_rcjan2010.aa(risingfs_rcjan2010.aa.a,risingfs_rcjan2010.aa.b)";

 

//MySQL Server 2

$dbhost2 = "41.66.140.74";

$dbuser2 = "**********";

$dbpassword2 = "**************";

$db2 = "edutoy";

$connection2 = mysql_connect($dbhost1,$dbuser1,$dbpassword1) or die (mysql_error());

mysql_select_db($db2,$connection2);   

 

//The 2nd SQL statement

$mysql_query ="

SELECT edutoy.products.id,edutoy.products.NAME FROM edutoy.products.products";

?>

 

 

[jayarsee]

You're welcome.

 

One PHP file, no need for two. You just need to split the code up a little bit.

 

The idea is, you execute a SELECT query and store the result of that in a variable, let's say $products. To learn how to do that, you should study and play around with Example #2 from the mysql_query() entry in the PHP manual:

http://php.net/manual/en/function.mysql-query.php

 

Except instead of echo for the result like the manual's example shows, you'll want to store the result in an array, so instead of:

 

while ($row = mysql_fetch_assoc($result)) {
    echo $row['firstname'];
    echo $row['lastname'];
    echo $row['address'];
    echo $row['age'];
}

 

Like this:

$x = 0;
$products = array();
while ($row = mysql_fetch_assoc($result)) { // Iterate over each product result
$products[$x]['name'] = $row['name']; // $row['name'] represents the "name" field in your database
$products[$x]['id'] = $row['id'];
++$x; // increase $x by 1
}

 

Then, you use $products to build a new UPDATE (or INSERT) query, like this:

 

// Set the beginning of the SQL that occurs no matter what
$sql = "INSERT INTO products (name, id) VALUES ";

// Initialize the $rowSqls array so that it exists when we tell PHP to add to it
$rowSqls = array();

// Turn each product into its part of the SQL query
foreach($products as $product)
{
$rowSqls[] = "('" . $product['name'] . "','" . $product['id'] . "')"; // ('Hungry Hippos','19F')
}
// Combine all the product SQLs into a string separated by commas and add it to the first SQL part
// Note the period in .= instead of =, which /adds/ it to the variable instead of replacing the variable's contents
$sql .= implode(',',$rowSqls);

 

Then you execute that query separately on the other database. I haven't tested that code so I'm not sure if it's flawless but it should give you the general idea.