PHP Recursive Find siblings

Question

working with this for a while.. cant get my head right.. sooo... help here ;-) It is quite simple I am sure..

Table:

CREATE TABLE IF NOT EXISTS `items` (
 `item_id` bigint(20) NOT NULL AUTO_INCREMENT,
 `item_parent_id` bigint(20) NOT NULL COMMENT 'itemid which this item belongs to',
 `item_name` varchar(255) NOT NULL,
 `item_serialnumber` varchar(255) NOT NULL,
 `item_model` varchar(255) NOT NULL,
  PRIMARY KEY (`item_id`),
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

I am trying to create an array of item_id and the item_id that it belongs to - via the item_parent_id - recursivly -

so that even if you find a child to a parent, check if the child is a parent to others.

Tried with something like this:

function get_item($item_id, $menu)
{
$sql = "
SELECT
    items.*,
    customers.*
FROM
    assets
LEFT JOIN item_customer_rel USING(item_id)
LEFT JOIN customers USING(customer_id)
WHERE
    items.item_parent_id = '".$parent."'
ORDER BY
    items.item_name
";
$res = mysqli_query($db, $sql) or die("ERROR: SQL Select a2a ancestor", $sql, mysqli_error($db) , $_SESSION["u_id"]);
while ($items = mysqli_fetch_assoc($res))
    $menu = build_ancestor_array($parent, $menu);
}


function build_ancestor_array($parent, $menu)
{
GLOBAL $db;
$sql = "
SELECT
    items.*,
    customers.*
FROM
    items
LEFT JOIN item_customer_rel USING(item_id)
LEFT JOIN customers USING(customer_id)
WHERE
    items.item_parent_id = '".$parent."'
";
$res = mysqli_query($db, $sql) or cc("ERROR: SQL Select a2a ancestor", $sql, mysqli_error($db) , $_SESSION["u_id"], $this_document);
while ($items = mysqli_fetch_assoc($res))
{
    if ($ancestor_item_array[$parent] == $items["item_id"])
        $menu = build_ancestor_array($parent, $menu);
    $ancestor_item_array[$parent] = $items["item_id"];

    // Creates entry into items array with current menu item id ie. $menu['items'][1]
    $menu['items'][$items['item_id']] = $items;
    $menu['items'][$items['item_id']]["connection_type"] = 2;
    // Creates entry into connected_to array. connected_to array contains a list of all items with connected_to
    $menu['connected_to'][$items['item_parent_id']][] = $items['item_id'];
}
return $menu;
} // end build item array

It only goes one "level" down.

score 0 · Answer 1 · edited May 23 '17 at 11:43

0

Refer, the 2 links below, I had recently posted answers for these, this is done in pure SQL

Recursive MySQL Query with relational innoDB

and

How to find all child rows in MySQL?

edited May 23 '17 at 11:43

Community

1
1

answered Apr 16 '13 at 19:05

Akash

4,956
11
42
70

I am trying to do it recursively with only some basic fetch data via mysql. the rest is php functions.. – osomanden Apr 16 '13 at 19:29

score 0 · Accepted Answer · answered Apr 16 '13 at 20:57

Recursive worked.. Just needed to try manually with pen and paper ;-)

function get_item_data($parent, $menu, $ancestor_item_array = "")
{
    GLOBAL $db;

    $sql = "
    SELECT
        items.*,
        customers.*
    FROM
        items
    LEFT JOIN item_customer_rel USING(item_id)
    LEFT JOIN customers USING(customer_id)
    WHERE
        items.item_parent_id = '".$parent."'
    ORDER BY
        items.item_name
    ";
    $res = mysqli_query($db, $sql) or cc("ERROR: SQL Select a2a ancestor", $sql, mysqli_error($db) , $_SESSION["u_id"], $this_document);
    while ($items = mysqli_fetch_assoc($res))
    {
        $ancestor_item_array[] = $items["item_id"];
        if (!in_array($items["item_parent_id"], $ancestor_item_array))
            $menu = get_item_data($items["item_id"], $menu, $ancestor_item_array);

        // Creates entry into items array with current menu item id ie. $menu['items'][1]
        $menu['items'][$items['item_id']] = $items;
        $menu['items'][$items['item_id']]["connection_type"] = 2;
        // Creates entry into connected_to array. connected_to array contains a list of all items with connected_to
        $menu['connected_to'][$items['item_parent_id']][] = $items['item_id'];
    }
}

score -1 · Answer 3 · answered Apr 16 '13 at 19:01

-1

It wont work on pure SQL.

You should take a look at stored procedures, the sql you're trying to make will only go 'inwards' one level because all the relations will be shown as if they're first level connections.

for example. parent->son->grandson->ggson

parent.item_parent_id = null
son.item_parent_id = parent
grandson.item_parent_id = son
ggson.item_parent_id = grandson

even tough grandson is a lower level connection, he will show up as a first level connection.

it cant be done with pure sql, sadly.. that's one of the reasons that made me go to NOSQL databases.

answered Apr 16 '13 at 19:01

Magus

2,905
28
36

You are incorrect. PostgreSQL is one of several databases that support recursive queries. – Robert K Apr 16 '13 at 19:06
I am trying to do it in a combination of SQL and code (recursive function), not pure sql.. and I see now that I didnt change the last function call - if ($ancestor_item_array[$parent] == $items["item_id"]) $menu = build_ancestor_array($parent, $menu); to if ($ancestor_item_array[$parent] == $items["item_id"]) $menu = get_item($parent, $menu); And I really feel better with some PHP code inbetween ;-) – osomanden Apr 16 '13 at 19:22

PHP Recursive Find siblings

3 Answers3