PHP MySQLi Query for printing out year/dates in table format

[timwhelan]

I need helping thinking though something. I am pulling dates, Year, Month of that year and an amount. I then want to go to the next year. So, 2010 then 12 rows of the months with amounts, then 2011 then 12 rows of the months printed out with amounts...

Ex:
2010
January
February
March ...
...
2011
January
February ...

Does that make sense? So do I need to do a query for each year and month grouping - step and repeat?

or is Array the best way? (Not sure where to start here)

Any thoughts towards how to figure out would be great.
Thanks!

[pritaeas]

If you use a while loop for all results, you can store the previous year, and check whether it has changed in the next loop. If it has changed, output the year, otherwise don't.

$year = 0;
while ($result = ...) {
  if ($year <> $result['year']) {
    $year = $result['year'];
    echo $year . '<br/>';
  }
  echo $result['month']; // etc.
}

[jstfsklh211]

one query ordered by year/month

year = '';
foreach row
    if row[year] != year
        print year
    end if
    do stuff
end loop

[diafol]

"SELECT ... YEAR(`date`) AS `year`,  MONTH(`date`) AS `counter`, MONTHNAME(`date`) AS `month`, SUM(`prices`) AS total GROUP BY `year`, `counter` ORDER BY `year`,`counter`"

Ensure you then echo year, month and total in a loop to produce the table.
Is that it?

//EDIT

Just saw that there was a duplicate thread. :(

[pritaeas]

Just saw that there was a duplicate thread. :(

Don't think it is, the other one was about the query, this one is about displaying the result.

[diafol]

So it now seems. I read:

So do I need to do a query for each year and month grouping - step and repeat?

And got the wrong end of the stick?

OK, thanks Prit.

[timwhelan]

If you use a while loop for all results, you can store the previous year, and check whether it has changed in the next loop. If it has changed, output the year, otherwise don't.

$year = 0;
while ($result = ...) {
  if ($year <> $result['year']) {
    $year = $result['year'];
    echo $year . '<br/>';
  }
  echo $result['month']; // etc.
}

Okay I am sure I nee to learn a little more. I tried this and no go. Plus this has been on hold for a week..

$year = 0;
while ($result = mysql_fetch_array($year)) {
  if ($year <> $result['year']) {
    $year = $result['year'];
    echo $year . '<br/>';
  }
  echo $result['month']; // etc.
}

I am also coding for mysqli so not sure if there is a different function
Thanks

[pritaeas]

Show what you have now, and we'll help you fix it.

[timwhelan]

Okay sorry for the delay. Thank you for you assistance!

Here is the initial query you helped me with already.

if ($result = $db->query("SELECT YEAR(reg_date) AS 'reg_year', MONTHNAME(reg_date) AS 'reg_month', COUNT(*) AS 'reg_count' FROM micro_applicants GROUP BY YEAR(reg_date), MONTH(reg_date)")) {

What I am trying to accomplish is listing the Year, Months with count of sign ups, like so:
2012
January = 10
February = 3
2011
January = 7
February =11
March = 9
etc

Here is the code I started writing trying to figure this out.

for ( $years = $row->reg_year; $years <= $row->reg_year; ++$years) {
	echo "$years ";
	
	while ($row = $result->fetch_object(YEAR)) {
		foreach ($months AS $month) {
			if $months = $month {
			     echo "$month";
                        }
                 }
        }
}

[timwhelan]

Okay so I have gotten this far...

/* HERE IS THE FIRST TEST LISTING EVERYTHING IN EACH LINE.*/
while ($row = $result->fetch_object()) {
echo '<br /> Year '. $row->reg_year .   "<br />";

	/* fetch associative array */
    while ($row = mysqli_fetch_assoc($result)) {
        printf ("%s (%s)\n", $row["reg_month"], $row["reg_count"]);
    }

}

Which prints this:

Year 2010
November (7) December (6) January (4) February (6) March (2) April (2) May (5) June (3) July (16) August (10) September (7) October (7) November (3) December (9) January (4) February (3)

Now I need to figure out splitting it between the years correctly

[pritaeas]

Something like this:

$year = 0;
while ($row = $result->fetch_object()) {
  if ($year <> $row->reg_year) {
    echo '<br/>Year ' . $row->reg_year . '<br/>';
    $year = $row->reg_year;
  }
  
  printf ("%s (%s)<br/>", $row->reg_month, $row->reg_count);
}

[timwhelan]

That is awesome! Can you tell me what the <> means in line 3? Does that mean count through?
Does starting the $year =0; mean start counting and end when done?

So it goes through the years and print the months under that. Thanks. I will mark as solved after this.

Thank you for you assistance.

[pritaeas]

1. $year stores 0
3. compares if the year in the current row is not equal (<>) to $year
4/5. if it is not, it prints the year, and copies it to $year for the next row to check

[timwhelan]

Thank you for all your help, I appreciate your patience. And thanks for the explanation. Trying to learn in every step.

Good Times!