Getting issues creating valid JSON object to use for jstree

Question

I am trying to create a tree using jstree which will show that all tables of a specific database along the columns of the tables.

Now this is my script, which I am using to fetch the table names first from the database and later using that table name, to fetch all the column are available in that table.

<?php
    $servername = "localhost";
    $username = "test";
    $password = "test";
    $dbname = "test";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    } 

    $post_data = array('id' => $dbname,'text' => $dbname, 'children' => array());

    //echo "Connected successfully";

    $sql = "SHOW tables";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            $new = array();
            array_push($new, array("id" => $row['Tables_in_test'], "text" => $row['Tables_in_test'], "children" => array()));

            $sql1 = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '".$dbname."' AND TABLE_NAME = '".$row['Tables_in_test']."'";
            $result1 = $conn->query($sql1);
            while($row1 = $result1->fetch_assoc()) {
                array_push($new[0]['children'], array("id" => $row1['COLUMN_NAME'], "text" => $row1['COLUMN_NAME']));
            }

            array_push($post_data['children'], $new);
        }
    } else {
        echo "0 results";
    }
    print_r(json_encode($post_data));

    $conn->close();
?>

Now, I am getting data in the following manner:

{
"id": "test",
"text": "test",
"children": [
  [
    {
      "id": "accounts",
      "text": "accounts",
      "children": [
        {
          "id": "id",
          "text": "id"
        },
        {
          "id": "name",
          "text": "name"
        }
      ]
    },
    {
        "id":"accounts_cases",
        "text":"accounts_cases",
        "children":[
        {
          "id":"id",
          "text":"id"
        },
        {
          "id":"account_id",
          "text":"account_id"
        }
      ]
    }
  ],
],
}

Now, this format of data is not working with jstree. As you can see, the first children is an array but due the script it is somehow shows array into array. It is like this:

"text": "test",
"children": [
 [
    "id": "accounts",

this should be like this:

"text": "test",
"children": [
    "id": "accounts",

I don't know how to explain this, but It should be like this to work properly:

{
"id": "sugarcrm",
"text": "sugarcrm",
"children": [
    {
      "id": "accounts",
      "text": "accounts",
      "children": [
        {
          "id": "id",
          "text": "id"
        },
        {
          "id": "name",
          "text": "name"
        }
      ]
    },
    {
        "id":"accounts_cases",
        "text":"accounts_cases",
        "children":[
        {
          "id":"id",
          "text":"id"
        },
        {
          "id":"account_id",
          "text":"account_id"
        }
      ]
    }
],
}

I know that there is something wrong in my script, but I am unable to correct. So, kindly help me out here.

I'm not too familiar with php, but perhaps you can try array_merge on this line? `array_push($post_data['children'], $new);` https://stackoverflow.com/questions/4268871/php-append-one-array-to-another-not-array-push-or — Vivek Chavda, Jul 31 '17 at 21:22
Nope, it is not working. As mentioned in the link, this won't work if an array has keys in string format. It will overwrite the previous one. And that the one is happening. — Tony Montana, Aug 01 '17 at 06:12
Maybe you can use `array_push` to push onto another array (not directly into 'children'), and then use `array_merge` after the while loop ends. It currently seems like it's trying to push an array *inside* the children array, rather than adding it as an element. — Vivek Chavda, Aug 01 '17 at 14:00

score 0 · Accepted Answer · answered Aug 01 '17 at 18:51

Finally, I found the solution after one day/night of struggle. Instead of using another array $new, I found out that in the same main array I can push new array into that. Here find the below code:

if ($result->num_rows > 0) {
    // output data of each row
    $i = 0;
    while($row = $result->fetch_assoc()) {

       array_push($post_data['children'], array("id" => $row['Tables_in_test'], "text" => $row['Tables_in_test'], "children" => array()));

       $sql1 = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '".$dbname."' AND TABLE_NAME = '".$row['Tables_in_test']."'";

       $result1 = $conn->query($sql1);

       while($row1 = $result1->fetch_assoc()) {

          array_push($post_data['children'][$i]['children'], array("id" => $row1['COLUMN_NAME'], "text" => $row1['COLUMN_NAME']));
       }
       $i++;
    }
} else {
    echo "0 results";
}

In above code, used $i counter to check get the latest pushed array, as you know, array_push inserts into array and set the key to an integer like 0, 1, ...and so one.

Hopefully, it will help someone, having similar problem.

Getting issues creating valid JSON object to use for jstree

1 Answers1