加入两个不同的数据库表[重复]

Question

This question already has an answer here:

• mysql_fetch_array()/mysql_fetch_assoc()/mysql_fetch_row()/mysql_num_rows etc… expects parameter 1 to be resource or result 32 answers

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...

</div>

Answer 1

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;