SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member centrex's Avatar
    Join Date
    Feb 2011
    Location
    San Diego
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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.
    Code:
    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:
    Code:
    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):
    PHP Code:
    $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.
    Code:
      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.

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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:

    Code:
    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. #3
    SitePoint Member centrex's Avatar
    Join Date
    Feb 2011
    Location
    San Diego
    Posts
    12
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •