Issue creating multiple join to generate categories and subcategories

Databases
1

Hello,

im trying to pull together a category and subcategory style setup using join but I cant seem to get the MySQL to work.
looking to do do 2 thing:
1 by example: technology
– computer
---- laptop
------and list what every under this category

  1. create a breadcrumb based on above results
    technology
    – computer
    ---- laptop

$dbArray1 = array(); //db-table query array
	Try
	{    $STM = $dbhandle->prepare("SELECT
				q1.cat_category AS parent,
				q2.cat_index ,
				q2.cat_category
				FROM tbl_categories as q1
		   LEFT JOIN tbl_categories as q2 ON q2.cat_parent = q1.cat_index
			   WHERE q2.cat_parent = $catid ");
				$STM->execute();
		while($row = $STM->fetch(PDO::FETCH_ASSOC))
		{   //Built with query in while loop
			$dbArray1[$row['parent']][$row['cat_index']] = $row['cat_category'];  			
		}
	}   catch (PDOException $e) { echo 'Connection failed1: ' . $e->getMessage(); }

cat_index | cat_parent | cat_category
1 0 Business
2 0 Pets
3 0 technology
4 3 computers
5 2 dogs, puppies for sale
6 4 printer
7 4 laptop
8 2 cats, kittens
9 3 tablet
10 2 birds, buy & sell

2

your query is fine except it goes down only one level, from the given starting category

aside: the WHERE clause should be changed, from

WHERE q2.cat_parent = $catid

to

WHERE q1.cat_index = $catid

to meet your original request, technology–computer–laptop, you have to go down to the third level, and to get categories under laptop you have to go down to the 4th

SELECT cat1.cat_category AS cat1_category
     , cat2.cat_index    AS cat2_index
     , cat2.cat_category AS cat2_category
     , cat3.cat_index    AS cat3_index
     , cat3.cat_category AS cat3_category
     , cat4.cat_index    AS cat4_index
     , cat4.cat_category AS cat4_category
  FROM tbl_categories as cat1  
LEFT OUTER
  JOIN tbl_categories as cat2 
    ON cat2.cat_parent = cat1.cat_index 
LEFT OUTER
  JOIN tbl_categories as cat3 
    ON cat3.cat_parent = cat2.cat_index 
LEFT OUTER
  JOIN tbl_categories as cat4 
    ON cat4.cat_parent = cat3.cat_index 
 WHERE cat1.cat_index = $catid
3

Thanks friend.