mysql避免join_在MySQL中使用JOIN时避免模糊的列错误

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()){ ?>

echo '

This is the band';

?>

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.

解决方案

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.