显示两个不同表格的数据

I'm having a trouble displaying data from two different tables from mysql database.

I have two tables.

(table1 ID NAME MID and the values are ID=1,2,3 NAME=name1, name2, name3 MID=1, 0, 0)

(table2 MID NAME and the values are MID=1,2,3 MNAME=mname1,mname2,mname3)

I use this query but I cant get what output I wanted

$sql="SELECT MNAME FROM table2 WHERE MID = MID IN(SELECT MID from table1)";

And here the entire query:

$sql="SELECT * FROM table1 ORDER BY ID ASC";
$result = mysqli_query($con,$sql);
if (mysqli_num_rows($result)>0) {
    $sql1="SELECT MNAME FROM table2 WHERE MID = MID IN(SELECT MID from table1)";
    $result1 = mysqli_query($con,$sql1);
    if (mysqli_num_rows($result1)>0) {
        while($row1 = mysqli_fetch_array($result1)) {
echo '<table">
<tr>
<th>SID</th>
<th>NAME</th>
<th>MNAME</th>
</tr>';
while($row = mysqli_fetch_array($result)) {
    <tr>
    <td><?php echo  $row['ID']  ?></td>
    <td><?php echo  $row['NAME']  ?></td>
    <td><?php echo  $row1['MNAME']  ?></td>
    </tr>

The above code display like this:

|ID | NAME | MNAME   
|1  |name1 | mname1  
|2  |name2 | mname1  
|3  |name3 | mname1

It should be display like this:

|ID | NAME | MNAME   
|1  |name1 |mname1  
|2  |name2 |empty  
|3  |name3 |empty

What you're attempting to do can be condensed down into a single query.

In your example, it appears as though you're aiming to loop through the results of your first query and execute another SELECT for each result.

This can be simplified with a left join. With a left join, you'll get all of the results from the left table (table1) and the matching results from the right (table2). Where there is no match, the result will be null.

SELECT t1.ID, t1.NAME, t2.MNAME
FROM table1 t1
LEFT JOIN table2 t2 ON t1.MID = t2.MID
ORDER BY t1.ID ASC

In your query should be

select * from table1 as t1 left join table2 as t2 on t1.mid= t2.mid

and on behalf on this query result set you can iterate your foreach loop to show the data table.

:)