SQL left join automatic aliasing?

Question

I just realized that I'm going to have to start aliasing my database calls due to repeating column names in my join tables. Is there a way to automatically tell SQL to alias all my column names so that they are returned with a prefix of the table name? Otherwise it appears to be quite confusing when only some of them are aliased. Just trying to be consistent without writing tons of extra code.

$sql = "SELECT contracts.po_number, contracts.start_date, contracts.end_date, contracts.description, contracts.taa_required, contracts.account_overdue, jobs.id AS jobs_id, jobs.job_number, companies.id AS companies_id, companies.name AS companies_name
    FROM contracts
    LEFT JOIN jobs ON contracts.job_id = jobs.id
    LEFT JOIN companies ON contracts.company_id = companies.id
    WHERE contracts.id = '$id'
    ORDER BY contracts.end_date";

Answer 1

No, but you can make life a little easier by using table aliases:

SELECT c.po_number, c.start_date, c.end_date, c.description, 
    c.taa_required, c.account_overdue, j.id AS jobs_id, j.job_number, 
    cm.id AS companies_id, cm.name AS companies_name 
FROM contracts c
LEFT JOIN jobs j ON c.job_id = j.id 
LEFT JOIN companies cm ON c.company_id = cm.id 
WHERE c.id = '$id' 
ORDER BY c.end_date

Answer 2

you can use alias tables in your sql statements so you have to write less, but to actually access the columns from php there's no way around aliasing all of them, if you want to access them by name.

you can also access columns with indexes from php, but that's a maintenance nightmare

Answer 3

I would recommend to always alias table names. It makes it very hard to read later, if you skip alias.