Table Join and Overall Design

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

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 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 = 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. :slight_smile:

You’re doing it right. That’s the right query. In your application you loop over these rows and only output the attribute name/paragraph when it’s different from the previous row (which you store in a variable to access in the next iteration of the loop).


VAR last_attribute;

FOR EACH row IN resultset:
    IF row[section_title] != last_attribute THEN
        PRINT row[section_title] <br /> - row[section_description] <br /> - row[image_name]
        PRINT , row[image_name]
   last_attribute = section_title

Awesome, that makes sense and it’s exactly what I was looking for. Thank you, Dan! After staring at it for so long yesterday I was convinced that I was approaching it the wrong way.