[SOLVED]newbie question: Help on "Join" and "For Each" to show results

[eluser]Juan Velandia[/eluser]
Hello, as i'm trying to go further in PHP and CI programming I will need your help again (and again...)

I want to show a list with all the sections and articles related to those sections, something like this:

Solutions:
- web design
- Brochures
- Flyers

Clients:
- CocaCola
- Pepsi
- SevenUp

But I cant seem to find the right way to do it. this is what I get:

Solutions: - web design
Solutions: - Brochures
Solutions: - Flyers

Clients: - CocaCola
Clients: - Pepsi
Clients: - SevenUp

Here's the Code

The model

Code:

function get_section_articles()
    {
        $this->db->select('cms_article.name AS article_name');
        $this->db->select('cms_section.name AS section_name');
        $this->db->from('cms_article');
        $this->db->join('cms_section','cms_article.id_section = cms_section.id_section');
        $this->db->order_by('section_name');
        $query = $this->db->get();
        return $query;
    }

The controller

Code:

$data['section_articles'] = $this->section_content_model->get_section_articles();

the view

Code:

foreach ($section_articles->result_array() as $row):
      echo $row['section_name'];
      echo $row['article_name'];
      ?&gt;<br>&lt;?    
      endforeach; ?&gt;

Any Ideas for a graphic designer? thanks a lot

[eluser]smilie[/eluser]

Code:

Without testing, I guess you will have to use

Model:
function get_section_articles()
    {
        $this->db->select('cms_article.name AS article_name');
        $this->db->select('cms_section.name AS section_name');
        $this->db->from('cms_article');
        $this->db->join('cms_section','cms_article.id_section = cms_section.id_section');
        $this->db->group_by('section_name');
        $query = $this->db->get();
        return $query;
    }

Tho' I would suggest to change DB structure and to place sections in one table, articles in another and to link them with i.e. article_section_id (which would reffer to section_id).

Cheers,
Smilie

[eluser]Juan Velandia[/eluser]
Hello Smilie

Thanks for you quick answer, I've add these two lines in the model as you suggested

Code:

$this->db->group_by('section_name');
$this->db->group_by('article_name');

Regarding your sugestion the DB has already the two tables that you recommend: "section" and "article" and both have a "id_section" field wich connects them.

I think the model could be ok, but I'm still struggling with the view. Do yoy have any further suggestions? thanks a lot and best regards!

[eluser]Juan Velandia[/eluser]
Well, it seems that It was already resolved: http://ellislab.com/forums/viewthread/158001/

Regards!

[eluser]Juan Velandia[/eluser]
After some hours dealing with this and with some previous threads I came up with he solution

Model

Code:

function get_section_articles()
    {
        $this->db->select('cms_article.name AS article_name');
        $this->db->select('cms_section.name AS section_name');
        $this->db->select('cms_section.url_section');
        $this->db->from('cms_article');
        $this->db->join('cms_section','cms_article.url_section = cms_section.url_section', 'left');
        $this->db->group_by('section_name');
        $this->db->group_by('article_name');
        $this->db->group_by('cms_section.url_section');
        $this->db->order_by('section_name');
        $query = $this->db->get();
        
        $previous_section = null;
            foreach($query->result() as $row) {
            
                if ($row->url_section != $previous_section)
                {
                    if ($previous_section != null)
                    {
                        $results[] = $section;
                    }
                    $section = array();
                    $section['url_section'] = $row->url_section;
                    $section['section_name'] = $row->section_name;
                    $section['articles'] = array();
                    $previous_section = $row->url_section;
                }
                $article = array();
                $article['article_name'] = $row->article_name;
                $section['articles'][] = $article;
            }
            if ($previous_section != 0)
            {
                $results[] = $section;
            }
        return $results;

    }

controller

Code:

$data['section_articles'] = $this->section_content_model->get_section_articles();

view:

Code:

foreach ($section_articles as $section): ?>

    <p><strong>&lt;?php echo $section['section_name'] ?&gt;</strong></p>
        &lt;?php foreach ($section['articles'] as $article): ?&gt;
        <p>&lt;?php echo $article['article_name'] ?&gt;</p>
    &lt;?php endforeach; ?&gt;
    
&lt;?php endforeach; ?&gt;

[eluser]Juan Velandia[/eluser]
[SOLVED]