title: Get unique data from multiple tables week by week
body: <p>I have a hard time dealing with multiple tables. I want to get the total number of products from all orders placed week by week.
  I used the following code:</p>
  
  <pre><code>$sql = "SELECT  id_order, order_placed FROM orders                      
          WHERE YEAR(order_placed) = YEAR(NOW()) 
          AND WEEKOFYEAR(order_placed) = WEEKOFYEAR(NOW())";
  </code></pre>
  
  <p>The above query will return all orders placed the current week. Then I added a new sql query to get all the product IDs from the retrieved ID orders.</p>
  
  <pre><code>$sql2 = "SELECT id_product, COUNT(id_product) AS num_products 
           FROM order_product 
           WHERE id_order = '".$id_order."'";
  $results2 = Db::getInstance()-&gt;ExecuteS($sql2);
  </code></pre>
  
  <p>In the same way I get the rest info. When I print the final statement it will be like:</p>
  
  <pre><code>Order ID: 1, Date: 06/07/2015, Product: 100052, Name: Example A
  Order ID: 1, Date: 06/07/2015, Product: 100052, Name: Example A
  Order ID: 2, Date: 07/07/2015, Product: 9987,   Name: Example B
  </code></pre>
  
  <p>But I want to find a way to show the total number:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Also I will group them by week so each line will contain just info from the current week.</p>
  
  <p>Below you can see the tables to get an idea (I simplify them a lot in order to be easier for you)</p>
  
  <p>ORDERS</p>
  
  <pre><code>-----------------------------------
  | Order ID |  Date                 |
  -----------------------------------
  | 1        |  06/07/2015 15:20:01  |
  -----------------------------------
  | 2        |  07/07/2015 10:10:41  |
  -----------------------------------
  </code></pre>
  
  <p>ORDER_PRODUCTS</p>
  
  <pre><code>    ------------------------------------------
      | Order ID |  Product ID  | Product Type |
      ------------------------------------------
      | 1        |  10052       |  1           |
      ------------------------------------------
      | 1        |  10052       |  1           |
      ------------------------------------------
      | 2        |  9987        |  0           |
      ------------------------------------------
  </code></pre>
  
  <p>A customer can order as many products as he wants.. That is why the above table contain 10052 ID twice. That customer for example ordered 10052 twice.
  Also, I only want type '1' to be retrieved.</p>
  
  <p>PRODUCT_ID_NAME</p>
  
  <pre><code>------------------------
  | Product ID | ID Name |
  ------------------------
  | 10052      |  1      |
  ------------------------
  | 9987       |  2      |
  ------------------------
  </code></pre>
  
  <p>PRODUCT NAME</p>
  
  <pre><code>--------------------------
  | ID Name | Product Name |
  --------------------------
  | 1       |  Example_A   |
  --------------------------
  | 2       |  Example_B   |
  --------------------------
  </code></pre>
  
  <p>Can anyone help me with this?
  The goal is to display the number of products sold each week.</p>
  
  <pre><code>Week 1: Example A sold: 150, Example B sold: 15
  Week 2: Example A sold: 100, Example B sold: 50
  etc
  </code></pre>