Create Category tree with PHP and mysql


Every now and then you will come across to a situation where you have to make a recursive calls to get all your parent and childs in an array or in a dropdown or in a list format. In this tutorial you will learn how to create category tree with PHP and mysql. You can then use this category tree to display in a dropdown box or list.

View Demo

Creating a mysql table with parent and child columns

Insert some dummy records. Parent columns with zero value denotes its the base/root category.

Creating category Tree using recursion

So that the database is all set up, create a php function to recursively get all the category grouping them ito their parent category.

The working of this function is pretty simple. It will start its iteration with the parent category and will call the function itself if it has child category and again if childs has childs until the child has no childs (phew!!! quite a line it was). The value returned by the function is stored in the array and is carried forward in the recursive function. So finally at the end of the function we will have an array with all categories grouped under the subcategories. The $spacing parameter is just just to indent the child category.

Display the category tree in a dropdown list.

Now that the hard work is done. time to eat the fruit.

Displaying Category tree in list format

To display the category use this snippet below.

View Demo


About Author

Entrepreneur & Dreamer

I am a passionate Software Professional, love to learn and share my knowledge with others.

Software is the hardware of my life.


        • brother,
          i have to create, show and update also categories from front end. what will be the way to update with html form ?

          Jazakumullaah .. for this good helping blog ..keep it up brother.

          • Create a table say “tbl_category” with 3 fields ID, NAME and PARENT. the parent column default value will be zero. Now when you create html form give column for name (textfield) and a parent field (dropdown) with will have a option with “0” i.e. for parent and rest options with generated using a sql query result that will fetch all category list from “tbl_category”.

  1. Hi!
    Why don’t you do ONE sql query and than process recursive function to build an array of category tree instead of multiple queries? You can decrease php-server load… it’s obvious, am I right? 😉

  2. Rasmi Ranjan Swain on

    im getting this error when i call function recursively

    Warning: mysqli::mysqli() [mysqli.mysqli]: (08004/1040): Too many connections

  3. How can I have root categories before child category? like this:

    – Hardware
    – Hardware > Printers
    – Hardware > Printers > Inkjet printers
    – Hardware > Printers > Laserjet Printers
    – Hardware > Monitors
    – Hardware > Monitors > LCD monitors
    – Hardware > Monitors > TFT monitors

  4. i signup and get a code , with my referral code only two can join , more then two members cant join its condition . Then first signup person should come on left and second person on right , like this it should go chain process , please help me from this

    • This is a very good problem. I can code it, but i would also like you to post your some code here. so I can know you are working on this as well and not just need a total work done by others.

  5. is there any way to lower SQL queries? i mean if i have thousand of categories and sub categories then it will make thousands of queries on single time.
    Single page = thousands of SQL queries
    thousands of visitors = millions of SQL queries on single page .
    Server Result = Always Down After processing few pages.

    i am trying to make my own code within one or few SQL queries…

  6. I used your code it work well… Thank you

    Please can you help me In Display the category tree in a dropdown list I need like, I show you in below

    For Example In dropdown list Value like:

    Inject Printers
    Laser Printers
    LCD Monitors
    TFT Monitors

    I need parent Hardware, Printers and Monitors child level are disabled , so you cant add new child for this two child.
    How Can I do it please help me,
    Thank you

      • this code is posted in many sites with thesame code… dont know who really originally created this.
        just wanna ask you question if u pls
        1. if parent` int(11) NULL, how would u display it
        2. the tables have no relationship, if you delete parent like 0 will display nothing but other data still there.

        • 1. you need to join the same table twice in the query to get the parent and child at the same time.
          2. yes that is the drawback if its in the same table, if you want to can create two table like parent and child then your problem will be solved.

  7. you can fetch the data in a associate array and display it using traversing each parent and child of that parent . no need to write two queries . you can achieve this by an associate array .

Leave A Reply

CommentLuv badge

Get more stuff like this
in your inbox

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.