Showing Totals In Monthly Columns and Rows as the years

PHP
1

Hi,

I would like to do something like this:

   Jan Feb Mar Apr

2014 £58 £68 £23 £58
2015 £28 £78 £58 £2
2016 £16 £18 £54 £85

I have the following, but don’t seem to be working, it is getting all the years down one column, but only bringing up the first line of totals for the first year and doesn’t give any other lines of totals for the other years.

Here is the script I have so far.

<?php
$sql8 = mysql_query('SELECT Year(date) FROM ss4i_earnings GROUP BY YEAR(date)');
$count8 = mysql_num_rows($sql8);{
while ($i8 < $count8) {$row8=mysql_fetch_array($sql8);
$rowed8 = "$row8[0]";
echo "<tr><td bgcolor='white'>$rowed8</td>";

$sql7 = mysql_query("SELECT SUM(pay_d) FROM ss4i_earnings WHERE YEAR(date) = $rowed8 GROUP BY DATE_FORMAT(date, '%Y-%m')");
$count7 = mysql_num_rows($sql7);{
while ($i7 < $count7) {$row7=mysql_fetch_array($sql7);

echo "<td bgcolor='white'>$row7[0]</td>";
$i7++;}}
echo "</tr>";
$i8++;}}
echo "</table>";
}
?>
2

Aside from what you want to do, you are using dangerous, obsolete Mysql code that has been completely removed from current versions of Php. You need to use PDO with Prepared Statements.

3

Aside from the obsolete mysql_query() call, can you give a bit more information please on how your data is laid out? It’s very difficult to suggest why the problem might be happening without knowing how stuff is laid out. It may be possible to use a single query to retrieve the data - for example get all of the results, then use PHP to decide when to throw a new table row because the year has changed.

You can simplify this bit as well. Instead of:

$count7 = mysql_num_rows($sql7);{
while ($i7 < $count7) {$row7=mysql_fetch_array($sql7);

and maintaining your own count variable, it’s just as easy to use

while ($row7 = mysql_fetch_array($sql7)) {

and is still the case when you switch to PDO or mysqli. Unless you need the incrementing counter for something else, of course.

4

Database is as follows

Id Date Company Amount
12 2004-12-20 entertainment 355.08
13 2004-12-22 share hosting 5.85
14 2005-01-20 gaming 185.10
15 2005-01-25 hobbies 634.51

I want to get the total cost for each month, where it is listed so the colours are months and the rows are the years.

5 
<?php
$sql8 = mysql_query('SELECT Year(date) FROM ss4i_earnings GROUP BY YEAR(date)');
while ($row8=mysql_fetch_array($sql8)){
echo "<tr><td bgcolor='white'>$row8[0]</td>";

$sql7 = mysql_query("SELECT SUM(pay_d) FROM ss4i_earnings WHERE YEAR(date) = $row8[0] GROUP BY DATE_FORMAT(date, '%Y-%m')");
while ($row7=mysql_fetch_array($sql7)){

echo "<td bgcolor='white'>$row7[0]</td>";
}
echo "</tr>";
}
echo "</table>";
}
?>

I’ve done it using the above.

Thanks

6

And does it work? That looks very similar to the code you posted originally.

You should have a read up on “Database normalisation” when you get a chance. I don’t think it’s a great idea to have a column that reads “entertainment”, for example, you should have a separate table for those categories and just stick the id into this table.

categories:
id      name
1       entertainment
2       gaming
3       hobbies

this-table:

12  2004-12-20  1 355.08
13  2004-12-22  4 5.85

and so on. Easy to change a name, easy to write code for whoever enters this data to choose from a category list.

7

The way I think I’d do it is with this query:

SELECT year(Date), month(date), sum(amount) 
from sortingtest group by date_format(date, "%Y-%m")

In my PHP code, I’d then retrieve each of the results, and compare the year value to the year value from the previous row retrieved. When the year changes, close the previous table row (if there was one open), and open the new row. At the end, close the table.

With this query, you also need to look at the month value in order to account for months where there is no information. I think that’s the same with your original code, though - if there were no sales in March, you need to output a blank month.

Pseudo-code:

query (SELECT year(Date), month(date), sum(amount) from sortingtest group by date_format(date, "%Y-%m"))
if (any rows returned) {
   output table header
   last-year = ""
   for each row returned:
      if year <> last-year: 
         if last-year <> ""
           close table row
         end if
         open new table row
      end if
     check month value, perhaps send some blank columns to make it line up.
    output figure
    last-year = year
  end for loop
8

Danger Will Robinson

Date is a reserved word. If you have named a column with it, you must needs encapsulate references to it properly with backticks.

1 Like
9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.