Virik Virik - 2 months ago 23
MySQL Question

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

Answer

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

Comments