显示两个不同表格的数据

Question

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

Answer 1

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