duanniwu7730
2019-08-06 04:52
浏览 167
已采纳

显示两个不同表格的数据

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
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • doudao1369 2019-08-06 05:07
    已采纳

    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
    
    点赞 打赏 评论
  • douchushao7799 2019-08-06 05:22

    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.

    :)

    点赞 打赏 评论

相关推荐 更多相似问题