problam with a complex JOIN query

problam with a complex JOIN query - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: problam with a complex JOIN query (/showthread.php?tid=11982)

problam with a complex JOIN query - El Forum - 10-01-2008

[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 -----&gt;

$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(); 

// -----------------------&gt;

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!

problam with a complex JOIN query - El Forum - 10-01-2008

[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?

problam with a complex JOIN query - El Forum - 10-01-2008

[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:

Quote:- cat_id
- cat_name

and this is my objects table:

Quote:- 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?

problam with a complex JOIN query - El Forum - 10-01-2008

[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

problam with a complex JOIN query - El Forum - 10-01-2008

[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