SQL query not displaying the product in each sub category

Question

I am trying to display a product on each category starting from the top parent category all the way down to the category. I have my db setup like this:

CATEGORY TABLE:
id  |  parent_id   |  root_cat   |  name
5       37              0            bulbs
45      5               0            standard auxiliary
289     45              5            standard
297     289             5            5W

Now this is the products table table:

id    |   name    |   cat
470       bulb 5w      297

And this is happening, when i click on the bulbs category i see the product just fine, but when i click in the sub category standard auxiliary i do not see the product. But if i go another level to standard i can see the product again and also if i click on the 5W cat i also see the product. So the problem is only on the standard auxiliary category.

This is the method i am using to retrieve the data: $table_2 is the products table and $table is the category table.

public function getProductsWhereParentIdis($cat) {
        $check = $this->checkCategoryChildren($cat);
        if($check) {
            $query = "
            SELECT * 
            FROM categories 
            INNER JOIN products 
            ON products.category = categories.id 
            WHERE categories.id = '".$this->db->escape($cat)."' 
            OR categories.parent_id = '".$this->db->escape($cat)."' 
            OR categories.root_category = '".$this->db->escape($cat)."'";   

            return $this->db->fetchAll($query);
        } else {
            $query = "SELECT * FROM {$this->table_2} WHERE category = '".$this->db->escape($cat)."'";

            return $this->db->fetchAll($query);
        }


    }

and this method is just to check if a product has a parent:

public function checkCategoryChildren($id = null) {
        if(!empty($id)) {
            $query = "SELECT * FROM {$this->table} WHERE parent_id = '".$this->db->escape($id)."' ORDER BY view_order ASC";

            return $this->db->fetchAll($query);
        }
    }

and this is the fetchAll method:

public function query($query) {
        $this->last_query = $query;
        $result = mysqli_query($this->connection, $query);
        $this->confirm_query($result);
        return $result;
    }

    public function confirm_query($result) {
        if(!$result) {
            $output  = "Database query failed<br />";
            $output .= "Last SQL query: ". $this->last_query;
            die($output);
        } else {
            $this->affected_rows = mysqli_affected_rows($this->connection);
        }
    }

    public function fetchAll($query) {
        $result = $this->query($query);
        $output = array();
        while($row = mysqli_fetch_assoc($result)) {
            $output[] = $row;
        }
        mysqli_free_result($result);
        return $output;
    }

Really looking forward to anyones suggestion as to why i can see the product in all the categories but one, thank you.

Answer 1

Here is the solution i think you are looking for the main problem is you are only seeing category data which are only one level up or one level down

I have also mentioned some comments in the code kindly check it. And i also don't know how your fetchAll() function return the data. eg. object or array.

Edited: So in this code fetchAll() function return the data in array format because you have used mysqli_fetch_assoc()

public function getProductsWhereParentIdis($cat)
{
    $check = $this->checkCategories($cat);
    if (!empty($check)) {
        $query = "
        SELECT * 
        FROM categories 
        INNER JOIN products 
        ON products.category = categories.id 
        WHERE categories.id in (".(implode(",", $check)).")";
        return $this->db->fetchAll($query);
    } else {
        $query = "SELECT * FROM {$this->table_2} WHERE category = '".$this->db->escape($cat)."'";

        return $this->db->fetchAll($query);
    }
}

public function checkCategories($cat_id)
{
    $query = "SELECT * FROM {$this->table} WHERE id = '".$this->db->escape($cat_id)."' ORDER BY view_order ASC";
    $catObj = $this->db->fetchAll($query);
    $childs = $this->getChildCategoryIds($cat_id);
    $childs[] = $cat_id;
    // you can remove the parents from here if you don't want to get all
    // products which have parents categories associated in it.
    // eg. while user click on standard then if you don't want to get the products which
    // is assigned with bulbs or standard auxiliary then return only childs.
    $parents = $this->getParentCategoryIds($catObj[0]['parent_id']);
    return array_merge($childs, $parents);
}

public function getChildCategoryIds($cat_id)
{
    $cat_array = [];
    $q = "SELECT * FROM {$this->table} WHERE parent_id = '".$this->db->escape($cat_id)."' ORDER BY view_order ASC";
    $result = $this->db->fetchAll($q);
    foreach ($result as $value) {
        $tmp_array = $this->getChildCategoryIds($value['id']);
        $cat_array[] = $value['id'];
        if (!empty($tmp_array)) {
            $cat_array = array_merge($cat_array, $tmp_array);
        }
    }
    return $cat_array;
}

public function getParentCategoryIds($cat_id)
{
    $cat_array = [];
    $q = "SELECT * FROM {$this->table} WHERE id = '".$this->db->escape($cat_id)."' ORDER BY view_order ASC";
    $result = $this->db->fetchAll($q);
    if (!empty($result[0]['parent_id'])) {
        $tmp_array = $this->getParentCategoryIds($result[0]['parent_id']);
        $cat_array[] = $result[0]['parent_id'];
        if (!empty($tmp_array)) {
            $cat_array = array_merge($cat_array, $tmp_array);
        }
    }

    return $cat_array;
}

Answer 2

FWIW, I find this easier to read...

SELECT c.olumms
     , i.actually
     , w.ant
  FROM categories c
  JOIN products p
    ON p.category = c.id 
 WHERE :mystring IN (c.root_category,c.parent_id,c.id);