PHP Add Values in Rows and Separate By Month

[ryanfilard]

I am currently trying to make a line graph out of data using php and javascript. How do I add up the values of a column and separate them by month. For example the database has 74 amount of rows. Then the values from the "views" column get added up and then displayed by month.

 

   <table width="70%" class="linechart">
     <thead>
       <tr>
         <td></td>
         <th scope="col">Jan</th>
         <th scope="col">Feb</th>
         <th scope="col">Mar</th>
         <th scope="col">Apr</th>
         <th scope="col">May</th>
         <th scope="col">Jun</th>
         </tr>
       </thead>
     
     <tbody>
       <tr>
         <th scope="row">Post Views</th>
         <td>24324</td>
         <td>29634</td>
         <td>15435</td>
         <td>56545</td>
         <td>23543</td>
         <td>2123</td>
         </tr>
         
       </tbody>
   </table>

[PFMaBiSmAd]

You would do this in your query by using SUM(views) to add up the numbers and use GROUP BY EXTRACT(YEAR_MONTH FROM your_date_column) to form groups of rows for each year/month period. You should also probably select YEAR(your_date_column) and MONTH(your_date_column) so that you get those two values with the data so that you can detect which year and month the data belongs with as you iterate over the result of the query.

 

Edit: or you might want to use DATE_FORMAT(your_date_column,'%b') to get the abbreviated month name instead of MONTH(your_date_column).