• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
problam with a complex JOIN query

#1
[eluser]A.M.F[/eluser]
hello guys

so this is my problam:
i have categories in one DB table, and objects in another table. what i want to do is to echo the categories and the objects that in each category.
so i did something like this:

Code:
// --- get CATS list ----->
$this->db->from('cats');
$this->db->select('objects.*, cats.*,  COUNT(objects.obj_cat_id) as total');
$this->db->join('objects', 'objects.obj_cat_id = cats.cat_id', 'left');
$this->db->groupby('cats.cat_id', 'desc');
$this->db->orderby('cat_id', 'desc');
$data['cats_list'] = $this->db->get();
// ----------------------->
Quote:SELECT objects.*, cats.*, COUNT(objects.obj_cat_id) as total FROM cats LEFT JOIN objects ON objects.obj_cat_id = cats.cat_id GROUP BY cats.cat_id ORDER BY cat_id desc

and this is how i am echoing it:

Code:
foreach ($cats_list->result() as $single)
{
    echo '<div class="gameBlock">';
    echo $single->cat_name;
    for ($i=1;$i<=$single->total;$i++)
    {
        echo "<br />" . $single->obj_name;
        }
    echo '</div>';
}

what i get is the list of the categories, and inside of each category i get the same object several times!
for example if i have the cat BOOKS and inside i have the books HARRY POTTER and the book WIZARDS, it echos it like this:
Quote:BOOKS - HARRY POTTER - HARRY POTTER
instead of doing it like this:
Quote:BOOKS - HARRY POTTER - WIZARDS

what can i do?
thank u!


Messages In This Thread
problam with a complex JOIN query - by El Forum - 10-01-2008, 01:16 AM
problam with a complex JOIN query - by El Forum - 10-01-2008, 01:22 AM
problam with a complex JOIN query - by El Forum - 10-01-2008, 01:48 AM
problam with a complex JOIN query - by El Forum - 10-01-2008, 07:25 AM
problam with a complex JOIN query - by El Forum - 10-01-2008, 07:55 AM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.