What is the best OOP approach to implement the best Category and Products relationship using PHP?

Question

I am trying to design a PHP project's structure. What I am not very sure about is how to implement categories and products relationship. A category has one parent, and can have many children. Product belongs to only one category. Db tables are like this:

products: id product_name category_id

categories id category_name parent_id

I created a class for products, a class for categories. But I do not know what is the best way to relate them in object oriented manner.

Should I have more classes like CategoryManager, if yes what should it look like ?

Anyone has good approach to implement relations in code level, not in db level.

Thanks in advance.

Answer 1

In my opinion, having methods like Product->getCategory() and Category->getProducts() would be enough.

Answer 2

I would do it like this

A product can have one Category but a Category can have may Products.

class products 
{
  public $productname;
  public $price;

public function getProduct($id)
{
  return $this->db->fetch("select * from Products where id={$id}");
} 


}


class Category extends Products
{
  public $category;

  public function __construct()
 {
  parent::__construct();
 }

 public function getCategoryProduct($id)
 {
  $this->getProduct($id);
 }

}

Answer 3

This is something i was trying to do last night for my Forums -> Topics relation. Procedural Programming is easy. Basically what you are trying to achieve is this:

Procedural Programming:

    function get_categories
    {
       //1. Perform database query for categories
       $sql_categories = "SELECT * FROM categories ORDER BY category_id ASC";
       $res_categories = mysqli_query($conn, $sql_categories);
       //2. Return result
       return $res_categories_classic; 
    }

    function get_products
    {
       //3. Get the returned data from the categories query
       $res_categories = get_categories();
       while ($array_categories = mysqli_fetch_assoc($res_categories))
       {
            //4. Perform database query for products
            $sql_forums = "SELECT * FROM products WHERE product_id = {$array_categories["category_id"]}";
            $res_forums = mysqli_query($conn, $sql_forums);
            //5. Display returned data from products
       }
    }

Now in OOP PHP this would depend on the framework if u use one, but in general:

        //1. Get all the Categories
        public function get_categories()
        {
           $this->db->select('*');
           $this->db->from('categories');
           $this->db->order_by('category_id', 'asc');
           $query = $this->db->get();
           return $query->result_array();
        {

        //2. Get all the related products
        public function get_products($category_id)
        {
           $products = $this->db->fetch("select * from Products where product_id={$category_id}");
           return $products;
        } 

The Category_ID can be supplied thru the URL as an Parameter by clicking a category's href ,basically with a framework it would be a piece of cake, because u have easy routing already set-up. Vanilla would be the same, but would look smt like this website/categories.php?category=1