User Login

Please enter user name and password into following boxes!

If you are not registered yet, please click

Forget Password

If you forget your password, please click
favorite

Managing Hierarchical Data in MySQL And Php

Category Table

CREATE TABLE category
(
  id         INT AUTO_INCREMENT PRIMARY KEY,
  name       VARCHAR(50)  NULL,
  url         VARCHAR(50)  NULL,
  parent     INT          NULL,
  created_at TIMESTAMP    NULL,
  updated_at TIMESTAMP    NULL,
  deleted_at TIMESTAMP    NULL,
  resim      VARCHAR(255) NULL,
  kisaltma   VARCHAR(255) NOT NULL,
  CONSTRAINT category_category_parent_fk
  FOREIGN KEY (parent) REFERENCES category (id)
    ON DELETE SET NULL
)
  ENGINE = InnoDB
  COLLATE = utf8_unicode_ci;

CREATE INDEX category_category_parent_fk
  ON category (parent);

PHP Implementation

/**
* @return array
*/
public function getCategories()
{
    $categories = DB::table('category as k1')
    ->join('category as k2', 'k1.id', '=', 'k2.parent','left')
    ->select('k1.name as parent_name', 'k1.url as parent_url', 'k1.resim as parent_resim',
    'k2.name as child_name', 'k2.url as child_url','k2.parent as parent_id' )
    ->get();
    $parents = array();
    $parentNames = array();
    foreach ($categories as $category) {
        if (in_array($category->parent_name, $parentNames)) continue;
        $parentNames[] = $category->parent_name;
        $parents[] = array("name" => $category->parent_name, "url" => $category->parent_url,
        "resim" => $category->parent_resim,"parent_id"=>$category->parent_id);
    }
    $result = array();
    $ttt=[];
    foreach ($parents as $parent) {
        $children = array();
        
        foreach ($categories as $category) {
            if($category->child_name==null)continue;
            
            if ($parent["name"] == $category->parent_name) {
                $children[] = array("name" => $category->child_name, "url" => $category->child_url);
            }
        }
        if(count($children)==0)$ttt[]=$parent["name"];
        $result[$parent["name"]] = array("fields" => $parent, "children" => $children);
    }
    
    foreach ($result as $key=>$value){
        foreach ($value["children"] as $child){
            if(in_array($child["name"],$ttt)){
                unset($result[$child["name"]]);
            }
        }
    }
    return array_values($result);
}

Result

{
  "success": true,
  "data": [
    {
      "fields": {
        "adi": "nam",
        "url": "nam",
        "resim": "9c14851b6288023a6d7b507b8e5f1340.jpg",
        "parent_id": null
      },
      "children": []
    },
    {
      "fields": {
        "adi": "ELEKTRİK",
        "url": "",
        "resim": "5ibrGExPtyTzPV9pMr31.jpg",
        "parent_id": 6
      },
      "children": [
        {
          "adi": "provident",
          "url": "provident"
        },
        {
          "adi": "ipsum",
          "url": "ipsum"
        }
      ]
    },
    {
      "fields": {
        "adi": "AYDINLATMA",
        "url": "",
        "resim": "X14AOtutnGaFq7Yv1VmY.jpg",
        "parent_id": 7
      },
      "children": [
        {
          "adi": "architecto",
          "url": "architecto"
        },
        {
          "adi": "praesentium",
          "url": "praesentium"
        }
      ]
    },
    {
      "fields": {
        "adi": "ELEKTRONİK",
        "url": "",
        "resim": "HebnYgcf66zL6OxTRFa7.jpg",
        "parent_id": null
      },
      "children": []
    },
    {
      "fields": {
        "adi": "HIRDAVAT",
        "url": "",
        "resim": "DMCJZYNT63Mx3Qwe7wUt.jpg",
        "parent_id": null
      },
      "children": []
    }
  ],
  "message": ""
}


PayPal - The safer, easier way to pay online!


Comments About Tutorial

There is no comment



Add Comment

Name*
Surname* 
Email* 
Subject* 
What is (12 + 5) :
WARNING

Please fill all text boxes

Message Result

Your comment is saved. After approved, it will be published

An Error Occured

We are sorry, your request cannot be continued try again later

LOGIN REQUESTED

Please login for this operation
Login