PHP Category Path / Breakcrumb Generator

[experience40]

I'm stuck on trying to generate the full Category Path / Breadcrumb path for a category including its parent categories e.g. Cars / Sports Cars / Petrol / 2 Door 

I have a category ID such as 123 for "2 Door" in the path example above, which is sent to the function below in hope of getting the full category path including parent categories. 

function retrieve_category_path($id){
	
	 // DB connection
	require("../../config/db/connection.php");
	// Company ID
	$company_id = 1	;
	
	$stmt = $conn->prepare("SELECT id, name, parent, position FROM category WHERE company_id = ? AND id = ?");
	$stmt->bind_param("ii", $company_id, $id);
	$stmt->execute();
	$result = $stmt->get_result();
	
	$category_path_array = [];
			while($row=$result->fetch_assoc()){				
					$parent =$row['parent'];
					$name = $row['name'];
					$position = $row['position'];				
					$position_seperator = '::';
					$seperator = '/';		
					
					$category_path_array = [$name, $position_seperator, $position, $seperator];
						if($parent > 0){			
						retrieve_category_path($parent);						
						}		
			}
			
  // return $category_path_array;
   var_dump($category_path_array);
}

This creates an array:

Quote

array(4) { [0]=> string(9) "Cars" [1]=> string(2) "::" [2]=> int(2) [3]=> string(1) "/" } array(4) { [0]=> string(15) "Sports Cars" [1]=> string(2) "::" [2]=> int(1) [3]=> string(1) "/" } array(4) { [0]=> string(6) "Petrol" [1]=> string(2) "::" [2]=> int(1) [3]=> string(1) "/" } array(4) { [0]=> string(10) "2 Door" [1]=> string(2) "::" [2]=> int(0) [3]=> string(1) "/" }

which when imploded, the array has split to 4 seperate array rows:

Quote

Cars::2/
Sports Cars::1/
Petrol::1/
2 Door::0/

However i want the rows combined into a single row such as:

Quote

Cars::2/Sports Cars::1/Petrol::1/2 Door::0/

ideally with the end forward slash removed to

Quote

Cars::2/Sports Cars::1/Petrol::1/2 Door::0

Can anyone please advise where i'm going wrong?

Many thanks 

[Barand]

43 minutes ago, experience40 said:

var_dump($category_path_array);

Instead of the above final line, can you post something processable such as

var_export($category_path_array)

Or, better still, a dump of your test data (struture and data) so we can reproduce the array ourselves.

(The more you help us the more we can help you)

[experience40]

31 minutes ago, Barand said:

Instead of the above final line, can you post something processable such as

var_export($category_path_array)

 

Quote

array ( 0 => 'Cars', 1 => '::', 2 => 2, 3 => '/', )array ( 0 => 'Sports Cars', 1 => '::', 2 => 1, 3 => '/', )array ( 0 => 'Petrol', 1 => '::', 2 => 1, 3 => '/', )array ( 0 => '2 Door', 1 => '::', 2 => 0, 3 => '/', )

 

SQL DB Category Table

Basically you will call the function with category id (retrieve_category_path($id) and it will loop through the database returning the category data until the parent id = 0 (ID for top Parent = 0, Parent ID > 0 = Child)

Just looking to combine the returned arrays into a single array (Parent/Child/Child/Child) rather than (Parent/) (Child/) (Child/) (Child/)

Edited May 21 by experience40
Image

[Barand]

After calling your recursive array you need finish up with a single array - you output a separate array during each iteration.

Don't connect to db inside functions - it's the slowest part of the process and inefficient (plus you can quickly reach your connctions limit). Connect once then pass the connection to the functions.

Don't include the separator in you results - add that when you implode (join).

(Sorry for any delay - it's not easy to load an image of data into a database test table)

My method...

function retrieve_category_path($pdo, $id, &$cpa){
    
    // DB connection
    // Company ID
    
    $stmt = $pdo->prepare("SELECT CONCAT(name, '::', position) as name
                                , parent
                           FROM category WHERE id = ?
                           ");
    $stmt->execute([$id]);
    
    while($row=$stmt->fetch()){                
            $parent =$row['parent'];
            $name = $row['name'];
            
            $cpa[] = $row['name'];       // append into array
            
            if($row['parent'] > 0){            
                retrieve_category_path($pdo, $row['parent'], $cpa);                        
            }        
    }
            
  
}

$category_path_array = [];
retrieve_category_path($pdo, 552, $category_path_array);
$breadcrumbs = join('/', array_reverse($category_path_array));

echo $breadcrumbs;  // Cars::2/Sports cars::1/Petrol::1/2 Door::0 

 

[experience40]

Slightly different to how i thought it would be done, but with a bit of tweaking it works  

Why is there an ampersand on the function call "function retrieve_category_path($pdo, $id, &$cpa)" ?

Also what purpose does the $cpa serve below, is it sending the "$cpa[] = $row['name']" array data back round to the function call for the next loop, i.e. is the built array data from the 1st loop sent to the function call to be included on each following loop?

Quote

if($row['parent'] > 0){
retrieve_category_path($pdo, $row['parent'], $cpa);

As for not including DB connection inside functions, i'll have to look into how to do this as i'm not aware of any other way to do it!
I'm picking up PHP as i go along, i've picked it up on this method from code examples which has stuck with me as standard practice so i'll have to figure out how to do it more efficiently 

Many thanks for your help, i've sent you a small donation for a drink 

 

[Barand]

2 minutes ago, experience40 said:

Why is there an ampersand on the function call "function retrieve_category_path($pdo, $id, &$cpa)" ?

We want to add a new element to the $cpa array in each call to the function. To do this we need always to add to the original empty array. The ampersand allows us to to this. Without it, a copy of the array would be passed to the function and we would just keep adding to a new empty array each time. &$cpa passes the array by reference (ie its address in memory) instead of a copy.

P.S.

This method below (which stores all the category data into a $cat_data array  instead of running a query in every call to the function, is 5x faster. $cat_data looks like this...

Array
(
    [532] => Array
        (
            [name] => Motorbikes::1
            [parent] => 0
        )

    [533] => Array
        (
            [name] => Cars::2
            [parent] => 0
        )

    [534] => Array
        (
            [name] => Boats::3
            [parent] => 0
        )

    [535] => Array
        (
            [name] => Bicycles::4
            [parent] => 0
        )
	.
	.
	.
)

CODE

    $cat_data = [];
    $res = $pdo->query("SELECT id
                            , CONCAT(name, '::', position) as name
                            , parent
                       FROM category
                       ");
    foreach ($res as $r)  {
        $cat_data[$r['id']] = [ 'name' => $r['name'], 'parent' => $r['parent'] ] ;
    }

    $category_path_array = [];
    retrieve_category_path ($cat_data, 552, $category_path_array);
    $breadcrumbs = join('/', $category_path_array);
    
    echo $breadcrumbs; 

function retrieve_category_path (&$cats, $id, &$cpa)
{
    array_unshift($cpa,$cats[$id]['name']);
    if ($cats[$id]['parent']) {
        retrieve_category_path($cats, $cats[$id]['parent'], $cpa);
    }
}

 

14 minutes ago, experience40 said:

Many thanks for your help, i've sent you a small donation for a drink 

Thank you - much appreciated.

[experience40]

Superb, thanks again 

[Barand]

FYI
Here is a simpler non-recursive solution...

    $cat_data = [];
    $res = $pdo->query("SELECT id
                            , CONCAT(name, '::', position) as name
                            , parent
                       FROM category
                       ");
    foreach ($res as $r)  {
        $cat_data[$r['id']] = [ 'name' => $r['name'], 'parent' => $r['parent'] ] ;
    }

    $breadcrumbs = retrieve_category_path(552, $cat_data);
    echo $breadcrumbs;
    

    
function retrieve_category_path($id, &$cats)
{
    if (!isset($cats[$id])) {
        return "Unknown category";
    }
    
    $category_path_array = [];
    do {
        array_unshift($category_path_array, $cats[$id]['name']);
        $id = $cats[$id]['parent'];
    } while ($id);
    
    return join(' / ', $category_path_array);
}