Join together Two diffrent Database Tables

Question

I want to join two diffrent database tables when i compared with common field (ID).The following code i was wriiten on my website but it shows an error

My code :

$conn = mysqli_connect("localhost", "username1", "password1", "databse1");
$conn1 = mysqli_connect("localhost", "username2", "password2", "database2");
$result = '';
$query = "SELECT * FROM database1.table1,database2.table1  where 

database1.table1.id=database2.table1.id ";

$sql = mysqli_query($conn,$query);


$result .='
<table class="table table-bordered">
<tr>
<th width="20%">ID</th>
<th width="10%">Qty</th>
</tr>';
if(mysqli_num_rows($sql) > 0)
{

    while($row = mysqli_fetch_array($sql))
    {
        $result .='
        <tr>
        <td>'.$row["id"].'</td>
        <td>'.$row["qty"].'</td>
        </tr>';
    }
}
else
{
    $result .='
    <tr>
    <td colspan="5">No Item Found</td>
    </tr>';
}
$result .='</table>';
echo $result;

Error Messages :

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given in

Please assist and help me...

Nishant Solanki · Accepted Answer · 2017-06-12T06:39:57.357

please try below query

select table1.*,table2.* from table1
LEFT JOIN table2 ON table2.id = table1.id

For further reading about MYSQL JOINS please check here: https://dev.mysql.com/doc/refman/5.7/en/join.html

EDIT query isn't working because there is a error in your query, you have created two connection strings $conn and $conn1 and using only 1, you dont need two different connection strings, give username1 permission of both the databases database1 and database2 and remove $conn1 only use $conn

if you are using any local server like WAMP,XAMPP etc then the root user have access to all the databases, if you are on cpanel then follow these steps to add user-database privileges: http://www.thehostingnews.com/how-to-grant-mysql-privileges-in-cpanel.html

EDIT 2 as per the OP both the databases are on different server there can be some different possible solutions already answered before can be checked from here and here

Another possible solutions

1) You can create a temporary table on database2 and insert the data from database1 to database2's temporary table and use JOIN query at database2 only

2) OR else you can use two different queries, first will fetch id from database1's table and using that id fire another query to database2's table

EDIT 3 (edited code as per my #2nd suggestion)

$conn   = mysqli_connect("localhost", "username1", "password1", "databse1");
$conn1  = mysqli_connect("localhost", "username2", "password2", "database2");
$result = '';
$query  = "SELECT * FROM database1.table1";
$sql    = mysqli_query($conn, $query);

$result .='
<table class="table table-bordered">
<tr>
<th width="20%">ID</th>
<th width="10%">Qty</th>
</tr>';
if (mysqli_num_rows($sql) > 0)
{
    while ($row = mysqli_fetch_array($sql))
    {
        $query1 = "SELECT * FROM database2.table1 where table1.id = " . $row['id'];
        $sql1   = mysqli_query($conn1, $query1);
        if (mysqli_num_rows($sql1) > 0)
        {
            while ($row1 = mysqli_fetch_array($sql1))
            {
                $result .='<tr>
                            <td>' . $row1["id"] . '</td>
                            <td>' . $row1["qty"] . '</td>
                       </tr>';
            }
        }
    }
}
else
{
    $result .='
    <tr>
    <td colspan="5">No Item Found</td>
    </tr>';
}
$result .='</table>';
echo $result;

hi i tried "LEFT JOIN" now also the same error code comes again thanks — Nisha Chandran, Jun 12 '17 at 05:50
how to give username1 permission to access databse2 ? may be it was assigned only database1 — Nisha Chandran, Jun 12 '17 at 06:02
okay thanks for your information but my problem was that both databases having different Cpanel (both are different websites) but running along the same server. so i don't know how can assign all cpanels create and access the same username and password — Nisha Chandran, Jun 12 '17 at 06:09
hi thank you so much but i really confuse that your sentence "first will fetch id from database1's table and using that id fire another query to database2's table" how can fetch the values to compare another query if u don't mine please provide anything query examples — Nisha Chandran, Jun 12 '17 at 06:33

Join together Two diffrent Database Tables

1 Answers1