Create a custom mysql JOIN query in Zen Cart

Question

I am trying to the product's quantity and what attribute name was selected by the user. For example, what attribute name was chosen for product id = 180 where the order id = 2118 ?

any suggestion how I can right mysql join query for retrieve order data.

Answer 1

The way to handle problems like this is to break the query down into steps.

In Zen Cart, the attributes details for an order are stored in the table orders_products_attributes. (NOTE: I am assuming your tables have the prefix zen_. If they don't, just remove this from the queries below.) So in phpMyAdmin, you could use:

SELECT * FROMzen_orders_products_attributeswhere orders_id = 2118

This gives you all attributes but instead of a product id, you get an orders_products_id. So to map that back to a product_id, check the orders_products table.

SELECT * FROMzen_orders_productswhere orders_id = 2118

So now you can see that both tables contain orders_products_id and orders_id. So use those two fields in your query. I'm going to guess that the fields products_options and products_options_values have the information you need.

SELECT products_options, products_options_values FROM zen_orders_products op, zen_orders_products_attributes opa WHERE op.orders_id = opa.orders_id AND op.orders_id = 2118 AND op.products_id=180;