如何使用PHP和MySQL创建JSON嵌套子父树（PDO方法）

Question

I am trying to build nested parent child JSON tree using PHP and MySQL. My aim is to create a JSON tree from my MySQL DB and display a tree in front-end using AngularJS. Creating tree is important.
My DB Structure is :

╔═══════╦═══════════════════╦═════════╗
║  id   ║       name        ║parent_id║
╠═══════╬═══════════════════╬═════════╣
║   1   ║       Parent      ║    0    ║
║   2   ║       Child-1     ║    1    ║
║   3   ║       Child-2     ║    1    ║
║   4   ║   Grand Child-1   ║    2    ║
║   5   ║   Grand Child-2   ║    2    ║
║   6   ║   Grand Child-3   ║    3    ║
║   7   ║   Grand Child-4   ║    3    ║
╚═══════╩═══════════════════╩═════════╝

I need the tree to look like:

Parent
    |--Child-1
    |    |--Grand Child-1
    |    |_ Grand Child-2
    |--Child-2
    |    |--Grand Child-3
    |    |_ Grand Child-4

And i have made something like this :

function hasChild($id){
    $sql = "SELECT count(*) FROM `myTable` WHERE parent_id=".$id;
    $stmt = $this->db->prepare($sql);
    $stmt->execute($a);
    $row = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return $row[0] > 0 ? true : false;
}//function hasChild($id)

// create an index on id
$index = array();
foreach($rows as $i =>$row)
{
    if (hasChild($i)) {
        $index[$row['id']] = $row;
    }
}

// build the tree
foreach($index as $id => $indexRow)
{
    if ($id === 1) continue;
    $parent = $indexRow['parent_id'];
    $index[$parent]['children'][] = $indexRow;                    
}
unset($indexRow);

echo json_encode($index);

But it's clearly not giving me correct json tree :(

I've looked at nesting json and array solutions and something is just not clicking for me, so I was hoping someone could help me out with this. I'm fine with using another way, as long as i can have the same/similar functionality.

Hopefully I was able to portray the situation well enough, but let me know if you need more data.

Thanks in advance!

Answer 1

$a is undefined. You also aren't using any placeholders in your query so I think that would fail out.

Try:

function hasChild($id){
    $sql = "SELECT count(*) as da_count FROM `myTable` WHERE parent_id = ?";
    $stmt = $this->db->prepare($sql);
    $stmt->execute(array($id));
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row['da_count'] > 0 ? true : false;
}//function hasChild($id)

A longer write up on prepared statements: http://php.net/manual/en/pdo.prepared-statements.php.