[MySQL][PHP]Grupowanie danych według roku i miesiąca - Datetime Opcje

[Tidude]

Cześć.
Próbuję zrobić sobie tabelkę z informacjami o ilości zarejestrowanych użytkowników.
Chciałbym, żeby wyglądała ona tak:

[HTML] pobierz, plaintext 
2012: 
  styczeń   10
  luty   5
  marzec     11
[HTML] pobierz, plaintext 

Gdzie liczby 10,5,11 to ilość osób zarejestrowanych w danym miesiącu.
Udało mi się napisać skrypt, który tworzy następującą tabelę.

[HTML] pobierz, plaintext 
2012: 
  styczeń
  luty
  marzec
[HTML] pobierz, plaintext 

I mam problem z wyciągnięciem ilości użytkowników z danego miesiąca. Według poniższego kodu, pokazuje mi że w danym miesiącu zarejestrował się jeden użytkownik, choć było ich więcej. Doszedłem do wniosku, że to przez linijkę $ilosc = $row['ilosc']; bo dodałem ją w złym miejscu, a nie wiem jak ją dodać w innym.

[PHP] pobierz, plaintext 
$query = "select count(*) as ilosc, DATE_FORMAT(data_rejestracji,'%Y-%m') as month, DATE_FORMAT(data_rejestracji,'%Y') as year FROM uzytkiwnicy GROUP BY month ORDER BY data_rejestracji";
$result = mysql_query($query) or die(mysql_error());
 
$storage_array = array();
while($row = mysql_fetch_assoc($result)) {
   $year = $row['year'];
   $month = $row['month'];
   $ilosc = $row['ilosc'];
   $storage_array[$year][] = $month;
}
 
foreach ($storage_array as $year => $month_array){
   echo "<ul class='year'><li><a>{$year}</a>";
   foreach ($month_array as $month){
      echo "<ul class='months'><li><a>{$month} {$ilosc}</a></li></ul>
";
 
   }
   echo "</li></ul>";
}
[PHP] pobierz, plaintext 

[Tidude]

ten kod nie do końca wyświetla to co bym chciał, bo z tych danych chcę zrobić tabelkę.
I chcę żeby rok, miesiąc i ilość były wyświetlane oddzielnie.

Hmm, a wiesz jak inaczej to wyświetlić?

Podobny kod znalazłem w phpbb, ale nie wiem jak go przerobić by działał mi.

[PHP] pobierz, plaintext 
if ( !defined('IN_PHPBB') )
{
	die('Hacking attempt');
}
 
$statistics_module = true;
 
/***************************************************************************
 *								module.php
 *                            -------------------
 *   begin                : Tuesday, Sep 03, 2002
 *   copyright            : (C) 2002 Meik Sievertsen
 *   email                : acyd.burn@gmx.de
 *
 ***************************************************************************/
 
/***************************************************************************
 *
 *   This program is free software; you can redistribute it and/or modify
 *   it under the terms of the GNU General Public License as published by
 *   the Free Software Foundation; either version 2 of the License, or
 *   (at your option) any later version.
 *
 ***************************************************************************/
 
//
// Modules should be considered to already have access to the following variables which
// the parser will give out to it:
 
// $return_limit - Control Panel defined number of items to display
// $module_info['name'] - The module name specified in the info.txt file
// $module_info['email'] - The author email
// $module_info['author'] - The author name
// $module_info['version'] - The version
// $module_info['url'] - The author url
//
// To make the module more compatible, please do not use any functions here
// and put all your code inline to keep from redeclaring functions on accident.
//
 
//
// All your code
//
// New users by month
//
 
if (!$statistics->result_cache_used)
{
	// Init Cache -- tells the Stats Mod that we want to use the result cache
	$result_cache->init_result_cache();
 
	$sql = "SELECT YEAR(FROM_UNIXTIME(user_regdate)) as aar, MONTH(FROM_UNIXTIME(user_regdate)) as mnd, COUNT(*) AS ant 
	FROM " . USERS_TABLE . " 
	WHERE (user_id <> " . ANONYMOUS . " )
	GROUP BY YEAR(FROM_UNIXTIME(user_regdate)), MONTH(FROM_UNIXTIME(user_regdate)) 
	ORDER BY user_regdate";
 
	if ( !($result = $db->sql_query($sql)) )
	{
		message_die(GENERAL_ERROR, 'Couldn\'t retrieve users data', '', __LINE__, get_module_fd_name(__FILE__), $sql);
	}
 
	$user_count = $db->sql_numrows($result);
	$user_data = $db->sql_fetchrowset($result);
 
	for ($i = 0; $i < $user_count; $i=$i+$k)
	{
		$class = ( !($i+1 % 2) ) ? $theme['td_class2'] : $theme['td_class1'];
 
		$year = $user_data[$i]['aar'];
		$k = 0;
		for ($j = 0; $j < 12; $j++)
		{
			$m[$j+1] = 0;
		}
		for ($j = 0; $j < 12; $j++)
		{
			if ($year == $user_data[$i+$j]['aar'])
			{
				$month = $user_data[$i+$j]['mnd'];
				$m[$month] = $user_data[$i+$j]['ant'];
				$k = $k + 1;
			}
		}
		$template->assign_block_vars('signup', array(
			'CLASS' => $class,
			'YEAR' => $year,
			'M01' => $m[1],
			'M02' => $m[2],
			'M03' => $m[3],
			'M04' => $m[4],
			'M05' => $m[5],
			'M06' => $m[6],
			'M07' => $m[7],
			'M08' => $m[8],
			'M09' => $m[9],
			'M10' => $m[10],
			'M11' => $m[11],
			'M12' => $m[12])
		);
 
		$result_cache->assign_template_block_vars('signup');
	}
}
else
{
	for ($i = 0; $i < $result_cache->block_num_vars('signup'); $i++)
	{
		// Method 1: We are assigning the block variables from the result cache to the template. ;)
		$template->assign_block_vars('signup', $result_cache->get_block_array('signup', $i));
 
	}
 
}
$template->assign_vars(array(
	'L_SIGNUPBYMONTH' => $lang['Signup_month'],
	'L_YEAR' => $lang['Year'],
	'L_MONTH' => $lang['Month'],
	'L_NUMBER' => $lang['Number'],
	'L_JAN' => $lang['Month_jan'],
	'L_FEB' => $lang['Month_feb'],
	'L_MAR' => $lang['Month_mar'],
	'L_APR' => $lang['Month_apr'],
	'L_MAY' => $lang['Month_may'],
	'L_JUN' => $lang['Month_jun'],
	'L_JUL' => $lang['Month_jul'],
	'L_AUG' => $lang['Month_aug'],
	'L_SEP' => $lang['Month_sep'],
	'L_OCT' => $lang['Month_oct'],
	'L_NOV' => $lang['Month_nov'],
	'L_DEC' => $lang['Month_dec'])
);
[PHP] pobierz, plaintext 

A za wyświetlanie odpowiada .tpl

[HTML] pobierz, plaintext 
<table border="0" width="100%" cellpadding="2" cellspacing="1" class="forumline"> 
  <tr> 
    <td class="catHead" align="center" colspan="13"> 
      <span class="cattitle">{L_SIGNUPBYMONTH}</span> 
    </td> 
  </tr> 
 
  <tr>    
    <th class="thCornerL" align="center" ><strong>{L_YEAR}</strong></th> 
    <th class="thTop" align="center"><strong>{L_JAN}</strong></th> 
    <th class="thTop" align="center"><strong>{L_FEB}</strong></th> 
    <th class="thTop" align="center"><strong>{L_MAR}</strong></th> 
    <th class="thTop" align="center"><strong>{L_APR}</strong></th> 
    <th class="thTop" align="center"><strong>{L_MAY}</strong></th> 
    <th class="thTop" align="center"><strong>{L_JUN}</strong></th> 
    <th class="thTop" align="center"><strong>{L_JUL}</strong></th> 
    <th class="thTop" align="center"><strong>{L_AUG}</strong></th> 
    <th class="thTop" align="center"><strong>{L_SEP}</strong></th> 
    <th class="thTop" align="center"><strong>{L_OCT}</strong></th> 
    <th class="thTop" align="center"><strong>{L_NOV}</strong></th> 
    <th class="thCornerR" align="center"><strong>{L_DEC}</strong></th> 
  </tr> 
 
 
   <!-- BEGIN signup --> 
   <tr> 
	<td class="{signup.CLASS}" align="center" ><span class="gen">{signup.YEAR}</span></td> 
	<td class="{signup.CLASS}" align="center" valign="middle"><span class="gen">{signup.M01}</span></td> 
	<td class="{signup.CLASS}" align="center" valign="middle"><span class="gen">{signup.M02}</span></td> 
	<td class="{signup.CLASS}" align="center" valign="middle"><span class="gen">{signup.M03}</span></td> 
	<td class="{signup.CLASS}" align="center" valign="middle"><span class="gen">{signup.M04}</span></td> 
	<td class="{signup.CLASS}" align="center" valign="middle"><span class="gen">{signup.M05}</span></td> 
	<td class="{signup.CLASS}" align="center" valign="middle"><span class="gen">{signup.M06}</span></td> 
	<td class="{signup.CLASS}" align="center" valign="middle"><span class="gen">{signup.M07}</span></td> 
	<td class="{signup.CLASS}" align="center" valign="middle"><span class="gen">{signup.M08}</span></td> 
	<td class="{signup.CLASS}" align="center" valign="middle"><span class="gen">{signup.M09}</span></td> 
	<td class="{signup.CLASS}" align="center" valign="middle"><span class="gen">{signup.M10}</span></td> 
	<td class="{signup.CLASS}" align="center" valign="middle"><span class="gen">{signup.M11}</span></td> 
	<td class="{signup.CLASS}" align="center" valign="middle"><span class="gen">{signup.M12}</span></td> 
   </tr> 
	<!-- END signup --> 
</table>
[HTML] pobierz, plaintext 

Ten post edytował Tidude 2.07.2013, 00:00:54