Managing Hierarchical Data in MySQL And Php

17-02-2018

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": ""
}

© 2019 All rights reserved. Codesenior.COM