Help needed with SQL query

[eluser]Kamy[/eluser]
Hi:

I'm working on a musician website , who has many albums and songs. Many songs appear in many albums, for example "Greatest Hits"

To manage that I have created 3 tables: albums, songs, songs_albums.

Albums has all the Album info like: title, year released, etc....
Songs has all the songs info like: title, length, lyrics...

songs_albums is where the relationship between albums and songs defined.
It has albumid, songid, displayOrder

I can successfully retrieve and display all the songs for 1 album with a statement like this:

Code:

function Albums()
    {
    $this->load->scaffolding('songs');
    $this->load->helper("url");
    $this->template->set('blogDisplay', '1');
    $this->template->set('nav', 'Discography');
    $this->db->select('*');
    $this->db->from('songs_albums');
    $this->db->join('albums', 'albums.albumid=songs_albums.albumid');
    $this->db->join('songs', 'songs.songid=songs_albums.songid');
    $this->db->where('songs_albums.albumid', $this->uri->segment(3));
    $this->db->order_by("songs_albums.displayOrder", "asc");
    $data['query2'] = $this->db->get();
    $this->template->load('template', 'albums_view',$data);
    }

Now we need a new page, where I have to display each the Album title with corresponding songs.
{from this point I'm lost}

Do I have to run a query within the query? Should I use DISTINCT?

I did tweak the code a bit to this:

Code:

function Downloads()
    {
    $this->load->scaffolding('songs');
    $this->load->helper("url");
    $this->template->set('blogDisplay', '1');
    $this->template->set('nav', 'Discography');
    
    $query = $this->db->query("SELECT * FROM albums");
    foreach($query->result_array() as $row99)
    {
        $this->db->select('*');
        $this->db->from('songs_albums');
        $this->db->join('albums', 'albums.albumid=songs_albums.albumid');
        $this->db->join('songs', 'songs.songid=songs_albums.songid');
        $this->db->where('songs_albums.albumid', $row99["albumid"]);
        $this->db->order_by("songs_albums.displayOrder", "asc");
        $data['query2'] = $this->db->get();
    
    }

$this->template->load('template', 'downloads_view',$data);
    }

But that only displays one album.....


please help.....

[eluser]Brad K Morse[/eluser]
Create two model functions, one that gets all albums, the other that gets all songs that relate to that album.

Then loop thru the albums array, and display the songs for that album, if their albumid's match

Code:

// controller
$data['albums'] = $this->your_model->getAlbums();
$data['songs'] = $this->your_model->getSongs();

// view
<?php foreach($albums as $a): ?>
    <h1>&lt;?=$a->title?&gt;</h1>
    &lt;?php foreach($songs as $s): ?&gt;
        &lt;?php if($a->albumid == $s->albumid) { ?&gt;
            <p>&lt;?=$s->title?&gt;</p>
        &lt;?php } ?&gt;
    &lt;?php endforeach; ?&gt;
&lt;?php endforeach; ?&gt;

[eluser]Kamy[/eluser]
But my problem is exactly the "...other that gets all songs that relate to that album." part !!

[eluser]Brad K Morse[/eluser]
Get all songs and albums and print the ones that have matching albumid

[eluser]Kamy[/eluser]
Maybe I didn't explain it properly, my problem is in the controller, to write the function that retrieves each album's songs.
I know how to write a func to retrieve all albums, or all songs, or all songs to each album. But I don't know how to combine these in one function !

It seems I have to write 2 queries, one for albums and one with Joins to retrieve songs.
I think running 2 queries within eachother like this is wrong and there's got to be a better solution.

Still not closer to solving my issue :-S

[eluser]Brad K Morse[/eluser]
In your controller, you would call two model functions, as seen below, then you'll pass that data array within your view

Code:

// controller
function albums() {
$this->load->model('your_model');

$data['albums'] = $this->your_model->getAlbums();
$data['songs'] = $this->your_model->getSongs();

$this->load->view('album-song-list', $data);
}

// view
<?php foreach($albums as $a): ?>
    <h1>&lt;?=$a->title?&gt;</h1>
    &lt;?php foreach($songs as $s): ?&gt;
        &lt;?php if($a->albumid == $s->albumid) { ?&gt;
            <p>&lt;?=$s->title?&gt;</p>
        &lt;?php } ?&gt;
    &lt;?php endforeach; ?&gt;
&lt;?php endforeach; ?&gt;

You would have two queries, that being stored within your model, in this example, they are called getAlbums and getSongs

Hope this makes more sense.

[eluser]Kamy[/eluser]
Thanks bkmorse !

Solved it with your suggestions

The only thing is I don't use Models (I know I should) and run my queries within the controller.

[eluser]Brad K Morse[/eluser]
Let me know if you want help placing them within a model.

Fat models, skinny controllers.

Makes for easier to read code

[eluser]Kamy[/eluser]
I definitely wanna learn how to do that. I should do more reading on it too.

Next for me is to turn this project into a bilingual website, so that's going to be another new challenge.

Do you have any experience with CI language class?

As an example: The same queries would run on English (en) to retrieve english songs of the same singer and fa would grab all Persian songs.

[eluser]Brad K Morse[/eluser]
Unfortunately I do not have experience with the language class.

Post your question as a separate thread and someone will respond with an answer.