Table Join and Overall Design

[Murdock]

Hi all,

 

First and foremost, I'm still quite new to relational database design and PHP in general so any thoughts, suggestions or input is greatly appreciated.

 

I'm working on setting up a product catalog with three tables: product, product_attributes and product_images which are setup like so.

product			product_attributes 		product_images	
-----------------|------------------------------|-------------------------
id			id				id
item_title		item_id				attribute_id
item_subtitle		section_title			image_name
item_description	section_description		
item_video		sort_order
item_category

 

A product can have as many attributes as necessary and each attribute can also have as many images as desired. The goal is to output something like this:

ITEM TITLE
Subtitle
Item overview/description

Attribute 1
- paragraph about attribute 1
- image1.1, image1.2, image1.3

Attribute 2
- paragraph about attribute 2
- image2.1

Attribute 3
- paragraph about attribute 3
- no images included

 

My limited knowledge produced the following query (I've just started using INNER JOIN):

$query = 'SELECT ... FROM product_attributes AS t1
  INNER JOIN product_images AS t2
  ON t1.id = t2.attribute_id
  WHERE t1.product_id = 1
  ORDER BY t1.sort_order';

 

this is what is returned.

  id       product_id       section_title       section_description       sort_order       image_name       sort_order
-------|----------------|-------------------|-------------------------|----------------|----------------|----------------
   1		1	    attribute 1         description of attr1		1	     image1.1		1
   1		1	    attribute 1		description of attr1		1	     image1.2		2
   1		1	    attribute 1		description of attr1		1	     image1.3		3
   2		1	    attribute 2		description of attr2		2	     image2		2
   3		1	    attribute 3		description of attr3		3	     NULL		NULL

 

Attribute 1 has three rows; one for each image. This presents a unique challenge for me as I am not sure if it's the most appropriate query (I imagine probably not) or how to loop through the results to achieve the desired outcome above.

 

Is there a better way to approach this and/or any suggestions on improving the overall table design? Thanks for your time.

[savister]

Looks good to me.  Just use control breaks to get the images.

 

[Murdock]

Excellent, thanks savister!  It didn't even occur to me to try something like that. I've only previously just looped through results and output them. This is what led me to believe I may have taken the wrong approach altogether.