在MySQL中使用JOIN时避免模棱两可的列错误 [英] Avoiding ambiguous column errors when using JOIN in MySQL

问题描述

我的查询如下:

$sql = "
SELECT u.*, s.*
FROM bands u
inner join statuses s on u.status_id = s.id
WHERE u.status_id = 1
ORDER BY u.band_name";

我想像这样从bands表中输出ID:

And I would like to output the ID from the bands table like so:

<?php while($row = $result->fetch_assoc()){ ?>
            <tr>
              <?php
                    echo '<td id="' . $row['id'] . '">This is the band</td>';
              ?>
            </tr>
<?php }

但是$row['id']没有输出任何内容，由于某种模棱两可的列问题，我猜测是这样，但是我不确定如何正确地查询它.我也尝试过$row['u.id']，但我认为这不是正确的别名用法，因为它也没有输出任何内容.

but $row['id'] is not outputting anything, I'm guessing due to some sort of ambiguous column issue but I'm not sure how to properly query it. I tried $row['u.id'] as well but I don't think that is the proper alias use because it didn't output anything either.

推荐答案

您将要为id列创建不冲突的别名；

You'll want to create non conflicting aliases for the id columns;

SELECT u.*, s.*, u.id AS uid, s.id AS sid
FROM bands u
inner join statuses s on u.status_id = s.id
WHERE u.status_id = 1
ORDER BY u.band_name

然后，您可以将它们分别选为$row['uid']和$row['sid']，并仍照常访问其他列.冲突的id列也仍然存在，只是避免使用它.

Then you can pick them out as $row['uid'] and $row['sid'] and still access your other columns as usual. The conflicting id column is also still there, just avoid using it.

这篇关于在MySQL中使用JOIN时避免模棱两可的列错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！