mysql | PHP | Join within own table

Question

i dont know if i am doing right or wrong, please dont judge me... what i am trying to do is that if a record belongs to parent then it will have parent id assosiated with it.. let me show you my table schema below.

enter image description here

i have two columns ItemCategoryID & ItemParentCategoryID

Let Suppose a record on ItemCategoryID =4 belongs to ItemCategoryID =2 then the column ItemParentCategoryID on ID 4 will have the ID of ItemCategoryID.

I mean a loop with in its own table..

but problem is how to run the select query :P I mean show all the parents and childs respective to their parents..

score 2 · Answer 1 · answered Oct 25 '13 at 09:49

This is often a lazy design choise. Ideally you want a table for these relations or/and a set number of depths. If a parent_id's parent can have it's own parent_id, this means a potential infinite depth.

MySQL isn't a big fan of infinite nesting depths. But php don't mind. Either run multiple queryies in a loop such as Nil'z's¹, or consider fetching all rows and sorting them out in arrays in php. Last solution is nice if you pretty much always get all rows, thus making MySQL filtering obsolete.

Lastly, consider if you could have a more ideal approach to this in your database structure. Don't be afraid to use more than one table for this.

This can be a strong performance thief in the future. An uncontrollable amount of mysql queries each time the page loads can easily get out of hands.

Hans Dubois · Answer 2 · 2013-10-25T09:51:16.210

I don't think you want/can to do this in your query since you can nest a long way.

You should make a getChilds function that calls itself when you retrieve a category. This way you can nest more than 2 levels.

function getCategory()
{
   // Retrieve the category


   // Get childs
   $childs = $this->getCategoryByParent($categoryId);
}

function getCategorysByParent($parentId)
{
  // Get category

  // Get childs again.
}

score 0 · Answer 3 · answered Oct 25 '13 at 09:46

Try this:

function all_categories(){
    $data   = array();
    $first  = $this->db->select('itemParentCategoryId')->group_by('itemParentCategoryId')->get('table')->result_array();
    if( isset( $first ) && is_array( $first ) && count( $first ) > 0 ){
        foreach( $first as $key => $each ){
            $second = $this->db->select('itemCategoryId, categoryName')->where_in('itemParentCategoryId', $each['itemParentCategoryId'])->get('table')->result_array();

            $data[$key]['itemParentCategoryId'] = $each['itemParentCategoryId'];
            $data[$key]['subs']                 = $second;
        }
    }
    print_r( $data );
}

score 0 · Answer 4 · edited May 23 '17 at 12:21

0

MySQL does not support recursive queries. It is possible to emulate recursive queries through recursive calls to a stored procedure, but this is hackish and sub-optimal.

There are other ways to organise your data, these structures allow very efficient querying.

edited May 23 '17 at 12:21

Community

1
1

answered Oct 25 '13 at 09:51

RandomSeed

29,301
6
52
87

score 0 · Answer 5 · answered Oct 25 '13 at 12:01

This question comes up so often I can't even be bothered to complain about your inability to use Google or SO search, or to offer a wordy explanation.

Here - use this library I made: http://codebyjeff.com/blog/2012/10/nested-data-with-mahana-hierarchy-library so you don't bring down your database

mysql | PHP | Join within own table

5 Answers5