how to connect two mysql databases in two servers

Question

i want to connect two mysql databases one is located in localhost and other one is located in a server

here is what i have done so far and i am not getting either error or data

<?php
$con=mysql_connect('120.247.201.8:3306','root','root');
$con1=mysql_connect('localhost','root','');

//mysql_connect('localhost','root','');
if(!$con){
    die('Try Again');
}


if(!$con1){
    die('Try Again');
}

    mysql_select_db("iot",$con1); 
mysql_select_db("lora_gateway",$con);
$result =mysql_query("SELECT lora_gateway.`server_log`.`created_at`, lora_gateway.`server_log`.`temperature`FROM iot.`device` inner JOIN  `lora_gateway`.`server_log` on `lora_gateway`.`server_log`.`gateway_Id` = `iot`.`device`.`gatewayId` where iot.`device`.`deviceId`='23' ORDER BY lora_gateway.`server_log`.`created_at` desc");

$num= mysql_num_rows($result);



print_r($num);
?>

why are you doing this. localhost is used for development. i.e test data — Rotimi, Apr 25 '17 at 10:05
if you were using mysqli it would be simpler... for a million reasons, dont use the **removed** mysql driver — DevDonkey, Apr 25 '17 at 10:19

score 1 · Answer 1 · answered Apr 25 '17 at 10:14

First of all, try to accustom yourself to using PDO or at least the mysqli_ functions. It's the future. :)

mysql_query's second parameter, the connection link, is optional. If omitted, it uses the last connection opened with mysql_connect. (See php.net Documentation)

Ergo, always use $con or $con1 as 2nd parameter in order to use the correct connection.

Then, provided that your queries are correct, it should work as expected.

score 1 · Accepted Answer · edited Jun 27 '22 at 02:58

This is a solution for multiple servers, connections and DBs.

Two or more MySQL 5 db servers
Two or more locations (local and/or anything else)
Two or more different databases
Two or more tables and fields

Tested and Works fine :-)

//Define your database connections and select your database want to use. In this example I use two connections and two DBs. But you can use more than two.
 
<?php
    //MySQL Server 1
    $dbhost1 = "127.0.0.1";
    $dbuser1 = "dbuser1";
    $dbpassword1 = "dbpass1";
    $db1 = "database1";
    $connection1 = mysql_connect($dbhost1,$dbuser1,$dbpassword1) or die (mysql_error());
    mysql_select_db($db1,$connection1);
     
    //MySQL Server 2
    $dbhost2 = "xxx.xxx.xxx.xxx";
    $dbuser2 = "dbuser2";
    $dbpassword2 = "dbpass2";
    $db2 = "database2";
    $connection2 = mysql_connect($dbhost2,$dbuser2,$dbpassword2) or die (mysql_error());
    mysql_select_db($db2,$connection2);    
     
    //The SQL statement
    $sql =" SELECT database1.tablename1.fieldname1 AS field1, database2.tablename2.fieldname2 AS field2 FROM database1.tablename1,database2.tablename2";
     
    //Execute query and collect results in $results
    $results = mysql_query($sql);
     
    //Print result until end of records
    while($rows = mysql_fetch_array($results)){
        print $rows["field1"]." | ".$rows["field2"]."<br>";
    }
     
    ?>

ohh no it didnt connect those i am getting this error Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in — shane, Apr 25 '17 at 10:56
look at this http://stackoverflow.com/questions/2973202/mysqli-fetch-array-mysqli-fetch-assoc-mysqli-fetch-row-expects-parameter-1 — Med Dhiia, Apr 25 '17 at 11:07

score 0 · Answer 3 · answered Apr 25 '17 at 09:59

0

what about to add connection to mysql_query() ?

$result = mysql_query("SELECT lora_gateway.`server_log`.... desc", $con);

answered Apr 25 '17 at 09:59

Honzy

88
1
3

i am getting this error when i tried it Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in – shane Apr 25 '17 at 10:01
dont you have error in thaty query? can you try it in phpmyadmin ? – Honzy Apr 25 '17 at 10:03
one db in local and other one in a server how can try it phpmyadmin i tried with workbench but couldnt not connect local and server together – shane Apr 25 '17 at 10:06
1

You can try Navicat or DataGrip for managing databases on different servers. Also, **mysql_num_rows() expects parameter 1 to be resource, boolean given** means that `mysql_query` returned false - the query was unsuccessful. – Robin K Apr 25 '17 at 10:15
can you var_dump($result) ? I think its FALSE, that means an error in MySQL query. – Honzy Apr 25 '17 at 10:19

how to connect two mysql databases in two servers

3 Answers3