CODEWITHSUNDEEP
phpmysqljson
Rooz Far
Rooz Far

Reputation: 187

How to create nested JSON for the multi-level menu list using PHP and MYSQL?

I have been trying to create a multi-level nested JSON using MySQL data using PHP.

I will need this JSON so I can later on use jQuery to create HTML menu.

But I'm currently struggling to create my multi-level nested JSON. I have found 100's of similar questions on Stackoverflow website and google but they all slightly different.

Basically, I have a MYSQL database that looks like this:

id    post_title    post_parent

1     test 1         0
2     test 2         1
3     test 3         1
4     test 4         0
5     test 5         3
6     test 6         5
7     test 7         5

The column post_parent is the column that links them together.

I've tried using the following PHP code but the JSON output is wrong.

My current PHP code:

$return_arr=  array();
  $sql = mysqli_query($db_conx,"SELECT * FROM wp_posts WHERE post_status = 'publish' AND post_title !='Hello world!' AND post_title !='' ORDER BY post_title");
  while ($row = mysqli_fetch_array($sql ,MYSQLI_ASSOC)) 
  {

        if( !isset( $return_arr[ $row['post_parent'] ] ) ) {

            $return_arr[ $row['post_parent'] ] = array();

        }

        if( !isset( $return_arr[ $row['post_title'] ][ $row['post_title'] ] ) ) {

            $return_arr[ $row['post_parent'] ][ $row['post_title'] ] = array();

        }


  }       
  echo json_encode($return_arr);

The output of the above code is like this which is wrong:

{

    "0": {
        "test 1": [],
             "test 4": []
    },
    "1": {
        "test 2": [],
            "test 3": []
    },
    "3": {
        "test 5": []
    },
    "5": {
        "test 6": [],
            "test 7": []
    }

}

this is not showing a correct multi-level nested JSON data.

Could someone please advice on this issue?

Any help would be appreciated.

EDIT:

I will need a multi-level nested JSON like this structure:

   [  
 {
    "post_title":"Test 1",
    "children":[  
       {  
          "post_title":"test 3",
          "children":[  
             {  
                "post_title":"test 5",
                "children":[  
             {  
                "post_title":"test 6"
             },
              {  
                "post_title":"test 7"
             }
          ]
             }
          ]
       },
       {  
          "post_title":"test 2"
       }
    ]
 }
]

So then I can create a multi-level menu like this:

https://www.jqueryscript.net/demo/Menu-List-Generator-jQuery-renderMenu/index2.html

Upvotes: 0

Views: 1364

Answers (1)

ARN
ARN

Reputation: 719

First make an array with all childs and use the post_parent as index. In the example below I called this array $array_with_elements. After that you need a separate function so you can use this recursive.

    <?php

$array_with_elements = array();

$sql = mysqli_query($db_conx,"SELECT * FROM wp_posts WHERE post_status = 'publish' AND post_title !='Hello world!' AND post_title !='' ORDER BY post_title");

while ($row = mysqli_fetch_array($sql ,MYSQLI_ASSOC)) { 
  $array_with_elements[$row['post_parent']][] = $row;
}


function add_children($array_with_elements, $wp_level){

  $nested_array = array();
  foreach($array_with_elements[$wp_level] as $wp_post){
    $obj = new stdClass();
    $obj->title = $wp_post['post_title'];
    $obj->id = $wp_post['ID'];
    // check if there are children for this item
    if(isset($array_with_elements[$wp_post['ID']])){
      $obj->children = add_children($array_with_elements, $wp_post['ID']); // and here we use this nested function again (and again)
    }
    $nested_array[] = $obj;
  }
  return $nested_array;
}

// starting with level 0
$return_arr = add_children($array_with_elements, 0);

// and convert this to json
echo json_encode($return_arr);
?>

Upvotes: 1

Related Questions