PHP breadcrumbs loop

Question

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:

And a SQLFiddle

The better solution would actually be to get MySQL to return exactly what you want. — grepsedawk
– grepsedawk, Commented Sep 10, 2016 at 16:45
Can you please provide 10 rows of data, and desired results. This looks like it could be a very simple MySQL query without the whole loop. — grepsedawk
– grepsedawk, Commented Sep 10, 2016 at 16:46
Create a sqlfiddle and perhaps @Pachonk is up for doing a self-join. Right now you could support your question better with a fiddle. Otherwise apathy can set in for the answer people — Drew
– Drew, Commented Sep 10, 2016 at 16:55
sqlfiddle.com you can use text-to-ddl to really speed up creation time. — grepsedawk
– grepsedawk, Commented Sep 10, 2016 at 16:57

grepsedawk · Accepted Answer · 2016-09-10 17:16:33Z

1

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

answered Sep 10, 2016 at 17:16

grepsedawk

3,4142 gold badges28 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Virik Over a year ago

Amazing. Screw php :) Thank you!

grepsedawk Over a year ago

For the record, most of that type of messing around can be saved on the mysql side, but there is a line that must be drawn when it comes to time and effort.

grepsedawk Over a year ago

The one thing you want to be careful with is using too many variables and using loops. When queries get complex, make them stored procs (they're like functions that php can call and pass in args)

Collectives™ on Stack Overflow

PHP breadcrumbs loop

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related