CODEWITHSUNDEEP
phpmysqldatabase
zww
zww

Reputation: 3

PHP, mysql & two databases - updating certain rows in 2nd database table from table in 1st db with different schema

I've got the logic working (when entered directly in phpmyadmin) but not sure how it can be converted to php. I would be most grateful for assistance with this translation to php statements.

The working mysql logic is listed between the hash signs (in that I've been figuring out the other php statements required). To clarify the logic/process, I have one table in db2 holding a set of 'averages' for certain id's. I'm wanting to move these averages over to the equivalent 'average' field in a db1 table. The matching has to be done via the id field which is common to both tables, though db2 does not have an entry for every id listed in db1.

<?php
$dbhost = 'localhost';
$dbuser1 = 'dbuser1';
$dbpass1 = 'dbpass1';
$dbuser2 = 'dbuser2';
$dbpass2 = 'dbpass2';
$conn1 = mysql_connect($dbhost, $dbuser1, $dbpass1);
$conn2 = mysql_connect($dbhost, $dbuser2, $dbpass2, true);
if(! $conn1 || ! $conn2 )
{
  die('Could not connect to db1 or db2: ' . mysql_error());
}

mysql_select_db('db1', $conn1 );
mysql_select_db('db2', $conn2);

#####
update db1.gfields, db2.averages
set db1.gfields.ratingavg = db2.averages.average
where db1.gfields.id = db2.averages.id;

this is an after-fix edit to show the code that solved the issue (initially provided below by KyleMassacre & updated slightly. Works with two connections, didn't try with one.)

$q = mysql_query("select * from averages", $conn2);

while ($r = mysql_fetch_array($q)) {
$u = mysql_query("update gfields set ratingavg = " . $r["average"] . " where id = " . $r["id"], $conn1);

#####

mysql_close($conn1);
mysql_close($conn2);
?>

Upvotes: 0

Views: 622

Answers (2)

KyleMassacre
KyleMassacre

Reputation: 362

Let me try this out and sorry for any errors since I'm on my phone

$q = mysql_query("select * from db1.gfields", $conn2);

while ($r = mysql_fetch_array($q))
    $u = mysql_query("update averages set average = " . $r["ratingavg"] . " where id = " . $r["id"], $conn2);

mysql_close($conn2);

also mysql_* is pretty much deprecated so maybe at some point in time look into mysqli or possibly PDO

Updated. This is assuming your user has sufficient privs.

Upvotes: 0

Master-Guy
Master-Guy

Reputation: 196

You should make one connection to the server with an account that has access to both databases, and then use the query you already had:

update
  database1.table as t1,
  database2.othertable as t2
set
  t1.name = t2.name
where
  t1.id = t2.id

If you execute the query this way, it will only update the records where the ID matches, and it will skip all the other records. It's not possible to use two separate database connections and sync the data that way.

Upvotes: 3

Related Questions