PHP Pagestructure to array

Question

Okey, so this is my problem. I have a page structure saved into a mysql db. Like this:

Page 1
- SubPage 1.1
- - SubPage 1.1.1
- - - SubPage 1.1.1.1
- - - SubPage 1.1.1.2
- - SubPage 1.1.2
- SubPage 1.2
- SubPage 1.3
Page 2
Page 3

The structure can have endless pages and sub pages. All pages have a field called "url" and "childof". "childof" is what binds a page as a subpage of another.

Example: Page 1 have "url" page-1 and "childof" is empty
SubPage 1.1 have "url" subpage-1-1 and "childof" page-1
SubPage 1.1.1 have "url" subpage-1-1-1 and "childof" subpage-1-1

Hope you get the basic idea.

My problem is to make a loop to get all these pages out in one good array.

To get first line of pages is easy;

$sql = "SELECT * FROM `page` WHERE `childof` = ''
  ORDER BY `id` DESC";
$result = mysql_query($sql);
$i=0;
while ($row = mysql_fetch_assoc($result)) {
 $pages[$i]['id'] = $row['id'];
 $i++;
}

To get second line is easy aswell...

for($x=0; $x < sizeof($pages); $x++){
 $sql = "SELECT * FROM `page` WHERE `childof` = '".$pages[$x]['url']."'
   ORDER BY `id` DESC";
 $result = mysql_query($sql);
 $i=0;
 while ($row = mysql_fetch_assoc($result)) {
  $pages[$x]['children'][$i]['id'] = $row['id'];
  $i++;
 }
}

And offcourse I could go on like this. But to do this without knowing how many subpages there are is not very efficient. So how do I make a loop to retrieve all pages and subpages into an array with a good structure?

Thank you!

Answer 1

You want to use recursion to solve this is here some example code of something I did recently to solve a similar task

function retrieveFolderChildren($iRoot, &$aChildren, $cMysql)
    {
        if($cStatement = $cMysql->prepare("SELECT folderName, id FROM dir_structure WHERE rootId=? ORDER BY folderName"))
        {
            $cStatement->bind_param('i', $iRoot);
            $cStatement->execute();

            $cStatement->bind_result($sFolderName, $iId);

            while ($cStatement->fetch())
            {
                $aChildren[] = array( "folderName" => $sFolderName, "id" => $iId, "children" => array());
            }

            $cStatement->close();
        }

        foreach ($aChildren as &$aChild)
        {
            retrieveFolderChildren($aChild["id"], $aChild["children"], $cMysql);
        }
    }

Answer 2

Edit your table , make new field called subcat as INT 1 = true 0 = false

Create ifstatement to look for the value, if yes go to the while loop else, do nothing i guess?

Answer 3

The first solution will be to use recursion to retrieve the children of each page. But this can result in a huge number of queries when the number of pages become large, especially if they are nested deeply.

You might want to look at this blog-entry as to retrieve all pages with a single query and then using a map to access children of a certain page.