• 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!

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

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

#4
[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

#5
[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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


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