current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.

Join them; it only takes a minute:

Sign up
Join the Stack Overflow community to:
  1. Ask programming questions
  2. Answer and help your peers
  3. Get recognized for your expertise
up vote 1 down vote favorite

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!

share|improve this question
up vote 1 down vote accepted

$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.

share|improve this answer
    
thanks for your response mate, but i wasnt using prepared statement so i just defined $a = array(); and i changed my code according to your suggestion but still its not working :( – Rishi Vishwakarma Oct 25 '15 at 2:55
    
You tried my code or you tried with an empty array? You are using prepared statements just not parameterized queries. – chris85 Oct 25 '15 at 2:58
    
i tried with your code but no luck today. – Rishi Vishwakarma Oct 25 '15 at 3:02
    
Okay, made an update.. – chris85 Oct 25 '15 at 5:04
    
@RishiVishwakarma any luck with the update? – chris85 Oct 29 '15 at 16:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.