Create hierarchy from JSON data to mysql

Question

I have a JSON file that contains categories. The idea is to add this to the database with a hierarchical structure. The data is stored in the JSON file like this:
[{"productCategory":"Clothes - Pants - Jeans"},{"productCategory":"Clothes - Pants - Chinos"}]

How my database table should look like:

| taxonomy_id | taxonomy_name | taxonomy_parent | taxonomy_type |
| ----------- | ------------- | --------------- | ------------- |
|      1      |    Clothes    |        0        |    Category   |
|      2      |    Pants      |        1        |    Category   |
|      3      |    Jeans      |        2        |    Category   |
|      4      |    Chinos     |        2        |    Category   |

I've got it halfway. But I'm stuck on figuring out the rest. Maybe I need to to this a different way, but I want to check here first.
My code:

<?php

include_once('../../../products/categories.inc.php');

$string = file_get_contents("../products/products.json");
$json_decode = json_decode($string, true);


foreach ( $json_decode as $keys ) {

    $category = $keys['productCategory'];

    $split = explode( '-', $category );
    $has_child = count($split);
    $trim_whitespace = array_map('trim', $split);

    $categories = $trim_whitespace;

    if ( $has_child > 1 ) {
        
        $previous_value = 0;
        foreach ( $categories as $key => $value ) {

                $check_tax_name = new Insert_Taxonomy;
                $exsists_tax_name = $check_tax_name->Exists_Taxonomy_Name($value);

                if ( empty( $exsists_tax_name ) ) {
                
                    $check_tax_name = new Insert_Taxonomy;
                    $parent_id = $check_tax_name->Get_Taxonomy_Id($previous_value);

                    $args = array($value, $parent_id, 'category');
                    $object = new Insert_Taxonomy;
                    $object->create_taxonomy($args);

                    //echo "$previous_value<br>";
                    $previous_value = $value;

                }

        }

    } else {
        
        foreach ( $categories as $key => $value ) {

            $check_tax_name = new Insert_Taxonomy;
            $exsists_tax_name = $check_tax_name->Exists_Taxonomy_Name($value);

            if ( empty( $exsists_tax_name ) ) {

                $args = array($value, 0, 'category');
                $object = new Insert_Taxonomy;
                $object->create_taxonomy($args);

            }

        }

    }

}

My results:

| taxonomy_id | taxonomy_name | taxonomy_parent | taxonomy_type |
| ----------- | ------------- | --------------- | ------------- |
|      1      |    Clothes    |        0        |    Category   |
|      2      |    Pants      |        1        |    Category   |
|      3      |    Jeans      |        2        |    Category   |
|      4      |    Chinos     |       NULL      |    Category   |

score 0 · Accepted Answer · answered Nov 29 '21 at 15:51

After some more debugging i found the solution witch was just some small adjustment.

I had to check the parent id before checking if exists.
Also had to move previous_value after checking if exists. pretty obvious...

<?php

include_once('../../../products/categories.inc.php');

$string = file_get_contents("category.json");
$json_decode = json_decode($string, true);


foreach ( $json_decode as $keys ) {

    $category = $keys['productCategory'];

    $split = explode( '-', $category );
    $has_child = count($split);
    $trim_whitespace = array_map('trim', $split);

    $categories = $trim_whitespace;

    $taxonomy = new Insert_Taxonomy;

    if ( $has_child > 1 ) {
        
        $previous_value = 0;
        foreach ( $categories as $key => $value ) {

                $exsists_tax_name = $taxonomy->Exists_Taxonomy_Name($value);

                $parent_id = $taxonomy->Get_Taxonomy_Id($previous_value);

                if ( empty( $exsists_tax_name ) ) {

                    $args = array($value, $parent_id, 'category');
                    $taxonomy->create_taxonomy($args);

                }

                $previous_value = $value;

        }

    } else {
        
        foreach ( $categories as $key => $value ) {

            $check_tax_name = new Insert_Taxonomy;
            $exsists_tax_name = $check_tax_name->Exists_Taxonomy_Name($value);

            if ( empty( $exsists_tax_name ) ) {

                $args = array($value, 0, 'category');
                $taxonomy->create_taxonomy($args);

            }

        }

    }

}

Create hierarchy from JSON data to mysql

1 Answers1