How to: one to many to some to many? In PHP

I am building an online store. The store has several categories (~10), the categories have several products (~10 each), the products have many sub-products(20+ each).

This is the part I am getting stuck on:

The sub-products have several columns of details. The products also have 10 headers.

This is what a product looks like:

http://www.ampedwebdesign.com/bear/new2/public/products/images/straightpins.jpg

the headers are the cells with dark background, and the sub-products are the rows.

This is part of my header class:

class Header extends DatabaseObject {

  protected static $table_name="headers";
  protected static $db_fields=array('id', 'product_id', 'col1', 'col2', 'col3', 'col4', 'col5', 'col6', 'col7', 'col8', 'col9', 'col0', 'col1', 'col2');

  public $id;
  public $product_id;
  public $col1;
  public $col2;
  public $col3;
  public $col4;
  public $col5;
  public $col6;
  public $col7;
  public $col8;
  public $col9;
  public $col10;
  public $col11;
  public $col12;

The product view calls the product class to show the image. The product view then calls the header class to return headers by product_id. The product view then calls the sub-products class to return the sub-products by product_id. The product view then calls the sub-products class to return the sub-product details in a foreach loop.

Any help would be appreciated, I hate writing code like this when I know there is a better way. I just don't know how to find it.

My DB tables: products, sub-products, categories, headers

Solution

The first step I usually take in solving a problem like this is to normalize the schema as much as possible. Looking at your example product, this is what jumps out at me:

Table items: columns number, price
Table attributes: columns id, title
Table items_attributes: columns item_id, attribute_id, value

Then, when querying the database:

   SELECT title, value
     FROM items_attributes
    WHERE item_id = 123
LEFT JOIN attributes
          ON items_attributes.attribute_id = attributes.id;

My MySQL is a bit rusty so the syntax may not be 100% correct but hopefully this'll help you in the right direction.

Edit:

As mentioned by rootatwc in the comments, the InnoDB storage engine has support for foreign keys, which allows you to do things like cascade UPDATEs and DELETEs so that you don't end up with a ton of orphaned data, and impose check constraints to help ensure that you don't get bogus/out-of-range data. Wikipedia has a fairly decent explanation of these mechanics.