php - Get unique data from multiple tables week by week -

i have hard time dealing multiple tables. want total number of products orders placed week week. used following code:

$sql = "select  id_order, order_placed orders                               year(order_placed) = year(now())          , weekofyear(order_placed) = weekofyear(now())"; 

the above query return orders placed current week. added new sql query product ids retrieved id orders.

$sql2 = "select id_product, count(id_product) num_products           order_product           id_order = '".$id_order."'"; $results2 = db::getinstance()->executes($sql2); 

in same way rest info. when print final statement like:

order id: 1, date: 06/07/2015, product: 100052, name: example order id: 1, date: 06/07/2015, product: 100052, name: example order id: 2, date: 07/07/2015, product: 9987,   name: example b 

but want find way show total number:

order id: 1, date: 06/07/2015, product: 100052, name: example a, count: 2 order id: 2, date: 07/07/2015, product: 9987,   name: example b, count: 1 

also group them week each line contain info current week.

below can see tables idea (i simplify them lot in order easier you)

orders

----------------------------------- | order id |  date                 | ----------------------------------- | 1        |  06/07/2015 15:20:01  | ----------------------------------- | 2        |  07/07/2015 10:10:41  | ----------------------------------- 

order_products

    ------------------------------------------     | order id |  product id  | product type |     ------------------------------------------     | 1        |  10052       |  1           |     ------------------------------------------     | 1        |  10052       |  1           |     ------------------------------------------     | 2        |  9987        |  0           |     ------------------------------------------ 

a customer can order many products wants.. why above table contain 10052 id twice. customer example ordered 10052 twice. also, want type '1' retrieved.

product_id_name

------------------------ | product id | id name | ------------------------ | 10052      |  1      | ------------------------ | 9987       |  2      | ------------------------ 

product name

-------------------------- | id name | product name | -------------------------- | 1       |  example_a   | -------------------------- | 2       |  example_b   | -------------------------- 

can me this? goal display number of products sold each week.

week 1: example sold: 150, example b sold: 15 week 2: example sold: 100, example b sold: 50 etc 

group_by friend here.

i'm not sure table or column names, should on way:

select o.order_id, o.order_placed, op.product_id, pn.product_name, count(op.order_id) `count`  `orders` o inner join `order_products` op on op.order_id = o.id inner join `product_id_name` pin on pin.product_id = op.product_id inner join `product_name` pn on pn.id_name = pin.id_name  year(o.order_placed) = year(now())  , weekofyear(o.order_placed) = weekofyear(now()) group op.order_id 

[edit] ak,

order id: 1, date: 06/07/2015, product: 100052, name: example a, count: 2 order id: 2, date: 07/07/2015, product: 9987, name: example b, count: 1

i didn't see bit grouping them product - that's more of same thing. group product id rather order id.