Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

Combine 3 tables

I have three tables I'm trying to combine

products which contains:
product_id, name, price
1, test1, 1.00
2, test2, 1.00
3, test3, 1.00

product_images which contains:
product_id, file_path, upload_number
2, testing.jpg, 1
2, testing2.jpg, 2
3, testing4.jpg, 1
3, testing4.jpg, 2
3, testing4.jpg, 3

product_image_order which contains:
product_id, order_number
2, 1
2, 2
3, 3
3, 1
3, 2

basically.. im trying to grab all of products and attach the product_images and product_image_order by matching the product_id together

i have the following code so far.. but not working =(
$this->db->select($this->product_table_name . '.product_token, '
					. $this->product_table_name  . '.status, '
					. $this->product_table_name  . '.price, '
					. ' GROUP_CONCAT(DISTINCT ' .  $this->product_image_table_name . '.base_filename) AS base_filename', false);
$this->db->from($this->product_table_name);
$this->db->join($this->product_image_table_name, $this->product_table_name . '.product_token = ' . $this->product_image_table_name . '.product_token', 'right');
$this->db->where($this->product_table_name . '.seller_id', (int) $seller_id);
//$this->db->limit($limit, $offset);
//$this->db->order_by($sort_by, $sort_order);

Open in new window

0
dsrnu
Asked:
dsrnu
  • 3
  • 2
  • 2
3 Solutions
 
Scott MadeiraCommented:
I think you have a flawed table structure...

The link between products and images is good with the product_id field in the images table.  What you also need in the product_images table is an image_id so that you can use that to link to the image order table.  The relationship is between image_order and images, not products.

so, add an image_id field to product_images and have a query like this:


 
select 
	products.name,
	products.price,
	product_images.file_path,
	product_image_order.order number
from
	products
	left join product_images on (products.product_id = product_images.product_id)
	left join product_images_order on (	product_images.image_id = product_image_order.image_id)

order by
	products.name,
	product_image_order.order_number

Open in new window


You may also want to consider combining the last two tables by adding an order column to product_images since the third table doesn't do anything but hold an order number and there is a one to one relationship (I think) between that table and the product_images table.

You will need to check syntax.  I didn't have anything to test with.
0
 
Scott MadeiraCommented:
One other thing.  in the third table you will want product_image_id and not product_id if you choose to keep it as three tables.
0
 
dsrnuAuthor Commented:
smadeira.. thanks.. but for some reason, it breaks apart when i try to use

GROUP_CONCAT(DISTINCT on product_images.file_path.. any ideas?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Ray PaseurCommented:
I recommend that you combine product_images with product_image_order so you can use ORDER BY on a single table.  I think this will make your code simpler.  

This has some links to interesting philosophical readings on the issue of table design.
http://lmgtfy.com?q=Should+I+Normalize+my+Database
0
 
dsrnuAuthor Commented:
so far, i get the following (ive removed the order of the images for now)

finding a really difficult time grouping the base_filename together whenever it belongs to the same product_token (or product_id)
Array
(
    [0] => stdClass Object
        (
            [product_token] => 3d6b30e93a9a9416d3ff04dcb8ed3e85
            [status] => active
            [price] => 2.0000
            [base_filename] => 
        )

    [1] => stdClass Object
        (
            [product_token] => 355b6a8da1634097112aa52396f0b736
            [status] => hidden
            [price] => 20.0000
            [base_filename] => 7f876a48780a1205eede59825c75832a
        )

    [2] => stdClass Object
        (
            [product_token] => 71d232ecb4a3a70a1f0999c4ba889f55
            [status] => comingsoon
            [price] => 0.0000
            [base_filename] => 6c2d841c81238ad708f0806efa10af48
        )

    [3] => stdClass Object
        (
            [product_token] => 71d232ecb4a3a70a1f0999c4ba889f55
            [status] => comingsoon
            [price] => 0.0000
            [base_filename] => 7aa445a75f9e9fc4a5e7648b3e6c072d
        )

)

Open in new window

0
 
dsrnuAuthor Commented:
the solution was to use group by! thanks both of you for helping!

@ray: yea i noticed the duplication there.. thanks!
0
 
Ray PaseurCommented:
Thanks for the points -- it's an interesting DB design question!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now