Avoiding ambiguous column errors when using JOIN i

问题:

My query looks like this:

$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";

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 }

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.

回答1:

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

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.

回答2:

add an alias on the same column names. ex,

SELECT u.id UID,....

then you can now retrieve using its alias (eg. UID)