Parent\child\child array

Jamiesw · July 14, 2019

Hi I am trying to create a hierarchy array from our mysql database.

What i have so far is:

//instantiate DB & connect
$database = new Database();
$db = $database->connect();

//instantiate question object

$question = new Question($db);

//query
    $results = $question->read(); 
   
      //check for data
    if ($results) {

     $question_arr['questions'] = array();
     $child_arr = array();
     
    while ($row = $results->fetch(PDO::FETCH_ASSOC)) {
        
        array_push($question_arr['questions'], $row);

        $question_arr['questions'][] = buildTree($row);

    }
        print_r($question_arr['questions']);
} else {
    echo json_encode(array('message'=>'nothing here!.'));
}


function buildTree($child) {
    $branch = array();
   foreach ($child as $row) {
    
    $childresults = $question->read_children($row['parentid']); 
    
           while ($childrow =$childresults->fetch(PDO::FETCH_ASSOC)) {
             
            $children = buildTree($childrow);

            if ($children) {
             $row['children'] = $children;
            }
         
            $branch[] = $childrow;
        }
    }
   //}
    return $branch;
}

in my Questions.php file:

<?php
class Question{
    private $conn;
    public $id;
    public $childid;
    public $parentid;
    public $question;
    public $pageid;
    public $numclicks;
    public $typeid;
    public $date_created;

 
     // Constructor with DB
     public function __construct($db) {
        $this->conn = $db;
      }

   public function read() {
        //create query(sql statement)
      $query = 'SELECT id as parentid, question, pageid, typeid,numclicks,date_created from ct_questions';
      // Prepare statement
      $stmt =$this->conn->prepare($query);
      // Execute query
      $stmt->execute();
      return $stmt;
    }

public function read_children($parentid)
{
//This is from a View I created in Mysql
    $query = 'SELECT parent_question, parentid, parent_pageid, child_question, childid, child_pageid from ct_vquestion_parent_child_lookup where parentid='.$parentid.'';
    // Prepare statement
    $stmt = $this->conn->prepare($query);
  
    // Execute query
    $stmt->execute();
   
    return $stmt;
}
}

the current errors i am getting are:

Notice: Undefined variable: question in C:\htdocs\api\questions\read.php on line 41 Fatal error: Uncaught Error: Call to a member function read_children() on null in C:\htdocs\api\questions\read.php:41 Stack trace: #0 C:\htdocs\api\questions\read.php(28): buildTree(Array) #1 {main} thrown in C:\htdocs\api\questions\read.php on line 41

gw1500se · July 14, 2019

Which is line 41? Also what do you expect this to do as the end of your query string?

.'';

maxxd · July 14, 2019

1 hour ago, Jamiesw said:

$query = 'SELECT parent_question, parentid, parent_pageid, child_question, childid, child_pageid from ct_vquestion_parent_child_lookup where parentid='.$parentid.'';
// Prepare statement
$stmt = $this->conn->prepare($query);

This is not how you do a prepared statement. You're still injecting the variable directly into the SQL string. Check the manual for the proper way to use prepare() (note that the link is for PDO, but the concept is the same for mysqli.

Barand · July 14, 2019

You are referencing the variable $question inside your function

Quote

function buildTree($child) {

    $branch = array();

    foreach ($child as $row) {

         $childresults = $question->read_children($row['parentid']);

$question must be defined inside the function or passed to the function as an argument,

Jamiesw · July 14, 2019

Hi All,

Thanks, i have changed the code but get new error. Line 42 is : $question->parentid = $row['parentid'];

Warning: Illegal string offset 'parentid' in C:\htdocs\api\questions\read.php on line 42 


function buildTree($child) {
    global $question;
    $branch = array();
   foreach ($child as $row) {
    
    $question->parentid = $row['parentid'];
    $childresults = $question->read_children(); 
    
           while ($childrow =$childresults->fetch(PDO::FETCH_ASSOC)) {
             
            $children = buildTree($childrow);

            if ($children) {
             $row['children'] = $children;
            }
         
            $branch[] = $childrow;
        }
    }
   //}
    return $branch;
}



-----------------------In My Questions.php--------------------
class Question{
  public $childid;
    public $parentid;
    public $question;

public function read_children()
{
    $query = 'SELECT parent_question, parentid, parent_pageid, child_question, childid, child_pageid from ct_vquestion_parent_child_lookup where parentid=?';
    // Prepare statement
    $stmt = $this->conn->prepare($query);
   
    // Bind ID
    $stmt->bindParam(1, $this->parentid);
   
    // Execute query
    $stmt->execute();
   
    return $stmt;
}
}

Barand · July 14, 2019

You need to be aware of what your variables contain. It would seem that $row is a string and not an array.

What does this output...

echo '<pre>', print_r($child, 1), '</pre>';

Jamiesw · July 14, 2019

Hi Barand,
here is the result.

<pre>Array
(
    [parentid] => 1
    [question] => Looking for somewhere to stay
    [pageid] => 1
    [typeid] => 1
    [numclicks] => 503
    [date_created] => 
)
</pre>

Edited July 17, 2019 by cyberRobot
added code tags

Barand · July 14, 2019

Therein lies your problem.

The parent id is $child['parentid'] since $child is the array, not $row. (When you get that error message on the first iteration, $row contains "1")

mac_gyver · July 14, 2019

in addition to not using a prepared query correctly, your code should be able to reuse an already prepared query, which it can simply by using implicate binding and calling the execute() method on the returned PDOStatment object with an array parameter consisting of the input data.

next, you have two logic problems, where code isn't doing what you think. the first one -

3 hours ago, Jamiesw said:

//check for data if ($results) {

$result is normally a PDOStatment object. It will be a true value if the prepare() statement succeeded. It will be a false value if the prepare() statement failed due to an error. it doesn't have anything to do with data from a query. you should be using exceptions to handle database errors (connection, query, prepare, and execute) and in most cases let php catch and handle the exception, where it will use its error related settings to control what happens with the actual error information (database errors will get displayed or logged the same as php errors.) to detect if there is data from a query, just fetch the data and test the fetched result.

the second one -

3 hours ago, Jamiesw said:

foreach ($child as $row) {

you are calling the buildTree(...) function with a row of data from a query, which is an array of the elements in a row. it is not an array of rows or an array of parent ids (which is what you should be doing), so looping over the elements in the supplied value doesn't make any sense. before writing code to do something, it would help if you first wrote a comment that defined what the input(s) are, what processing is going to be done, and what result is returned.

next, you should ALMOST never run queries in loops. it is extremely inefficient, mainly due to the communications involved between php and the database server (for most simple queries, the time it takes to execute the query is several times less then the communication time, so you should perform a tasks using the fewest number of queries.)

what you should do -

execute the first query to get all the first level parent data. (i'm not sure why you have a separate table for the first level parent data, all the data should be in a single table.)
get all the first level parent ids into an array.
call a recursive function, with the array of parent id as its input, to get all the corresponding child data.
if there is no further child data, return from the function.
if there is child data, store it in a data structure using the current level's parent id as the array index at the current data level.
get all the parent ids from the current level data into an array and call the recursive function again.

note: you can use FIND_IN_SET(some_column,?) in a prepared query, then supply a comma delimited string of values via a prepared query place-holder, to let you (re)use a single prepared query inside the recursive function.

mac_gyver · July 14, 2019

a couple of implementation points -

if you set the fetch mode to assoc when you make the database connection, you won't have to specify it in each fetch statement.
you seem to be creating class properties regardless of if they are being used. this is just wasting your time typing things. only create properties, variables, ... if they are needed. for what you are doing, you only need a property for the database connection.

Jamiesw · July 17, 2019

I have modified the code and i think it is giving me the right answers. My question is adding the child array to the parent.

$question = new Question($db);

//query

    $results = $question->read(); 
   $numRows = $results->rowCount();
      //check for data
    if ($numRows >0) {

     $question_arr['questions'] = array();
     $child_arr = array();
     $question_arr['questions'] = $results->fetchAll(PDO::FETCH_ASSOC);

    foreach ($question_arr['questions'] as $row){

        //Need to add $child_arr to the current $question_arr['question']
    $child_arr = buildTree($row['id']);


}
echo json_encode($question_arr['questions']);
} else {
    echo json_encode(array('message'=>'nothing here!.'));
}


function buildTree($id) {
    global $question;
    $question->id = $id;
    $child =array();
    $children = array();
    $results = $question->read_children(); 
    $numRows = $results->rowCount();
    if ($numRows>0) {
        while ($row = $results->fetch(PDO::FETCH_ASSOC)) {
        $children[] =$row;
        $child = buildTree($row['childid']);
            if ($child) {     
                $children[] = $child;
            }
        }
    }
    return $children;
}

Barand · July 17, 2019

I have no idea what you are starting with or what you want to finish with, so here is general method of dealing with a hierarchy.

category table

+--------+-----------+--------+
| cat_id | catname   | parent |
+--------+-----------+--------+
|      1 | Cat 1     |      0 |
|      2 | Cat 2     |      0 |
|      3 | Cat 3     |      0 |
|      4 | Cat 1 1   |      1 |
|      5 | Cat 1 2   |      1 |
|      6 | Cat 2 1   |      2 |
|      7 | Cat 2 2   |      2 |
|      8 | Cat 2 3   |      2 |
|      9 | Cat 3 1   |      3 |
|     10 | Cat 1 1 1 |      4 |
|     11 | Cat 1 1 2 |      4 |
|     12 | Cat 2 3 1 |      8 |
+--------+-----------+--------+

Code output

image.png.df3ac3b2b2ce37225e7b0e56bb697044.png

Store arrays of items belonging to each parent in an array

Starting with parent 0, list its children but each time you list a child you check if it is itself a parent and list its children. The recursive function does this for you.

Code

$sql = "SELECT cat_id, catname, parent
        FROM category";
$res = $db->query($sql);
//
// store arrays of items for each parent in an array
//
while (list($id, $name, $parent) = $res->fetch_row()) {
    $data[$parent][] = array('id'=>$id, 'name'=>$name);
}
echo '<pre>', print_r($data, 1), '</pre>';    

// call the recursive function
displayHierarchy($data, 0);

// function to print a category then its child categories 
function displayHierarchy(&$arr, $parent)
{
    if (isset($arr[$parent]))
        echo "<ul>\n";
    foreach($arr[$parent] as $rec)
    {
        echo "<li>{$rec['name']}";
        if (isset($arr[$rec['id']]))
            displayHierarchy($arr, $rec['id']);
        echo "</li>\n";
    }
    echo "</ul>\n";
}

Edited July 17, 2019 by Barand

Jamiesw · July 17, 2019

Thanks for relying.

We have 2 tables

Table 1 - tbl_questions (pageid has nothing to do with the parent \child, it references something else)

id | Question | pageid

1 | looking for accommodation? | 1A
2 | How do you pay? | 1A1
3 | Can you stay with friends? | 2A2

Table 2 - tbl_question_relations
parentid | childid
1|2
1|3
1|4
2|3
2|4
3|5
3|6
4|3
4|1

so the output would be in hierarchical json

Barand · July 17, 2019

Assuming the "parentid" in the relations table relates to the question.id

$res = $db->query("SELECT  q.id
                         , q.question
                         , q.pageid
                         , childid
                    FROM question q 
                           LEFT JOIN
                         question_relations r ON q.id = r.parentid; 
                  ");
$questions = [];
foreach ($res as $r) {
    if (!isset($questions[$r['id']])) {
        $questions[$r['id']] = [ 'question' => $r['question'],
                                 'pageid'   => $r['pageid'],
                                 'relations'=> []
                               ];
    }
    $questions[$r['id']]['relations'][] = $r['childid'];
}

$jQuestions = json_encode($questions);

The resulting questions array looks like...

Array
(
    [1] => Array
        (
            [question] => looking for accommodation?
            [pageid] => 1A
            [relations] => Array
                (
                    [0] => 2
                    [1] => 3
                    [2] => 4
                )

        )

    [2] => Array
        (
            [question] => How do you pay?
            [pageid] => 1A1
            [relations] => Array
                (
                    [0] => 3
                    [1] => 4
                )

        )

    [3] => Array
        (
            [question] => Can you stay with friends?
            [pageid] => 2A2
            [relations] => Array
                (
                    [0] => 5
                    [1] => 6
                )

        )

)

Is that what you are trying to do?

Jamiesw · July 17, 2019

thanks yes, however the bit i have been struggling with is the children can have children so it is N deep. and of course then the children can be parents aswell.

Array
(
    [1] => Array
        (
            [question] => looking for accommodation?
            [pageid] => 1A
            [relations] => Array
                (
                    [0] => Array
        					(
            					[question] => How do you pay?
            					[pageid] => 1A1
            					[relations] => Array		
                			(
                    			[0]=> Array
      						  			(
        								    [question] => Can you stay with friends?
         								    [pageid] => 2A2
            								[relations] => Array
                						(
                			)
                    [1] => 4
                )
        )
                    [1] => 3
                    [2] => 4
                )

        )

    [2] => Array
        (
            [question] => How do you pay?
            [pageid] => 1A1
            [relations] => Array
                (
                    [0] => 3
                    [1] => 4
                )

        )

    [3] => Array
        (
            [question] => Can you stay with friends?
            [pageid] => 2A2
            [relations] => Array
                (
                    [0] => 5
                    [1] => 6
                )

        )

)

Barand · July 17, 2019

Apply the mthod in my previous post

Sign In

Parent\child\child array

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information