CODEWITHSUNDEEP
phpmysqlarraysloops
Virik
Virik

Reputation: 407

PHP breadcrumbs loop

I'm stuck with a small PHP loop.

My query returns 10 rows, with columns 'id' and 'parentId'. I'm trying to create an array of parent ids to use for breadcrumbs on a site.

Current page ID is '6'. It's parent is '5', which again has page '3' as parent. Page '3' has '0' as parent... Array should then be 5, 3, 0.

$stmt = $db->query("
    SELECT id, parentId
    FROM cat
");
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

function crumbs($rows, $id = '6') {
    $crumbs = array();
    foreach ($rows as $row) {
        if ($row['id'] == $id) {
            if ($row['parentId'] != 0) {
                crumbs($rows, $row['parentId']);
            }
            $crumbs[] = $row['parentId'];
        }
    }
    // echo $crumbs returns Array, Array, Array...
    return $crumbs;
}

$json['crumbs'][] = crumbs($rows);
// ...but $json['crumbs'] return only one Array (5).

echo json_encode($json);

Edit: MySql Rows:

0   0
1   0
2   0
3   0
4   3
5   3
6   5
7   7
9   2
12  3

And a SQLFiddle

Upvotes: 0

Views: 395

Answers (1)

grepsedawk
grepsedawk

Reputation: 3411

Here is a solution that will use pure MySQL to get you all parent child relationships off of 1 parent:

Query:

SELECT  @r AS _id,
         (
         SELECT  @r := parent
         FROM    nav
         WHERE   id = _id
         ) AS parent,
         @l := @l + 1 AS level
 FROM    (
         SELECT  @r := 6,
                 @l := 0
         ) vars,
         nav h
WHERE    @r <> 0

Results:

| _id | parent | level |
|-----|--------|-------|
|   6 |      5 |     1 |
|   5 |      3 |     2 |
|   3 |      0 |     3 |

http://sqlfiddle.com/#!9/89845a/8

Upvotes: 1

Related Questions