CODEWITHSUNDEEP
phpmysqlsql-update
mar2195
mar2195

Reputation: 103

MySql - UPDATE from one database to another database

FYI: I have tried using other solutions that have been posted to resolve this query, but haven't had any success. I have decided to use the code below as it seems the most logical to accomplish my task. However, if there is a solution that is completely different, that is fine - as long as it works.

Within the same server, there are two databases with the same field names.

Both DBs:
ID,
users,
pwds,
emails

I am trying to grab all passwords from DB#2 (correct pwds) and move them to DB#1 (incorrect pwds) based upon each user's email address. Unfortunately, I cannot use ID's as they are different for each user per database.

The code below is not working. I am not receiving any errors - it's just not making the update.

Also, I realize that this type of coding has been deprecated. If anyone has this solution using newer syntax, that would be great.

<?php

$dbhost1="localhost";
$dbname1="aaaaaa";
$dbuser1="bbbbbb";
$dbpass1="cccccc";

$dbhost2="localhost";
$dbname2="dddddd";
$dbuser2="eeeeee";
$dbpass2="ffffff";

$conn1 = mysql_connect($dbhost1, $dbuser1, $dbpass1);
$conn2 = mysql_connect($dbhost2, $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);

$query = "UPDATE db1.users as t1, db2.users as t2 SET t1.pwds = t2.pwds WHERE t1.emails = t2.emails";

// LINE BELOW ADDED AFTER COMMENTS POSTED:
mysql_query($query, $conn1);

mysql_close($conn1);
mysql_close($conn2);

?>

Upvotes: 1

Views: 1943

Answers (3)

mar2195
mar2195

Reputation: 103

The folks that posted about the missing line of code regarding the mysql_query() were exactly correct. However, I felt the need to continue on with this posting - to accentuate this information - for others also searching for this solution.

The actual problem was that both users needed to be assigned to both DBs and given ALL PRIVILEGES for both DBs. As long as I was accessing one DB at a time, everything was fine. As soon as I coded two users and two DBs, the access error occurred.

TIP: As was mentioned above also, add the extra lines of code to display the errors. I've learned my lesson. Thanks to all.

...and one last INSANELY important thing was the updated syntax for the actual query:

$query = "UPDATE db1.users INNER JOIN db2.users ON (db1.users.emails = db2.users.emails) SET db1.users.pwds = db2.users.pwds";

Upvotes: 2

deemi-D-nadeem
deemi-D-nadeem

Reputation: 2514

Missing the mysql_query() after the $query

You just write like that

if you want to update in $conn1 then

mysql_query($query, $conn1);

And if you want to update in $conn2 then

mysql_query($query, $conn2);

Upvotes: 0

Suchit kumar
Suchit kumar

Reputation: 11859

after $query construction line use this line:

mysql_query($query);

Upvotes: 1

Related Questions