So I have the following tables:

bans

mybb57_users

The I'm basically trying to get the mybb57_users record for offender_steamid & admin_steamid in the bans record, I have this query:

SELECT * FROM bans
LEFT JOIN mybb57_users AS uOffender ON uOffender.loginname = bans.offender_steamid
LEFT JOIN mybb57_users AS uAdmin ON uAdmin.loginname = bans.admin_steamid
ORDER BY bans.id DESC LIMIT 10

Now this works OK in phpmyadmin - however, when running through with PHP, it's creating duplicate indexes on the result - I'm using aliases here, I would've thought the results would be prefixed or something - how can I prefix the tables so I can distinguish between the two records?

CodePudding user response：

Don't use * but list the columns (you need) and alias them.

SELECT bans.id AS bans_id,
       ...
       uoffender.id AS uoffender_id,
       ...
       uadmin.id AS uadmin_id,
       ...
       FROM bans
            LEFT JOIN mybb57_users AS uoffender
                      ON uOffender.loginname = bans.offender_steamid
            LEFT JOIN mybb57_users AS uadmin
                      ON uAdmin.loginname = bans.admin_steamid
       ORDER BY bans.id DESC
       LIMIT 10;