problam with a complex JOIN query

[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();
// ----------------------->

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:

BOOKS - HARRY POTTER - HARRY POTTER

instead of doing it like this:

BOOKS - HARRY POTTER - WIZARDS

what can i do?
thank u!

[eluser]Jon L[/eluser]
Can you export & display each table structure plus a couple of rows per table?

Additionally, have you tried testing your query directly with phpMyAdmin to see if it returns how you expect?

[eluser]A.M.F[/eluser]
[quote author="Jon L" date="1222863738"]Can you export & display each table structure plus a couple of rows per table?

Additionally, have you tried testing your query directly with phpMyAdmin to see if it returns how you expect?[/quote]

this is my cats table:

- cat_id
- cat_name

and this is my objects table:

- obj_id
- obj_name
- obj_cat_id
- obj_cat_name

and i haven't tried to test the query in PMA, how can i do it?

[eluser]Unknown[/eluser]
did you miss the GROUP BY part off for your count

edit - bah didnt spot that

also should you not be counting the obj_id rather then the obj_cat_id

[eluser]Jon L[/eluser]
To test the query, just copy the query and paste into the SQL or Query tabs in phpmyadmin (I forget which tab it is).

Or get a copy of SQLYog Community Edition, connect remotely to your database, and execute the query that way.

Then you can tweak the query to improve the results you're receiving