Logical Problem With category and sub-category

[eluser]woomacoder[/eluser]
Hello Everybody,

I have a problem (logical problem) with Managed my category and subcategory database; the code is it:

Code:

public function GetCats($UriString)
      {
          $DataSet = self::GetCatsRel($UriString);

          foreach($DataSet->result() as $Row)
          {
               $sql = "SELECT
                              c.id as cat_id, c.sub_cats as sub_cats,
                              c.name as cat_name, c.end as cat_end,
                              c.warning as cat_warning, c.class as cat_class,
                              c.completed as cat_completed
                         FROM cats as c
                         WHERE c.id = ".$Row->cat_id."
                         AND c.macro_id = ".$Row->macro_id." ;";

                $DataSet = $this->db->query($sql);

                foreach($DataSet->result() as $Row)
                {
                     if($Row->sub_cats != 0)
                     {
                          echo "INCAZUS";
                          break;
                     }
                }
          }
          return $DataSet;
      }

      public function GetCatsRel($UriString)
      {
           // query String
           $sql = "SELECT
                    r.id, r.macro_id as macro_id, r.cat_id as cat_id, r.subcat_id
                         FROM relcats as r
                    WHERE macro_id = ".$this->db->escape($UriString);
          
           // eseguo la queyr
           return $this->db->query($sql);
      }

INCAZUS is my personal word to identified a right status, where code works!
I have two table:

CATS table, with id, sub_cat (counter of subcategory), name and some values.
SUBCATS table, with id, parent cat id (for related subcategory with father's category) and some values.

But... php print_r reports:

Code:

CI_DB_mysql_result Object ( [conn_id] => Resource id #28 [result_id] => Resource id #48 [result_array] => Array ( ) [result_object] => Array ( [0] => stdClass Object ( [cat_id] => 6 [sub_cats] => 0 [cat_name] => Prova dopo ciclo [cat_end] => 2009-04-24 12:51:01 [cat_warning] => 0000-00-00 00:00:00 [cat_class] => B [cat_completed] => 0 ) ) [current_row] => 0 [num_rows] => 1 [row_data] => )

The code pass the latest value of the CATS table!!!
Why? Where is the error?

Thanks and sorry for bad english

[eluser]woomacoder[/eluser]
Nothing?

[eluser]Dam1an[/eluser]
Firstly, you can't always assume people will get back to you immediatly, so wait more then 4 and a half hours before bumping the thread

Secondly, what exactly is the relcats table, you only mention the cats and subcats tables in your post
On a side note, can you not just have all this in a single table, with id, parent_id, name, etc...

Also, can you try explain a bit more exactly what you're trying to acheive

Also, you might be getting a nameing conflict, as you have $DataSet in the outer loop, and then assigning another result to it (but will then carry on with the outer loop again)

[eluser]woomacoder[/eluser]
[quote author="Dam1an" date="1240610836"]Firstly, you can't always assume people will get back to you immediatly, so wait more then 4 and a half hours before bumping the thread

Secondly, what exactly is the relcats table, you only mention the cats and subcats tables in your post
On a side note, can you not just have all this in a single table, with id, parent_id, name, etc...

Also, can you try explain a bit more exactly what you're trying to acheive

Also, you might be getting a nameing conflict, as you have $DataSet in the outer loop, and then assigning another result to it (but will then carry on with the outer loop again)[/quote]

Thank's for reply; sorry form my hasty...
I Have two table: CATS and SUBCATS; I wanna extract all records of cats and ONLY records of SUBCATS that related with CATS; example:

cats contain:

ID NAME SUBCATS

1 A 0
2 B 2 (contains 2 subcategories)


subcats contain:

ID ID_CAT NAME

1 2(id of cats) SUBA
2 2 SUBB

I wanna extract all and printout that:

A
B
-- SUBA
-- SUBB

[...]

[eluser]Dam1an[/eluser]
I still don't see the point of storing the number of sub categories in the cats table, as you risk getting out of sync, and its very quick to do a count on the number of records where parent_id == cat_id

Could you not do something like

Code:

<?php
$categories = get_categories();
foreach($categories as $category) {
    // Print the top level category
    echo $category->name, '<br />';
    
    // get the sub categories
    $subcategories = get_categories($category->id);
    foreach($subcategories as $subcategory) {
        echo '- ', $subcategory->name, '<br />';
    }
}

function get_categories($parent_id = 0) {
    $this->db->where('parent_id', $parent_id);
    return $this->db->get('categories')->result();
}
?&gt;

[eluser]Eric Barnes[/eluser]
I agree with Dam1an. No need in having two separate tables for categories like this. A parent_id column will allow you to access any depth from the one table.

[eluser]woomacoder[/eluser]
[quote author="Dam1an" date="1240616391"]I still don't see the point of storing the number of sub categories in the cats table, as you risk getting out of sync, and its very quick to do a count on the number of records where parent_id == cat_id

Could you not do something like

Code:

<?php
$categories = get_categories();
foreach($categories as $category) {
    // Print the top level category
    echo $category->name, '<br />';
    
    // get the sub categories
    $subcategories = get_categories($category->id);
    foreach($subcategories as $subcategory) {
        echo '- ', $subcategory->name, '<br />';
    }
}

function get_categories($parent_id = 0) {
    $this->db->where('parent_id', $parent_id);
    return $this->db->get('categories')->result();
}
?&gt;

[/quote]

Yeah! Great solution! thank you!

[eluser]brandingdavid[/eluser]
So, where does this go?

I am trying to use it, and get:

Call to a member function on a non-object

Code is nearly identical... Anyone have any tips?

[eluser]brandingdavid[/eluser]
Trying to show a category tree (with subcats and sub-subcats)

Using this code:

Code:

<?php
    $categories = get_categories();
    
    foreach($categories as $category) {
            // Print the top level category
            echo $category->title .'<br />';
            
            // get the sub categories
            $subcategories = get_categories($category->id);
            foreach($subcategories as $subcategory) {
                echo '- '. $subcategory->title. '<br />';
            }
        }
        
        function get_categories($parent = 0) {
            $this->db->where('parent', $parent);
            $query = $this->db->get('bin_location');
            return $query->result();
        }
    ?&gt;

Getting: Fatal error: Call to a member function on a non-object in /home/domains/public_html/system/application/views/frontpage_view.php on line 25

Anyone have any ideas why I am having this problem?

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

It'd be helpful if you'd post the file that is actually giving you the error - the view file, in this case, as per the error message in your post.

Also handy if you show the controller code around the area where you load the view.