How to get results from a joined query

[eluser]SteveBluck[/eluser]
Hello everyone.

Please take a look at my query:

Code:

$query = $this->db->query("SELECT a.users_id, a.users_username, GROUP_CONCAT(b.game_id SEPARATOR ' ' )
            FROM users AS a
            JOIN usergames AS b ON a.users_id = b.user_id
            GROUP BY users_id");

Please take a look at my view:

Code:

<?php
      if($query->num_rows() > 0):
      foreach ($query->result() as $row):
      ?>    
      <tr class="row_bg">
        <td class="row_bg center avatar" >&lt;?php echo $row->users_id; ?&gt;</td>
        <td class="row_bg left" ><a href="#">&lt;?php echo $row->users_username; ?&gt;</a></td>
        <td class="row_bg center" >&lt;?php echo $row->game_id; ?&gt;</td>
      </tr>
      &lt;?php
    endforeach;
    endif;
    ?&gt;

Now all works fine apart from the game_id field. I can't seem to get it to show. I think its becasue it comes from the second table I have joined?

Any help to point me in the right direction would be greatly appreciated.

[eluser]vitoco[/eluser]
hi , i don't know exactly how "GROUP_CONCAT" works , but you can explicitly set the field name with 'AS' in this way

Code:

SELECT    
    a.users_id,
    a.users_username,
    GROUP_CONCAT(b.game_id SEPARATOR ' ' ) AS game_id
FROM
    users AS a
JOIN
    usergames AS b
ON
    ( a.users_id = b.user_id )
GROUP
    BY users_id

see if it works , if not..post the table script so i'll be able to run some tests.

Bye

[eluser]Mike Ryan[/eluser]
-------

Edit: Damn, someone beat me to it :-)

-------

Hi Steve,

Looks like using GROUP_CONCAT is causing the field to be renamed.

Try changing your SQL statement to:

Code:

... a.users_username, GROUP_CONCAT(b.game_id SEPARATOR ' ' ) as game_id FROM...

You could do print_r($row) during your loop to make sure the game_id value exists. This will print out the entire array structure - very useful.

[eluser]jedd[/eluser]
Hi Steve, and welcome to the CI forums.

Yeah - you definitely need to post your SCHEMA here. It's confusing to see users_id and user_id in proximity, not quite knowing what the origins and interconnections are.

You can use the CLI for MySQL and explore with the 'EXPLAIN' prefix on your SELECT - that can sometimes be instructive.

[eluser]jedd[/eluser]
Oh, and doing $query->result stuff in your view is a bit of a no-no. I didn't actually think you'd be able to do that there, but in any case you should use the Model to extract data from the DB, and feed it to your controller, which should do any massaging required, and the View should be treated as just a rendering engine.

[eluser]vitoco[/eluser]
[quote author="jedd" date="1238790775"]Hi Steve, and welcome to the CI forums.

Yeah - you definitely need to post your SCHEMA here. It's confusing to see users_id and user_id in proximity, not quite knowing what the origins and interconnections are.

You can use the CLI for MySQL and explore with the 'EXPLAIN' prefix on your SELECT - that can sometimes be instructive.[/quote]
script = SCHEMA , didn't know if it was correct

[eluser]vitoco[/eluser]
[quote author="Mike Ryan" date="1238790728"]-------

Edit: Damn, someone beat me to it :-)

-------

Hi Steve,

Looks like using GROUP_CONCAT is causing the field to be renamed.

Try changing your SQL statement to:

Code:

... a.users_username, GROUP_CONCAT(b.game_id SEPARATOR ' ' ) as game_id FROM...

You could do print_r($row) during your loop to make sure the game_id value exists. This will print out the entire array structure - very useful.[/quote]

4 minutes baby !!! ...jajjajaja , but's ok , you response it's maybe more clear .

[eluser]SteveBluck[/eluser]
Holy Moly 5 replys in like 8 minutes. Cool.

Thank you guys, it was the GROUP_CONCAT renaming my field, once I put the "AS game_id" in, it all worked fine.

Is there any information I can look at, at how to extract information from the database in the Model and pass it to the controller. I'm really new to MVC.

Thank you very much for your quick response!

[eluser]jedd[/eluser]
Yeah, I think we're all vying for Forum Responder Of The Month Award.

Fuzzy has a [url="http://ellislab.com/forums/viewthread/107773/"]thread dedicated to CI resources[/url] that may be of interest.

You should also be sure to read through the [url="http://ellislab.com/codeigniter/user-guide/toc.html"]the CI User Manual[/url], especially the first two columns of stuff there, and in particular here the Controller, View and Model pages.

The video tutorials are also very good at explaining ways of shifting data between view, model and controller code.

[eluser]SteveBluck[/eluser]
Thanks Jedd, I have read every single part of the user manual. I shall check out the videos as you suggested though.

Cheers!