Creating a drop-down menu to represent nested categories

I h开发者_JAVA技巧ave a category and this category is divided into subcategories at the level 5. I want to define a function that how we can access data form all level of category in proper way.

 Full Texts     id  cat_id subcat_id    cat_name subcat_name status
Edit    Delete  1       0       0       food                2
Edit    Delete  2       1       0       indian              1
Edit    Delete  3       2       0       japanis             1
Edit    Delete  4       3       0       chaines             1
Edit    Delete  5       4       0       american            1
Edit    Delete  6       5       0       southex             1
Edit    Delete  7       1       2       indian      up      0
Edit    Delete  8       1       2       indian      delhi   0

There is single root category food which contains 5 category which contains 5 sub category .i want to make function for that by which we can get all fields data as given below:

  food

* indian
  japanis
  chaines 
  american 
  southex

 indian
   up
   delhi

in the dropdown please help me.......

You don't need subcatid. Catid is enough . If you need multiple layers just add parentid with first level catid's parentid set to -1 or 0. See Oracle's EMP table where you can see the relation between EMPLOYEE and their MANAGER in the same table.

You should learn about database normalization - not sure why you are using cat_id/catname/ subcatid/subcatname in the same table? That is a huge redundancy. (There are cases where its useful but I am almost sure not in your case).

This table is not optimal... You have couple of useless columns. You just need "id", "parent_id" and "name" to do the job.

In "parent_id" column you have to put global "id" for the record where certain row belongs because every record, except ROOT has its parent. Then you don't need to worry about number of sub-levels.

example

[id] [parent_id] [name]
 1    0           food
 2    1           indian
 3    1           japanis
 4    1           chaines
 5    1           american
 6    1           southex
 7    2           up
 8    2           delphi

to filter level-1 categories use query "SELECT * FROM table_name WHERE parent_id='1'" where id=1 is row-id for category "food" (root). In same way you can filter all other levels with just replacing that number - id=2 for "indian", etc.

To change dropdowns on your page dynamically you need some JavaScript code (preloading values; easy way) or Ajax (update on-fly; bit harder way)