title: Create line chart using value from SQL database
body: <p>So I've been following this tutorial: <a href="https://www.plus2net.com/php_tutorial/chart-line-database.php" rel="nofollow noreferrer">https://www.plus2net.com/php_tutorial/chart-line-database.php</a></p>
  <p>I am trying to add a line chart to my website to display number of sales for each month.</p>
  <p>This is an example on how my SQL table looks like:</p>
  <pre><code>| ID |  user   | sale_id |         date        |
  |  1 | RVN4372 | 1341234 | 2020-09-22 17:31:32 |
  |  2 | OVI6517 | 5452351 | 2020-09-22 15:14:43 |
  |  3 | RVN4372 | 8452176 | 2020-09-17 16:23:54 |
  |  4 | FOK8905 | 7421312 | 2020-09-17 11:23:11 |
  |  5 | DIF9127 | 4236123 | 2020-09-15 15:32:26 |
  </code></pre>
  <p>This is how my current query looks like:</p>
  <pre><code>&lt;?php
            
  if($stmt = $link-&gt;query(&quot;SELECT user,COUNT(*) FROM sales WHERE yearweek(DATE(date), 1) = yearweek(curdate(), 1) GROUP BY user order by COUNT(*) DESC&quot;)){
  
  $php_data_array = Array(); // create PHP array
  while ($row = $stmt-&gt;fetch_row()) {
     $php_data_array[] = $row; // Adding to array
     }
  }else{
  echo $link-&gt;error;
  }
  //print_r( $php_data_array);
  // You can display the json_encode output here. 
  echo json_encode($php_data_array); 
  
  // Transfor PHP array to JavaScript two dimensional array 
  echo &quot;&lt;script&gt;
          var my_2d = &quot;.json_encode($php_data_array).&quot;
  &lt;/script&gt;&quot;;
  ?&gt;
  
  &lt;div id=&quot;curve_chart&quot;&gt;&lt;/div&gt;
  </code></pre>
  <p>This is how it looks like on my website:</p>
  <p><a href="https://i.stack.imgur.com/b9z5Q.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/b9z5Q.png" alt="Image here" /></a></p>
  <p>So this basically groups the users, and count how many sales each user has. On the X axis is display the user's name, and Y axis total number of sales.</p>
  <p>I want to change this, so in the X asix is display the month, and Y asis total number of sales. How can I accomplish this?</p>
  <p>EDIT: Hava been trying out some, but can't make it work. This is what I've got so far:</p>
  <pre><code>if($stmt = $link-&gt;query(&quot;
  SELECT YEAR(date) 
  as SalesYear,
  MONTH(date) as SalesMonth,
  COUNT(*) AS TotalSales
  FROM sales
  GROUP BY YEAR(date), MONTH(date)
  ORDER BY YEAR(date), MONTH(date)
  AND COUNT(*) DESC
  &quot;)){
  </code></pre>