Need some database help

[eluser]Firestorm ZERO[/eluser]
I'm a bit rusty on mysql. So need some help with the following example...

Book
id
name
publisher - id from publisher table
author - id from author table

Publisher
id
name

Author
id
name

To list all books I would do a join right? So in my Model I have the following...

Code:

$this->db->select('*');
$this->db->from('book');
$this->db->join('publisher', 'book.publisher = publisher.id');
$this->db->join('author', 'book.author = author.id');
$this->db->order_by('title');

Which I believe is right (not sure). Now to print it, in my view I have...

Code:

<?php foreach ($query->result() as $row): ?>
<tr>
<td>&lt;?= $row->title ?&gt;</td>
<td>&lt;?= $row->name ?&gt;</td>
<td>&lt;?= $row->publisher ?&gt;</td>
<td>&lt;?= $row->author ?&gt;</td>
</tr>
&lt;?php endforeach; ?&gt;

Now the problem is how do I print the Publisher and Author names? Since the column name is the same for all 3 tables?

I'm using CI v1.5.4.

[eluser]tonanbarbarian[/eluser]
you will need to 'alias' the fields in the joined tables, so you cannot use a select *
instead try

Code:

$this->db->select('book.*, publisher.name AS publisher, author.name AS author');

[eluser]wiredesignz[/eluser]
Book.name should be Book.title really

[eluser]sandwormusmc[/eluser]
[quote author="Firestorm ZERO" date="1201762499"]
Now the problem is how do I print the Publisher and Author names? Since the column name is the same for all 3 tables?

I'm using CI v1.5.4.[/quote]

Standardize your column names to table_element and that should fix your problems. Keeps things simple for larger databases, also.

Example:

Book
---------
book_id
book_name (book_title is more appropriate here, as wiredesignz suggested)
publisher_id - id from publisher table
author_id - id from author table

Publisher
---------
publisher_id
publisher_name

Author
---------
author_id
author_name
.

[eluser]Lone[/eluser]
Oh my god, that is awesome to see we're not the only one's doing that sandwormusmc. We ensure that all field names in our DB are unique as well in that format - works a treat for these situations and you never have to reference with a table.field.

[eluser]BizComputing[/eluser]
I think table_field naming for fields is a matter of taste. Years ago I used that format, but got tired of the longer names and the apparent repetition of my_table.my_table_my_field. Our team does not prefix fields with the table name unless it's a field that is a foreign key in which case, the prefix is not the table it's in, but the table that is referenced.

[eluser]Firestorm ZERO[/eluser]
Thanks for all the replies. Forgot about aliases.

As for me, I prefer not to use "table_field" naming for the same reasoning as bizcomputing pointed out.

[eluser]sandwormusmc[/eluser]
[quote author="Lone" date="1201841285"]Oh my god, that is awesome to see we're not the only one's doing that sandwormusmc. We ensure that all field names in our DB are unique as well in that format - works a treat for these situations and you never have to reference with a table.field.[/quote]

Yeah, it's just the way I've gotten used to doing it, both academically and in the real world. After undergraduate and graduate database courses, both professors of those courses recommended doing it that way, so it seems to make sense as a standard. To me using aliases complicates generating the code programmatically ... but to each his own.