Ant100 Ant100 - 1 month ago 11
MySQL Question

How to loop through two different tables?

I've been thinking about this for hours now and can't find a solution. I googled and searched all over stackoverflow for a duplicate question and couldnt find one. (Since I'm not a native english speaker this could be the reason, please if this is duplicate mark it as such and kindly point me to the question).

I got two tables. One is 'section table', and the other is 'news table'. Section table has 'section name' and 'section id'.

News table has 'section name', 'content description', 'content title', 'content id'. I want my page to look something like this:

Section 1


  • Title 1, Description 1

  • Title 2, Description 2



Section 2


  • Title 1, Descrition 1

  • Title 2, Descrition 2

  • Title 3, Descrition 3



Each section should get the last 5 news from database. I know this should be done with a loop, I've been using foreach but cant seem to make it work.

foreach($sections as $section)
{
echo $section->title;

foreach($news as $new)
{
echo $new->title;
echo $new->description;
}
}


But that just loops through all the news. I get this:

Section 1:


  • title 1, description 1, title 2, descrition 2 (and then title and description from other sections as well).



Another solution I thought of was joining the tables, but what i got was:

section 1


  • title 1, description 1



section 1


  • title 2, description 2



etc. Not what I'm looking for.

Please help, I've been stuck at this all day long :(

[edit]
This is a simplified version of my controller:

$sections = $this->mymodel->getSections();
$id = $sections->id;
$news = $this->mymodel->getNews($id);


And this is the model:

function getNews($id){
$rows = $this->db->select('title, description, sectionid, id')
->where('sectionid', $id)
->limit(5)
->order_by('id', 'desc')
->get('news')->result();
return $rows;
}

function getSections(){
$rows = $this->db->select('title, description, id')->get('section')->result();
return $rows;
}

Answer

Essentially you need to check and make sure the current news element ($new) has the same section as the current sections element ($section) you are displaying. Without knowing your exact keys I can't give a more exact answer, but try something like:

foreach($sections as $section)
{
    echo $section->title;

    foreach($news as $new)
    {
        if ($section->name == $new->section_name)
        {
            echo $new->title; 
            echo $new->description;
            echo "<br/>";
        }
    }
}