Category and Sub-Category

[eluser]mstojanov[/eluser]
Hello guys what i'm trying to accomplish here is to have 3 categories and in each 3 categories to have sub-categories.

What i cant figure out how to insert the parent_id of the sub-category which i'm inserting.


Here is my model :

Code:

$p_id = $this->db->insert_id();
$category= array(

   'sub_cat_name'=>$this->input->post('category'),
   'post_id'=>$p_id,
   'parent_id'=> ???
   );


  $this->db->insert('sub_category',$category);

In my db i have :

Table : POSTS
Table: categories
fields : cat_id(AUTO INCREMENT), cat_name
example :
cat_id = 1 ; cat_name = Cars
cat_id = 2 ; cat_name = Phone

Table: sub_category
fields : sub_id(AUTO INCREMENT), parent_id, sub_cat_name, post_id

As you can see before i try to insert into the category i'm inserting the post first and the i'm inserting the category in the category table
thats why i'm getting the post id like this way mentioned in the begginig

Code:

$p_id = $this->db->insert_id();

And here is my view :

Code:

<select `name="category"  class="form-control" >
<option value=""> --Choose category--</option>
        <option class="cat_head" value="Cars" &lt;?php echo set_select('kategorija', 'Cars'); ?&gt; >Cars</option>
<option value="BMW" &lt;?php echo set_select('category', BMW'); ?&gt; >BMW</option>
<option value="AUDI" &lt;?php echo set_select('category', 'AUDI'); ?&gt; >AUDI</option>
<option value="RENAULT" &lt;?php echo set_select('category', 'RENAULT'); ?&gt; >RENAULT</option>
&lt;!-- Cars End --&gt;

&lt;!-- Phone Start --&gt;
<option class="cat_head" value="Phone" &lt;?php echo set_select('kategorija', 'Phone'); ?&gt; >Phone</option>
<option value="Samsung" &lt;?php echo set_select('category', 'Samsung'); ?&gt; >Samsung</option>
<option value="Nokia" &lt;?php echo set_select('category', 'Nokia'); ?&gt; >Nokia</option>
&lt;!-- Phone End --&gt;
</select>

What i want here is when somebody select example BMW the parent category id to be CARS and that for AUDI too.

So in that way if somebody select BMW i can list my category as Cars or if only select CARS and not select some sub-category to be able to list in CARS category.

How can i accomplish this ? To insert the parent ID to my database.
Or if my database fields or tables are not ordered right, give me the correct answer.

Thanks guys
Waiting for help/

[eluser]ivantcholakov[/eluser]
According to my experience it would be better if you use only ONE table for categories with the following sample structure:

id (autoincrement)
parent_id
name
description
slug
metatitle
metadescription
metakeywords

Then you can use only the id as a foreign key within the other tables, you would not be forced to use composite id.

And URL generation and parsing would be simpler:

http://site.com/products/category/1 - this is easy to be done, but this:
http://site.com/products/category/1-5 - uses a composite ID, it is possible, however code gets needlessly complicated. A have tried this way too on a legacy application.

Also, if you use slugs:
http://site.com/products/nice-cars (matches to http://site.com/products/category/1) - it would be easier this match slug <-> id to be implemented.

And finally, in your administration panel, code (user interface) for filling the categories would be simpler.

Use only one table for categories!