PHP/SQL Recursive Function - how to handle limited selects on mysqli while loop with sub selects

Question

I have a table with products. Sub-products can be assigned to a main product.

products
________________________________________________________________________
id  |   product_title   |   main_id     | owner_id 
1   |   Volvo           |       0       | 1     
2   |   Mercedes        |       0       | 2                     
3   |   Chevrolet       |       0       | 1                     

4   |   Rear lights     |       1       | 1                     
5   |   Glasses         |       1       | 1                     
6   |   Seats           |       1       | 1                     
7   |   Heater          |       1       | 1                     
8   |   Radio           |       6       | 1                     
12  |   Tyres           |       6       | 1 
13  |   Rearview mirror |       8       | 1 
14  |   Door            |       8       | 1 
15  |   Engine          |       14      | 1 
15  |   Door            |       3       | 1 

I use function get_the_list(id = 0, owner_id = 1);

    function get_the_list(id = 0, owner_id = 1) {
        
        $query = "SELECT * FROM products WHERE main_id = $id  AND owner_id = $owner_id";
        while ($row = mysqli_fetch_array($result, MYSQLI_BOTH)) {
            
            $list .= $row[product_title];
            
            // select sub products from main_id
            $list .= get_the_list($row[main_id], 1);
        }
     
    }
echo get_the_list(id = 0, owner_id = 1);

On this way I get the whole product list. Works well, no problems.

(1)   i: 1 --- loop: 1, 1 - Volvo
        i: 1 --- loop: 2, 2 - Rear lights

        i: 2 --- loop: 3, 2 - Glasses

        i: 3 --- loop: 4, 2 - Seats
        i: 1 --- loop: 5, 3 - Radio
        i: 1 --- loop: 6, 4 - Rear-view mirror

        i: 2 --- loop: 7, 4 - Door
        i: 1 --- loop: 8, 5 - Engine

        i: 2 --- loop: 7, 3 - Tyres

        i: 4 --- loop: 6, 2 - Heater

____________________________

(2)   i: 2 --- loop: 1, 1 - Chevrolet
        i: 1 --- loop: 2, 2 - Door

____________________________


(3)   i: 3 --- loop: 1, 1 - Mercedes

____________________________


(4)   i: 4 --- loop: 1, 1 - XX

____________________________

First (number) is main_id. Second number is running $i++ in while. Third number should be continuous/ongoing counter. BUT this breaks after level 5. fourth number after comma is level.

I have to limit a select statement to only 8 products (incl. sub products). So I will end, for example, with rear-view mirror on this image example. It works. But it works not after more than 8, because the counter breaks.

How can I limit the number of products that can be retrieved, what select statement should I choose? OR WHAT php workaround?

enter image description here

Answer 1

First of all consider moving to PDO instead of mysqli. Then set main_id nullable and add a foreign key for it as products.main_id -> products.id.

The modern way to handle this task is using CTE.

I'not using the owner_id here and I assume you only have two levels in your products tree:

WITH root_products AS (
    SELECT
        products.id, 
        products.main_id,
        products.product_title,
        products.id as sequence_route
   FROM products WHERE main_id IS NULL OR main_id = 0
   LIMIT 0, 20 -- this is where limit is set
)
SELECT * FROM (
    SELECT * FROM root_products
    UNION 
    SELECT 
        id, 
        main_id, 
        product_title,
        CONCAT( main_id, '.', id ) sequence_route
    FROM products 
        WHERE 
            main_id IN ( SELECT id FROM root_products )
) result 
ORDER BY sequence_route

Note that this solution is using CTE. Check if your DB server version supports those. Tested with MySQL 8+

If you need some simplier queries, you will need two of those:

$query = "SELECT * FROM products WHERE main_id = 0 OR main_id IS NULL LIMIT 0, 20";

This will fetch 20 top-level products. And:

$query = "SELECT * FROM products WHERE main_id IN( " . implode(',', $root_ids_you_take_from_the_prev_query ) . " )";

will get the subproducts.

Upd:

Here's the query for unlimited depth. Its is still tied to a root product with id = 1, you might want to leave this in place for performance reasons or rework the WHERE clause to main_id IS NULL at the first SELECT to grab all the root entries. This query implements 'way #2'. It is still all about an ORDER BY. If you need to roll back to a layer-by-layer 'way #1', just drop the ORDER BY clause and corresponding rank/recursion_level columns from CTE.

-- This CTE will take the given root and 
-- recursively grab all its descendants, 
-- layer after layer
WITH RECURSIVE deeper_products AS (
    SELECT
        0 as recursion_level                  -- This is required for ranking
        , CAST(id AS CHAR) as `rank`          -- This should be 'CAST(id AS FLOAT)' but i'm 2 lazy to update my mysql installation
                                              -- Server makes conversions internally just fine anyways

        , CAST(id as CHAR) as sequence_route  -- Just a visual helper, does nothing

        , id
        , main_id
        , product_title
    FROM products
        WHERE id = 1 -- the root product id
    UNION 
        SELECT    
            prev_layer.recursion_level + 1 as recursion_level
            , prev_layer.rank + products.id / POWER( 100, prev_layer.recursion_level + 1 ) as `rank` -- I assume there'r less than 100 children there
            , CONCAT( prev_layer.sequence_route, '.', products.id ) sequence_route

            , products.id 
            , products.main_id
            , products.product_title
            

        FROM products 
        INNER JOIN deeper_products prev_layer
            ON products.main_id = prev_layer.id
)


SELECT * FROM deeper_products
ORDER BY `rank` -- Remove this and the rank/recursion_level fields from CTE if you need a level-by-level ordering
LIMIT 0, 8      -- Your limit

Upd2: Here's a PHP solution. I'm using PDO here (messing with mysqli is a pain). No recursion needed, so the limit is calculated pretty simple:

<?php
$pdo = new PDO("mysql:dbname=test;host=localhost", 'test', 'SECRET_PASSWORD');

function get_list( $dbh, $pid = null ){

    $out = [];
    $limit = 8;

    // The regular query - items never have NULL as their main_id
    $regular_query = "SELECT * FROM products WHERE main_id = ? LIMIT 0, $limit";

    // The root query - items might have NULL as main_id
    $root_query = (
        is_null( $pid )
        ? "SELECT * FROM products WHERE main_id IS NULL LIMIT 0, $limit"
        : $regular_query
    );

    // Results stack contains statements
    // Every deeper level will add a new statement the top
    $root_sth = $dbh->prepare( $root_query );
    $root_sth->execute( [ $pid ] );
    $results_stack = [ $root_sth ];

    // Fill in the final result, loop untill the limit reached
    // or while we still have records in DB
    while( count( $out ) < $limit and count( $results_stack ) ){

        // Take the topmost statement..
        $sth = $results_stack[ count( $results_stack ) - 1 ];

        // .. and grap the row 
        $row = $sth->fetch(PDO::FETCH_BOTH);

        // The row is there, so we..
        if( $row ){
            // ..save it in the final result..
            $out[] = $row;

            // ..and add a new query returning children of a 
            // current item on the top of a stack
            $regular_sth = $dbh->prepare( $regular_query );
            $regular_sth->execute( [ $row['id'] ] );
            $results_stack[] = $regular_sth;
        }
        // No row - we'r out of data with this query, remove the it from the stack
        else{
            array_pop( $results_stack );
        }
    }

    return $out;

}

print_r( get_list( $pdo ) );

Upd3:

This builds the UL structure. I assume you've added the depth key for the items:

<?
$data = [
    [ 'title' => 'Item 1', 'depth' => 1 ],
    [ 'title' => 'Item 2', 'depth' => 1 ],
    [ 'title' => 'Item 2.1', 'depth' => 2 ],
    [ 'title' => 'Item 2.2', 'depth' => 2],
    [ 'title' => 'Item 2.2.1', 'depth' => 3 ],
    [ 'title' => 'Item 2.2.2', 'depth' => 3 ],
//  [ 'title' => 'Item 3', 'depth' => 1 ],
];
?>
<ul>
<?
foreach( $data as $i => $row ){

    ?><li><?=$row['title']?><?

    // If we even have a next level
    if( !empty( $data[$i + 1]) ){
        // If next item is on a level higher - open ul tag for it
        if( $data[$i + 1]['depth'] > $row['depth'] ){?>

            <ul>
        <?} 
        // If next item is a level lower - close li/ul tags between the levels and close higher li
        elseif( $data[$i + 1]['depth'] < $row['depth'] ){?>
            <?=str_repeat('</li></ul>', $row['depth'] - $data[$i + 1]['depth'] )?>
            </li>
        <?} 
        // Next item is on the same level, just close self li and go on
        else{?>
            </li>           
        <?}
    }
    // This is the last item, close li/ul to the end
    else{
        print str_repeat("</li></ul>", $row['depth'] );
    }
}?>