prefixing column names in return from SQL JOIN

[eluser]m4rw3r[/eluser]
I am wondering if it is possible to have the database to prefix the columns in a join with the tablename from where the columns originate.

For example I have two tables:

Code:

Posts
id   author   text   threads_id
1    m4rw3r   Hello  1

Threads
id   name      author
1    A thread  foo

And if I call this JOIN query:

Code:

SELECT * FROM threads LEFT OUTER JOIN posts ON threads.id = posts.threads_id

The columns will overwrite each other in the result (the author will be m4rw3r, not foo (depending on return order)).

Is there any way I can distinguish between the results from the different tables, so I know from which table the result came from?

[eluser]_asdf[/eluser]
alias the fields using AS? This isn't CI specific, but a function of SQL. The last (right-most) column takes precedence in a resultset with the same names.

Code:

select t.id, t.name as thread_name, t.author as original_author from `threads` as t [...] etc

[eluser]wiredesignz[/eluser]
A quick test I made showed you can specify the table in the select. ie: SELECT threads.* FROM threads

I'm not sure if it will help with a join.

[eluser]champs[/eluser]
Use aliases. Please. Alias every table reference and join the tables by their aliases. I'm not going to rehash every time it comes up, but as a pet issue of mine, you'll probably find the explanation and examples by sifting through one or two pages of my comment history.

[eluser]m4rw3r[/eluser]
The result of

Code:

SELECT threads.* FROM threads LEFT OUTER JOIN posts ON threads.id = posts.threads_id

is the same as

Code:

SELECT * FROM threads

so that eliminates the benefits of a JOIN quite brutally .

This with aliases is a possibility I have considered, but then I have to use SHOW COLUMNS FROM ... 2x and then make some PHP processing assembling the query.

I need this (maybe not really, but it would make some performance improvements by limiting the number of queries) for my IgnitedRecord model (thanks to nmweb for the name :-) ).
The reason I need it is that I need to split the result into two objects, one for the requested object, the other for the related one.

But maybe the performance improvement is not necessary, I don't know.

[eluser]wiredesignz[/eluser]
I need to use MySQL more seriously, I never have these problems.