Extracting Data

[eluser]leonglass[/eluser]
Ok I have a problem with extracting data from my db. Probably more of a problem with my understanding of MySql than anything else but here goes. I have a game site that I am re-writing (to use CI) and I want to extract the categories and the first four games from each category for the index page. I can't seem to get the queries right for this.

Code:

function index()
{
   $data['cats'] = $this->db->get('GA_cats');
        
   foreach($data['cats']->result() as $row)
   {
        $data['games'] += $this->db->get('GA_games', 4);
   }
        
   $this->load->view('game_view', $data);
}

Now I could just get the cats info and pass that into the view and then do a get limiting to 4 from within the view but that sort of defeats the point in having CI nicely split up the controllers and views. Is there a way to extract this data in one hit with a join clause or something like that. By the way the error that code causes is:

A PHP Error was encountered

Severity: Notice

Message: Undefined index: games

Filename: controllers/game.php

Line Number: 12

I have also tried

Code:

$data[$row->id] = $this->db->get('GA_games', 4);

which gave me problems when trying to extract the data in the view.

[eluser]leonglass[/eluser]
I can almost do what I need now and currently my query code looks like this.

Code:

function index()
{
    $data['cats'] = $this->db->get('GA_cats');
        
    foreach($data['cats']->result() as $row)
    {
        $data[$row->name] = $this->db->get('GA_games', 4);
    }
        
    $this->load->view('game_view', $data);
}

I have checked and I have result sets for each of the categories if I manually use the category name in the view

Code:

$Puzzle->result();

I can extract the data. How can I get the data out using a nested foreach and using the name field as the var name which relates to the query I need to use.

Code:

foreach($cats->result() as $row)
{

    foreach($row->name->result() as $game)
    {
            print $game->id . " " . $game->name;
    }
}

Obviously the $row->name->result part is causing me problems. So how can I get the $row->name part to be used as the variable name that points to my query results?

[eluser]leonglass[/eluser]

Code:

${$row->name}->result()

Is the answer.

[eluser]smith[/eluser]

Code:

function index()
{
    $sql = "select cat_id from GA_cats";
    $query = $this->db->query($sql);

    foreach ($query->result() as $row)
    {
        $sql2 = "select  game_id from GA_games where cat_id=".$row->cat_id." limit 4";
        $query2 = $this->db->query($sql2);
        
        foreach ($query2->result() as $row2)
        {
            $this->data[$row->cat_id][] = $row2->game_id;
        }
    }
    print "<pre>";
    print_r ($this->data);
    print "</pre>";
}

i think that will do, i don't know how your database looks...

[eluser]bcorcoran[/eluser]
Just wanted to comment here that I was looking for a solution to a similar problem and this helped out quite a bit.

Thanks!