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)?

Abdulla Nilam · Accepted Answer · 2021-12-08 08:09:32Z

5

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 ....

edited Dec 8, 2021 at 8:09

Abdulla Nilam

39.7k18 gold badges85 silver badges115 bronze badges

answered Oct 9, 2008 at 13:09

pilsetnieks

10.4k12 gold badges51 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

meleyal Over a year ago

using postgres, is it still the same?

pilsetnieks Over a year ago

I see no reason why it shouldn't be, "AS" is standard SQL syntax, and I'd wager PHP's pg_* functions work very much like mysql_* ones.

Abdulla Nilam · Accepted Answer · 2021-12-08 08:09:56Z

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

edited Dec 8, 2021 at 8:09

Abdulla Nilam

39.7k18 gold badges85 silver badges115 bronze badges

answered Oct 9, 2008 at 13:57

Pittsburgh DBA

6,8042 gold badges44 silver badges71 bronze badges

1 Comment

Luís Almeida Over a year ago

in my case I have a lot of queries without the AS and it would be very painful renaming all. is there any way to do this ?

Abdulla Nilam · Accepted Answer · 2021-12-08 08:10:14Z

1

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

edited Dec 8, 2021 at 8:10

Abdulla Nilam

39.7k18 gold badges85 silver badges115 bronze badges

answered Nov 14, 2008 at 11:50

Thejuli

Collectives™ on Stack Overflow

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

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related