Report Query MySQL - Help Needed

[ksoup]

Database: MySQL 5.0.45

need help with an "inline view" of the product attributes on a regular mysql query.

The issue with the following query is that it will only return the first product attribute. We have more than one product attribute and would like to see them returned in the row (out to the right).

Example...

we want to just run a back end query with the following information and the attributes in the actual row for the orders. size, color and image are all in zen_orders_products_attributes.products_options_values and our query only allows for one of those to be returned.... there has to be a way to subquery this to get it all in the one line.

qty | orderdate | orderno | customer | email | model | product | price | finalprice | *** need this.... size | color | image | ***

qty | orderdate | orderno | customer | email | model | product | price | finalprice | *** need this.... size | color | image | ***

qty | orderdate | orderno | customer | email | model | product | price | finalprice | *** need this.... size | color | image | ***

Please help.


******* query **********
SELECT
zen_orders_products.products_quantity as qty,
zen_orders.date_purchased as orderdate,
zen_orders.orders_id as orderno,
zen_orders.customers_name as customer,
zen_customers.customers_email_address as email,
zen_orders_products.products_model as model,
zen_orders_products.products_name as product,
zen_orders_products.products_price as price,
zen_orders_products.final_price as final,
zen_orders_products_attributes.products_options as opt,
zen_orders_products_attributes.products_options_values as value


FROM
zen_orders
inner join zen_orders_products on zen_orders_products.orders_id=zen_orders.orders_id
inner join zen_orders_total on zen_orders_total.orders_id=zen_orders_products.orders_id
inner join zen_orders_products_attributes on zen_orders_products_attributes.orders_products_id=zen_orders_products.orders_pro ducts_id
inner join zen_customers on zen_orders.customers_id=zen_customers.customers_id
where (zen_orders_products.products_model like '%model%' and zen_orders_products_attributes.products_options_values like '%size%' and zen_orders.orders_status>2)
group by zen_orders.orders_id
order by zen_orders.customers_name

[kuroi]

I suspect that you're only getting one attribute result per order because you're using inner joins. If you used outer joins you'd get all the attributes but see the rest of the order duplicated for each attribute.

To avoid that your would need to use left join products_attributes table to itself (using different aliases each time) as many times as there attributes to be retrieved for each product and then concatenate the results. If the products have a variable number of attributes, then it becomes damned near impossible.

If that sounds difficult, it's because it is. In these situations I usually take the easier path of using SQL to pull out all the data with duplicate orders and then use a while loop to decant the results into an associative array in PHP to get the sort of structure that you want before outputting them - array structures are much more flexible than database tables.

[ksoup]

I understood what you said... but didn't know the syntax well enough to get it to work. With some help, this is what we came up with... The pitfall with this is... it is specific and it won't include anything that didn't get that attribute associated with the order.

Is there a way to tweak something like this to pull any and all attributes rather than being so specific? :)

SELECT
zop.products_quantity as qty,
zo.date_purchased as orderdate,
zo.orders_id as orderno,
zo.customers_name as customer,
(select zc.customers_email_address from zen_customers zc where zc.customers_id = zo.customers_id) as email,
zop.products_model as model,
zop.products_name as class,
zop.products_price as price,
zop.final_price as final,
zopa_term.products_options_values as term,
zopa_user.products_options_values as dsp

FROM
zen_orders_products_attributes as zopa_user,
zen_orders_products_attributes as zopa_term,
zen_orders as zo,
zen_orders_products as zop

where

zop.products_model like '%model%'
and zo.orders_status>2
and zop.orders_id=zo.orders_id
and zopa_term.orders_products_id=zop.orders_products_id
and zopa_user.orders_products_id=zop.orders_products_id
and zopa_term.products_options='Term'
and zopa_user.products_options='User'
and zopa_term.products_options_values like '%January 2008%'

order by zo.customers_name

I suspect that you're only getting one attribute result per order because you're using inner joins. If you used outer joins you'd get all the attributes but see the rest of the order duplicated for each attribute.

To avoid that your would need to use left join products_attributes table to itself (using different aliases each time) as many times as there attributes to be retrieved for each product and then concatenate the results. If the products have a variable number of attributes, then it becomes damned near impossible.

If that sounds difficult, it's because it is. In these situations I usually take the easier path of using SQL to pull out all the data with duplicate orders and then use a while loop to decant the results into an associative array in PHP to get the sort of structure that you want before outputting them - array structures are much more flexible than database tables.