Help with Query

[phprockskasbah]

I am working on the constraints of an existing database.

The way the products are set up when they have options such as color, size, etc makes it hard to query. These values are written always as option and value and there can be several per product order. So if I query them out, I get many rows. Is there any way to subroutine this or work around it?

The problem I have is that the last 2 columns can have anywhere from 1 to 4 entries causing the results to have 1-4 rows.

example...

SELECT
date_purch,
order_no,
customer,
email,
product_model,
product_price,
product_option,
product_option_value

FROM
ordertable

order by customer

returns

2006-09-20 | 00001 | customer [e-mail] | Model# | Opt Text | opt nameon tshirt
2006-09-20 | 00001 | customer [e-mail] | Model# | Opt Color | opt r 255 g 255 b 255
2006-09-20 | 00001 | customer [e-mail] | Model# | Opt 2nd Color | opt r0 g0 b0
2006-09-20 | 00001 | customer [e-mail] | Model# | Opt Comments | please ship to my house
2006-09-20 | 00002 | customer [e-mail] | Model# | Opt Text | opt nameon mug
2006-09-20 | 00002 | customer [e-mail] | Model# | Opt Color | opt r 150 g 57 b 125
2006-09-20 | 00002 | customer [e-mail] | Model# | Opt 2nd Color | opt r20 g130 b55
2006-09-20 | 00002 | customer [e-mail] | Model# | Opt Comments | gift for uncle ship to his house
2006-09-20 | 00003 | customer [e-mail] | Model# | Opt Text | opt nameonmousepad
2006-09-20 | 00003 | customer [e-mail] | Model# | Opt Color | opt r 150 g 57 b 125
2006-09-20 | 00003 | customer [e-mail] | Model# | Opt 2nd Color | opt r20 g130 b55
2006-09-20 | 00003 | customer [e-mail] | Model# | Opt Comments | can you use arial instead of times new roman

it would be cool if this could be cleaned up to look like

2006-09-20 | 00001 | customer [e-mail] | Model# | Opt Text | opt nameon tshirt | Opt Color | opt r 255 g 255 b 255 | Opt 2nd Color | opt r0 g0 b0 | Opt Comments | please ship to my house

2006-09-20 | 00002 | customer [e-mail] | Model# | Opt Text | opt nameon mug | Opt Color | opt r 150 g 57 b 125 | Opt 2nd Color | opt r20 g130 b55 | Opt Comments | gift for uncle ship to his house

2006-09-20 | 00003 | customer [e-mail] | Model# | Opt Text | opt nameonmousepad | Opt Color | opt r 150 g 57 b 125 | Opt 2nd Color | opt r20 g130 b55 | Opt Comments | can you use arial instead of times new roman

Any help would be greatly appreciated. I am going square eyed staring at it. 🙂

[phprockskasbah]

Is this a one to many or a many to many style relationship?

[phprockskasbah]

I really don't know anything... I am wondering if this needs a pivot like Excel does??