Help with explandable category tree

Posted on 16th Feb 2014 07:03 pm by admin

I have the below query:

SELECT l1.id as lev1_id,l2.id as lev2_id,l3.id as lev3_id,l1.category AS lev1, l2.category as lev2, l3.category as lev3
FROM categories AS l1
LEFT JOIN categories AS l2 ON l2.parentCat = l1.id
LEFT JOIN categories AS l3 ON l3.parentCat = l2.id
WHERE l1.parentCat = NULL


Which outputs something like:
-------------------------------------------------------
lev1_id | lev2_id | lev3_id |lev1 | lev2 | lev3 |
------------------------------------------------------
1 | 4 | 7 |makeup | lipstick | wet look
1 | 4 | 8 |makeup | lipstick |matt
1 | 5 | 9 |makeup | mascara| black
2 | 11 | NULL|skincare|creame | NULL
3 | 12 | 13 |haircare |hairspray| Xtra Hold

I loop through the results and list all the categories and subcategories as below.

$prevCat1Name = '';
$prevCat2Name = '';
foreach ($all_cats as $cats) {
if ($cats['lev1'] !== $prevCat1Name) {
echo $cats['lev1'];
}
if (($cats['lev2'] !== $prevCat2Name)&&($all_cat['lev2'] !== NULL)) {
echo ' - ' .$cats['lev2'];
}
if (($cats['lev3'] !== NULL)&&($all_cat['lev2'] !== NULL)) {
echo ' -- ' . $cats['lev3'] . PHP_EOL;
}

$prevCat1Name = $all_cat['lev1'];
$prevCat2Name = $all_cat['lev2'];
}

How do I amend the above code so that i can expand / contract the sublevels (without having seperate queries for each level)?

Your Answer:

Login to answer

Other forums

Set Categories
I am creating a submission form for somebody to come in and submit products. These products are sep

Apple's revamped lineup arrives

I'm personally curious about the magic mouse.. a multi-touch concept sounds like it could be nea

Just a white page
Okay so, my website, when I click SignUp on it it takes me to /join.php but its a complete white pag

Cursor statement failed with errors
Following statement failed with the errors.

cursor proj_cursor1 is (select projectID from

Downloading file (Headers)
I'm trying to make users download a file, but they must wait 60 seconds before it begins.

But

comparing tables across databases sql refinement ideas required
Hi all

Via pl/sql I need to ensure that data between tables in different databases match

problem with query error
First Thanks to those who helped me on my previous posts, and the following code i'm using is not mi

Final year project, please point me in the right direction
hi there for my final year project I'm aiming to build a php script shell to use as an expert system

Pass sql into pl/sql and create RMAN duplicate script.
Hi,

I'm new to pl/sql and I'm trying to write a script that will generate some RMAN comma

Problem displaying "scraped" XML data
I'm basicly having trouble displaying XML data scraped from an URL using cURL.

What the code