Solved

Combine 3 tables

Posted on 2011-09-24
8
258 Views
Last Modified: 2012-05-12
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
Comment
Question by:dsrnu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
8 Comments
 
LVL 14

Accepted Solution

by:
Scott Madeira earned 334 total points
ID: 36593784
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
 
LVL 14

Assisted Solution

by:Scott Madeira
Scott Madeira earned 334 total points
ID: 36593788
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
 

Author Comment

by:dsrnu
ID: 36593904
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 110

Assisted Solution

by:Asked Anonymously
Asked Anonymously earned 166 total points
ID: 36593908
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
 

Author Comment

by:dsrnu
ID: 36593934
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
 

Author Comment

by:dsrnu
ID: 36593952
the solution was to use group by! thanks both of you for helping!

@ray: yea i noticed the duplication there.. thanks!
0
 
LVL 110

Expert Comment

by:Asked Anonymously
ID: 36593996
Thanks for the points -- it's an interesting DB design question!
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question