Joe Weinpert
asked on
Recursive BOM table search in a MS-SQL table via PHP
We have a BOM (Bill of Materials) table named BM_BillDetails. Within it are 2 fields named BillNo and ComponentItemCode.
We are trying to create a PHP program function to extract any top-level BOM and all of its sub-BOMs recursively and place the results into either an array or a temporary table.
In other words:
A top-level BOM contains several component items. Each of these contain sub-BOMs of their own in addition to just plain items. So we want to build a complete BOM array or temporary table until there are no more levels to find.
The database itself is a Microsoft SQL database running living on a Microsoft 2014 SQL Server. We access the database for many different reasons using simply PHP.
Thank you for your help.
We are trying to create a PHP program function to extract any top-level BOM and all of its sub-BOMs recursively and place the results into either an array or a temporary table.
In other words:
A top-level BOM contains several component items. Each of these contain sub-BOMs of their own in addition to just plain items. So we want to build a complete BOM array or temporary table until there are no more levels to find.
The database itself is a Microsoft SQL database running living on a Microsoft 2014 SQL Server. We access the database for many different reasons using simply PHP.
Thank you for your help.
post some data... I am confused with all those bom subbom billno componentid...
which one refers to what?
if you put your table(s) structure and some sample data, we can give you a single query and you dont need to write any single php code :)
which one refers to what?
if you put your table(s) structure and some sample data, we can give you a single query and you dont need to write any single php code :)
ASKER
The picture shows an example. You can see the table structure as well as a visual of how a BOM would be structured when recursively extracting the N01010 BillNo and all of its sub-BOMs.
check this
WITH BM_BillDetails as (
select 'N01010' BillNo, 'BACD0A' COmponentItemCode
union select 'BACD0A', '135032'
union select 'BACD0A', '700137'
union select 'BACD0A', '966104'
union select '700137', '210050'
union select '700137', 'A00600'
union select '210050', 'A00600'
union select '210050', 'AL0210'
),
DirectReports (BillNo, ComponentItemCode, Level) as (
-- Anchor member definition
SELECT b.BillNo, b.ComponentItemCode, 0 AS Level
FROM BM_BillDetails b
WHERE BillNo='N01010'
UNION ALL
-- Recursive member definition
SELECT b.BillNo, b.ComponentItemCode, Level + 1
FROM BM_BillDetails b
INNER JOIN DirectReports AS d
ON d.ComponentItemCode = b.BillNo
) select * from DirectReports order by 3,1
BillNo ComponentItemCode Level
N01010 BACD0A 0
BACD0A 135032 1
BACD0A 700137 1
BACD0A 966104 1
700137 210050 2
700137 A00600 2
210050 A00600 3
210050 AL0210 3ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Having the SLQ server use a CTE style recursive extract seems like a reasonable idea. Having the Apache PHP server use a recursive function call is more common. I will take the time to read your posted link of the CTE. Thanks for you input.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window
Can you provide some sample data and expected output.