search data location wise in PHP SQL

Question

I have some parent and daughter design-wise locations-id in the MySQL database. Where the daughter linked to the parent. I will show the database design below - I can able to fetch the data when I search it through daughter location id wise but I don't have any idea how I combined the daughter value when I click parent location. For example -

MainLocation (123) //total stock 23+10+56= 89
 |
 |
 |---- DaughterLoc1 (456) //suppose stock 23
 |
 |---- DaughterLoc2 (789) //suppose stock 10 and total stock 10+56 = 66
        |
        |
        |---DaughterLocA (963) //suppose stock 56

SQL : SELECT stock FROM table WHERE location = '456'

OUTPUT = 23 (Corrent)

But I want when searching location 123 I want output 89

My table design is like this below - table: LocParent

-------------------------
| ID  | stock  | loc_id |
-------------------------
| 1   | 10     | 789    |
-------------------------


`location`

--------------------------------------------------------------------------------
| ID    | main_loc     | main_loc_id   | loc_under   |  loc_under_id | stock   |
--------------------------------------------------------------------------------
| 1     | MainLocation | 123           | DaughterLoc1 | 456          |  23     |
--------------------------------------------------------------------------------
| 2     | MainLocation | 123           | DaughterLoc2 | 789          |  10     |
--------------------------------------------------------------------------------

Answer 1

It is hard to tell from your sample structure what things actually look like still, and it is further complicated by multiple things called an "id". But, generally speaking, if your depth is finite, you can make small sub-queries, and if your depth is infinite (or unbound) you can make a recursive query.

Here is a sample database. It doesn't match yours, but hopefully it make sense still. If it doesn't, it would help if you provided an actual schema and data (excluding irrelevant columns).

This table is self-referencing to make things easier for demo.

CREATE TABLE sample
(
id int AUTO_INCREMENT NOT NULL,
parent_id INT NULL,
stock int NOT NULL,
PRIMARY KEY (`id`),
CONSTRAINT FOREIGN KEY (`parent_id`) REFERENCES `sample` (`id`)
);

And here's some sample data. There are two records that are "root" and don't have parent values (IDs 1 and 5), two child values (IDs 2 and 3) and one grandchild value (ID 4)

INSERT INTO sample VALUES (1, null, 11);
INSERT INTO sample VALUES (2, 1, 22);
INSERT INTO sample VALUES (3, 1, 33);
INSERT INTO sample VALUES (4, 2, 4);
INSERT INTO sample VALUES (5, null, 55);

Finite/bound

If you have a finite/bound depth, you can make use of subqueries like the below. This one goes to a depth of 3 and sums to 70. Hopefully it is fairly easy to read, but I've included a couple of comments.

SELECT
    s.id,
    s.stock -- root
    +
    (
        (
            SELECT
                SUM(c.stock) -- child
           FROM
                sample c
            WHERE
                c.parent_id = s.id
        )
        +
        (
            SELECT
                SUM(p.stock) -- grandchild
            FROM
                sample c
            JOIN
                sample p
            ON
                p.parent_id = c.id
            WHERE
                c.parent_id = s.id
        )
    )
    as three_level_sum
FROM
    sample s
WHERE
    s.id = 1;

Infinite/unbound

If you have an infinite hierarchy, however, things get more complicated. MySQL and other database platforms have a thing called "Common Table Expressions" (CTEs) that allow you to make recursive queries. These can be harder to wrap your head around because of the recursion, but it basically does the same as the previous version, just with infinite depth. This version also returns the sum of 70.

WITH RECURSIVE sample_rec AS
(
    SELECT
        id AS root_id,
        id,
        parent_id,
        stock
    FROM
        sample
    WHERE
        parent_id IS NULL
         
    UNION ALL
 
    SELECT
         R.root_id,
         E.id,
         E.parent_id,
         E.stock
    FROM
         sample E
    INNER JOIN
         sample_rec R
    ON
         E.parent_id = R.id
)
SELECT
    SUM(stock)
FROM
    sample_rec
WHERE
    root_id = 1