Database joins and fields overlapping

[eluser]Unknown[/eluser]
When performing SQL queries fetching data from multiple tables, fields that have the same name in two different tables (overlap) will only return one (unless effort is taken to specify alternate field names using AS).

I want to switch to CI from my own small-footprint MVC, but one of the things I would miss most is how my framework would return table data in a nested array similar to:

$result[0]["table1"]["field1"] ...
$result[0]["table2"]["field2"] ...

Cake does this as well, which makes it extremely simple to perform joins and access data specific to each table.

Is there anyway to do this in CI?? Not only is it much simpler, but it also helps remove unexpected bugs because of the field overlap.

[eluser]Gaz[/eluser]
I'm having the very same problem. Does anybody have any advice on how to go about fixing this (without using AS that is)?

Cheers,
Gary.

[eluser]xwero[/eluser]
Mmmm never noticed that but i checked it and it seems like it's a bug because a sql statement returns two fields.
But i wonder how common it is to select two fields with the same name and don't use aliases?

Code:

foreach($rows as $row)
{
   echo $row['id'].' '.$row['id'];
}

You can't put the second id before the first id. As with

Code:

foreach($rows as $row)
{
   echo $row['table2id'].' '.$row['table1id'];
}

You can.

I don't think making the array deeper is a good idea because people who don't know how it works are going to be confused so adding the table as prefix of the field is the best solution but then what are you going to to if the table is aliased? Is the prefix going to be the table or the alias?

In the end i think working with aliases isn't that bad.