CODEWITHSUNDEEP
phpmysqlzen-cart
R.K.Bhardwaj
R.K.Bhardwaj

Reputation: 2192

Create a custom mysql JOIN query in Zen Cart

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.

Upvotes: 0

Views: 92

Answers (1)

Scott C Wilson
Scott C Wilson

Reputation: 20036

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;

Upvotes: 0

Related Questions