Geek asks and answers

A very complex query that returns categories if products are associated with a category

I have the following function that returns the ul menu with product categories only if at least 1 product belongs to the category. The function is as follows:

function getProductCategorieshome() {

    $query = 'select id, 
                     category, 
                     title 
              from products_categories 
              where visible="1" and 
                    parent="0" 
              group by category 
              order by category ASC';

    $result = mysql_query($query) or 
                die('Mysql Error:'.mysql_error().'<br /> Query:'.$query);

    $num_rows = mysql_num_rows($result);

    if($num_rows){
        echo '<ul id="menu" style="list-style:none;">';
        $htm = '';

        for($i=0; $i<$num_rows; $i++) {
            $row = mysql_fetch_row($result);

            //sub category
            $query = 'select pc.id, 
                             category, 
                             pc.title, 
                             p.id, 
                             p.new 
                      from products_categories pc,
                           products_to_categories ptc, 
                           products p 
                      where visible="1" and 
                            parent="'.$row[0].'" and 
                            pc.id=ptc.category_id and 
                            p.id=ptc.product_id and 
                            p.new="1" and 
                            ( expire_date>now() or expire_date=0) 
                      group by category 
                      order by category ASC';

            $result1 = mysql_query($query) or 
                         die('Mysql Error:'.mysql_error().'<br /> Query:'.$query);

            $num_rows1 = mysql_num_rows($result1);

            $q = 'select pc.id, 
                         category, 
                         pc.title, 
                         p.id, 
                         p.new 
                  from products_categories pc, 
                       products_to_categories ptc, 
                       products p 
                  where pc.id="'.$row[0].'" and 
                        pc.id=ptc.category_id and 
                        p.id=ptc.product_id and 
                        p.new="1" and 
                        ( expire_date>now() or expire_date=0) 
                  group by category 
                  order by category ASC';

            $r = mysql_query($q) or 
                    die('Mysql Error:'.mysql_error().'<br /> Query:'.$q); 

            $num_rows2 = mysql_num_rows($r);

            if($num_rows1>0) {
                $sub_htm='';
                for($j=0; $j<$num_rows1; $j++){
                    $row1 = mysql_fetch_row($result1);
                    $sub_htm .= '<li style="list-style:none;text-align:left;">
                                 <a href="./shop/index.php?offers='.$row1[0].'" title="'.$row1[2].'">'.$row1[1].'</a></li>';
                }
                if(!empty($sub_htm)) {
                    $htm .= '<li style="list-style:none;text-align:left;">
                            <a href="./shop/index.php?offers='.$row[0].'" title="'.$row[2].'">'.$row[1].'</a><ul>'.$sub_htm.'</ul></li>';
                }
            } else {
                for($s=0; $s<$num_rows2; $s++){
                    $rr = mysql_fetch_row($r);
                    $htm .= '<li style="margin:0px;padding:0px;text-align:left;">
                    <a href = "./shop/index.php?offers='.$rr[0].'" title="'.$rr[2].'">'.$rr[1].'</a></li>';
                }
            }
        }

        echo $htm;
        echo '</ul>';
    }
}

This function returns the correct categories if they have a depth of 2 levels - so we have a parent category and a level 1 category, BUT now I need to have an unlimited category depth, and the function should loop, maybe again or ??? Please help me solve this. And I know mysql is deprecated :)

+3
source share
1 answer

The simplest solution would be to select the DISTINCT categories from products_categories:

SELECT DISTINCT category FROM products_categories WHERE visible = 1

db node node

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `treePath`(in category_id int)
BEGIN
DECLARE parent int;
DECLARE current int;
SET @parent := NULL;
SET @current := category_id;
CREATE TEMPORARY TABLE tree_path_tmp(id int);
-- TRUNCATE tree_path_tmp;
WHILE @current DO
    INSERT INTO tree_path_tmp SELECT @parent := parent_id FROM categories WHERE id = @current;
            SET @current := @parent;

END WHILE;
    SELECT * FROM tree_path_tmp;
END$$

DELIMITER ;

, proc , ,

CALL treePath($categoryThatHasVisibleProducts_id);

... !:) .

btw . .

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

0

More articles:


All Articles