0

I currently have a MySQL table set up as follows:

ID FirstName LastName Gender FParent MParent CreationTimeStamp
1 Lilly Carbeso 1 0 0 2021-07-23 12:14:40
2 Cameron Colebourne 0 0 0 2021-07-23 12:16:42
3 Liam Nugent 0 1 2 2021-07-23 12:17:55
4 Craig Newell 0 1 2 2021-07-23 12:27:00

And I am currently parsing the full table to output a JSON array that looks like this:

[{"ID":"1","FirstName":"Lilly","LastName":"Carbeso","Gender":"1","FParent":"0","MParent":"0","CreationTimeStamp":"2021-07-23 12:14:40"},{"ID":"2","FirstName":"Cameron","LastName":"Colebourne","Gender":"0","FParent":"0","MParent":"0","CreationTimeStamp":"2021-07-23 12:16:42"},{"ID":"3","FirstName":"Liam","LastName":"Nugent","Gender":"0","FParent":"1","MParent":"2","CreationTimeStamp":"2021-07-23 12:17:55"},{"ID":"4","FirstName":"Craig","LastName":"Newell","Gender":"0","FParent":"1","MParent":"2","CreationTimeStamp":"2021-07-23 12:27:00"}]

Using the current php:

<?php
    require_once "gene-config.php";
    $connection = new mysqli($servername, $username, $password, $dbname) or die("Error " . mysqli_error($connection));

    $sql = "select * from gtable";
    $result = mysqli_query($connection, $sql) or die("Error in Selecting " . mysqli_error($connection));

    $emparray = array();
    while($row =mysqli_fetch_assoc($result))
    {
        $emparray[] = $row;
    }
    echo json_encode($emparray);

    mysqli_close($connection);
?>

The format that I would like the data to look like and what D3JS uses is below:

{
  "name": "Eve",
  "children": [
    {
      "name": "Cain"
    },
    {
      "name": "Seth",
      "children": [
        {
          "name": "Enos"
        },
        {
          "name": "Noam"
        }
      ]
    },
    {
      "name": "Abel"
    },
    {
      "name": "Awan",
      "children": [
        {
          "name": "Enoch"
        }
      ]
    },
    {
      "name": "Azura"
    }
  ]
}

"FParent" is the ID value of the female parent "MParent" is the ID value of the male parent

What I am struggling to understand is how is it possible to parse a JSON object from a MySQL table that would nest the children based on the IDs, "FParent" and "MParent" values?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Liam Nugent
  • 43
  • 1
  • 6
  • It is a very bad idea to use `die(mysqli_error($conn));` in your code, because it could potentially leak sensitive information. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) – Dharman Jul 23 '21 at 12:00
  • @Dharman Thank you so much for this, having read through the article I will update my code to throw an error instead. – Liam Nugent Jul 23 '21 at 12:16
  • I think you cannot do that with a query. You need to fetch all rows and build your array recursively. – Markus Zeller Jul 23 '21 at 12:29
  • Does this so answer your question? https://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query – Hisham Jul 23 '21 at 12:29
  • @Hisham I will have a read-through now and see if I can implement some of the suggested methods in that post. I will update when nessecary. – Liam Nugent Jul 23 '21 at 12:37

0 Answers0