How to generate breadcrumbs using recursion on database row data?

Question

I have to make a breadcrumb menu which comes database.

So made this function

function file_list($path) {
    $result = array();
    $q = "SELECT staticTitle,staticId,parentId FROM tbl_static_pages WHERE staticId = $path";
    $run = mysql_query($q);
    while($row = mysql_fetch_array($run)) {
        if($row['parentId'] > 1) {
            echo $row['staticTitle'];
            $result = file_list($row['parentId']);
            return $result; // INSERTED
        }else { 
            return $result; 
        }  
    }

I have a database structure like this:

 id | parentId | title
 3  |  1      | keyword
 28 |  3      | xxx
 31 | 28      | business

I want to output like this business -> xxx -> keyword

I want to run this function until $row['parentId'] = 1.
When I echo the title, I got correct result.
When I try it to store it in array, I always get single value.

How can I return an array in recursive array?

check this link http://stackoverflow.com/questions/8587341/recursive-function-to-generate-multidimensional-array-from-database-result — , Apr 15 '13 at 07:04

score 1 · Answer 1 · answered Feb 03 '18 at 13:48

I REALLY don't like the idea of recursive calls on the database. If this is a "breadcrumbs" database table, how big could it possibly be? ...I mean size is probably not a concern.

I would like to suggest that you pull the full table out (3 columns, all rows) and assign the resultset to a php variable. This means you only ever make one trip to the database -- and that's a good thing.

Code: (Demo)

function breadcrumber($array,$id){
    static $result=[];  // declare the storage variable without losing elements during recursion
    if(isset($array[$id])){  // if target exists
        $result[]=$array[$id]['title'];  // store title text
        $parent=$array[$id]['parentId'];  // assign new target
        unset($array[$id]);  // remove possibility of an infinite loop
        breadcrumber($array,$parent);  // recurse
    }
    return $result;
}

$resultset=[
    ['id'=>3,'parentId'=>1,'title'=>'keyword'],
    ['id'=>28,'parentId'=>3,'title'=>'xxx'],
    ['id'=>31,'parentId'=>28,'title'=>'business']
];

echo implode(' -> ',breadcrumber(array_column($resultset,NULL,'id'),31));
//                               ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^-assign associative keys for easy lookup

Output:

business -> xxx -> keyword

...and here is another demo using your second set of data

score 0 · Answer 2 · answered Apr 15 '13 at 08:36

Try this:

function file_list($path)
{
    $result = array();
    $q = "SELECT staticTitle,staticId,parentId FROM tbl_static_pages WHERE staticId = $path";
    $run = mysql_query($q);

    $results = array();
    while ($row = mysql_fetch_array($run))
    {
        if ($row['parentId'] > 1)
        {
            echo $row['staticTitle'];
            $results = array_merge($results, file_list($row['parentId']));
        }
        else
        { 
            $results[] = $result; 
        }
    }  
    return $results;
}

Few things that are not clear from your question:

Breadcrumbs are usually not recursive, so I suppose you do NOT want to actually return a recursive array (array where every element is again array). If you want, follow comment by @midhunjose
Do you expect the query to ever return multiple records?
Swap the order of array_merge arguments, if you want the parent before child in resulting array.

@user375947 Formatting is not preserved in comments, please update your question with the database structure. — Martin Prikryl, Apr 17 '13 at 06:38
See I have a database structure like this. staticId ParentId Title 3 1 Media 19 3 Files 28 19 Network 31 28 Business and I want breadcrumb like this Business -> Network -> files -> Media So i want to create a recursive function which runs until partentId reaches to 1 and titles in array. — user375947, Apr 17 '13 at 06:41

score -1 · Answer 3 · edited Apr 15 '13 at 07:24

-1

Try this:

$result[] = file_list($row['parentId']);

instand of

$result = file_list($row['parentId']);

edited Apr 15 '13 at 07:24

René Höhle

26,716
22
73
82

answered Apr 15 '13 at 07:04

thumber nirmal

1,639
3
16
27

How to generate breadcrumbs using recursion on database row data?

3 Answers3

Linked