MySQL 3 Table join question...

[eluser]f244[/eluser]
Hi everyone,

I'm a newbie to CodeIgniter, really enjoying working with it so far. I have a question around MySQL joins across multiple tables. I've googled it, checked the user guide and searched the forum but haven't found another situation similar enough to solve my problem.

I'm working on a site for a record shop. Each item in the shop's catalogue has a single format (eg. CD album, 7" single etc.) but can potentially have more than one artist associated with it.

I have 3 tables:

catalogue (id, title, artist, artist2, artist3, artist4, etc...)
artists (id, name, etc...)
formats (id, format, etc...)

I have this query:

Code:

function getCatalogueItem($id)
    {    
    $data = array();
    $this->db->select('catalogue.id, catalogue.title, catalogue.artist, artists.id, artists.name, formats.format');
    $this->db->from('catalogue', 'artists', 'formats');            
    $this->db->join('artists', 'catalogue.artist = artists.id', 'left');        
    $this->db->join('formats', 'catalogue.format = formats.id', 'left');    
    $this->db->where('catalogue.id =', $id);        
    $Q = $this->db->get();    
    if ($Q->num_rows() > 0){
        foreach ($Q->result_array() as $row){
            $data[] = $row;
        }
    }
    $Q->free_result();
    return $data;
    }

This works perfectly for catalogue items with a single artist.

I'm trying to understand how to modify this query if there is an artist2, artist3 or artist4 for a catalogue item.

I'd like to return an artist name and id for each artist associated with a catalogue item.

Any thoughts or suggestions would be very much appreciated!

Steve.

[eluser]port22[/eluser]
Best solution is to change your database design to something similar to the following, making it relational:

catalogue - ( id, title, etc )

artists - ( id, name, etc )

formats - ( id, format, etc ) ( Be wary with this table as it may actually contain data that fits nicely in catalogue )

artists_cds - ( id, catid, artid ) ( Where catid = foreign key to catalogue id and artid to artists id )

[eluser]f244[/eluser]
Hi,

Many thanks for your reply, that's a perspective I hadn't considered - too bogged down in thinking about the query to take a step back.

artists_cds - ( id, catid, artid ) ( Where catid = foreign key to catalogue id and artid to artists id )

If I understand you correctly, the table 'artist_cds' would just have the ids of catalogue items and the relevant artist ids? eg. To use the example of catalogue item 1 having two artists and artist 1 having two catalogue items...

artists_cds

id catid artid
1 1 1
2 1 2
3 2 3
4 3 1

Am I understanding this right? If so how does that affect my query above where I'm trying to retrieve all the details for a catalogue item from the database (including multiple artists). I'm starting to get a little confused about my original MySQL query and all the joins now...!

Thanks!

[eluser]port22[/eluser]
It changes the query, but just use the join table (artists_cds) as the starting point.

So, you would do a join much like this:

select * from
artists_cds join artists
join catalogue
join format

Which will produce a detailed table of everything. If you want a specific artist, catalogue or format, you can just throw in where clauses as necessary.

[eluser]f244[/eluser]
[quote author="port22" date="1267800466"]It changes the query, but just use the join table (artists_cds) as the starting point.

So, you would do a join much like this:

select * from
artists_cds join artists
join catalogue
join format

Which will produce a detailed table of everything. If you want a specific artist, catalogue or format, you can just throw in where clauses as necessary.[/quote]

Many thanks for the info and suggestions. I've re-designed my DB as you recommended and after some experimentation I'm starting to see the advantages of this approach. I have one further question though, one thing I can't quite figure out.

Let's say I want to get the details for a single item in the catalogue (in this case item 5 which has 3 artists associated with it), I could use a query like:

Code:

select catalogue.title,artists.name from
artists_cds
join artists on artists.id=artists_cds.artid
join catalogue on catalogue.id=artists_cds.catid
where catalogue.id=5

I get a result back with 3 rows, one for each artist:

Row 1 -> Title, Artist(1)
Row 2 -> Title, Artist(2)
Row 3 -> Title, Artist(3)

Is there a way to modify the query so I can have the results as a single row with all the artist names either in separate fields or in an array or something like that?

I have a similar problem with the overall catalogue, I can work out how to get a list of everything but I've always got this problem of multiple rows for a catalogue item where there are multiple artists associated with that item.

Thanks for any help or tips!

[eluser]port22[/eluser]
What you're looking for is probably possible with a pure SQL query, but I think it's going to require some amount of joins.

To return the result as an array you can then manipulate with PHP for the result you want, you can use result_array(). Check this link for more info: http://ellislab.com/codeigniter/user-gui...ecord.html .

[eluser]jedd[/eluser]
[quote author="f244" date="1267872872"]
I get a result back with 3 rows, one for each artist:

Row 1 -> Title, Artist(1)
Row 2 -> Title, Artist(2)
Row 3 -> Title, Artist(3)

Is there a way to modify the query so I can have the results as a single row with all the artist names either in separate fields or in an array or something like that?
[/quote]

You could, but you probably don't want to. I suspect you have a view problem here, not a SQL one. That is, you have a certain way of handling data in your view, and you just don't yet know the best way to handle this (new) format of the arrays.

Have a read of this thread: [url="http://ellislab.com/forums/viewthread/125879"]http://ellislab.com/forums/viewthread/125879[/url]