How to output data from tables with same column names in CodeIgniter?

Question

This is my query:

$query = $this->db->query('
    SELECT archives.id, archives.signature, type_of_source.description, media_type.description, origin.description 
    FROM archives, type_of_source, media_type, origin                                                             
    WHERE archives.type_of_source_id = type_of_source.id                                                          
    AND type_of_source.media_type_id = media_type.id                                                              
    AND archives.origin_id = origin.id                                                                            
    ORDER BY archives.id ASC
');

But how to output the result? This works, but only gets the last description (origin.description):

foreach ($query->result_array() as $row)
{
    echo $row['description'];
}

This doesn't work:

foreach ($query->result_array() as $row)
{
    echo $row['type_of_source.description'];
}

Or should I rename the columns (e.g. type_of_source_description)?

Answer 1

This actually has very little to do with CodeIgniter and a lot with how mysql_fetch_assoc provides the query results. The solution is that you should rename the columns inside the query using "AS", e.g.

select type_of_source.description as type_of_source_description, origin.descripotion as origin_descripotion from ....

Answer 2

Just alias the columns in the SELECT statement. Do not modify your database. The standard practice is to use aliasing in your SQL statements.

From PostgreSQL's docs for "SELECT":

"Using the clause AS output_name, another name can be specified for an output column."

PostgreSQL SELECT

Answer 3

I recommend not to use that way, instead you can rename output columns with "AS".

Answer 4

select type_of_source.description as type_of_source_description, origin.descripotion as origin_descripotion from ....

It still didn't work, i've tried it looks like this:

SELECT a.id as mid, a.nama as mnama, a.kode as mkode, b.kode as mkodebd, b.nama as mnamabd FROM datamodel_ms_subbidangdaerah AS a,datamodel_ms_bidangdaerah as b WHERE....

I try to retrieve looks like this:

foreach ($query->result() as $row)
{
     hasil.= '$row->mid'.'<br />';
}

But CodeIgniter shows error : Message: Undefined property: stdClass::$mid Please give me a best solution without renaming column name.

Thanks