Table Join and Overall Design

Databases
1

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

2

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).

Pseudocode:


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]
    ELSE
        PRINT , row[image_name]
    ENDIF
   last_attribute = section_title
ENDFOREACH
3

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.