Display data from SQL in view using one-to-many relations

[eluser]Unknown[/eluser]
Good evening all,

I have a very weird question... I was wondering how you handle ONE to MANY relations using codeigniter ? Lets say I have a table called "news" and a table called "news-tags" (where news-tags contains random tags connected to a newspost) and i would like to show both the news content and its tag inside the same div...

Getting a list of all news records works fine, but i cant find the way to get my related records from the "news-tag" db

Since i'm developing in .NET mostly, i was thinking about using objects like an object news, with a property of type "array of news-tag"... But my guess is this is done different in php + ci ?

Basicly what I would do is: Get a list of all news items, for each returned items get a list of all news-tags records and store them in a "property" named "newsTags"... But i need to find a way to return this to my view...

What i need is something so i can access them using two foreach in the view:

- for each to display each news item
- inside this foreach we use another foreach to display all the news tag.

That last part is the part I can not get working. I would like to be able to do something like this:

Code:

{foreach item=newsitem in $newsitems}
{foreach item=tag in $newsitem->newsTags}
{/foreach}
{/foreach}

But the problem is, there is no "newsTags" collection property...


Hope my problem is understandable


Edit:

I've had some chat on the IRC about this issue and have tried seveal things..
On of this was using a join... But this resulted in what i expected:

Because news-tags can contain multiple records with the same newsId (one to many), this join will return the same record multiple times:

Example: My news1 has 5 tags( this means 5 related records in news-tag) and my news2 has 3 tags (this means 3 related records in news-tag).

If i do this query: select * from news n, news-tag nt WHERE nt.newsId=n.id

this will return 8 records, 5 times the same "news1" data with 5 times a different "news-tag" for this news... and 3 times the same "news2" data with 3 different "news-tag" for this news...

Now i would like to show boths news (news1 and news2) data only once in my view, but i also want to show the 5 tags below news1 and the 3 tags below news2...

Any idea how to get this working?

[eluser]Unknown[/eluser]
Alright guys, my girlfriend explained me how she would do this:

The Model:

Code:

public static function getMenu(){
        $db = CodePDO::getInstance();
        $sql = 'SELECT *
                FROM china_menu';

        $stmt = $db->prepare($sql);

        if($stmt->execute()!== false){
            $menu = array();

            while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
                $menu[] = $row;
            }
            return $menu;
        }else{
           echo("$stmt->execute() = false");
        }

    }

    public static function getCategorie(){
        $db = CodePDO::getInstance();
        $sql = 'SELECT *
                FROM china_categorie';

        $stmt = $db->prepare($sql);

        if($stmt->execute()!== false){
            $categorie = array();

            while($row = $stmt->fetch(PDO::FETCH_ASSOC)){
                 $categorie [] = $row;
            }
            return  $categorie ;
        }else{
           echo("$stmt->execute() = false");
        }
    }

The controller

Code:

$menu = DAOMenu::getMenu();
    $categorie = DAOMenu::getCategorie();
    //print_r($menu);

    $smarty->assign('menu', $menu);
    $smarty->assign('categorie',$categorie);
    $smarty->display('index.html');

The View:

Code:

<div class="Menu">
    <ul>
        {foreach $categorie as $categorieItem}
        <li>{$categorieItem.categorie}</li>
            <ul>
                {foreach $menu as $menuItem}
                    {if $categorieItem.id == $menuItem.id_categorie}
                        <li>{$menuItem.gerecht}</li>
                    {/if}

                {/foreach}
            </ul>
            </li>
        {/foreach}
    </ul>
</div>

Now she showed me this example using menuitems and categories. each category has multiple menu's...

So She uses two queries: one to get all categories and one to get all menuitems... Isnt this possible using one query?

The output is exact what I want.