Jump to content

Newbie: Mysql Select -> creating month statistics


mishina

Recommended Posts

Hello, im php, mysql newbie. I need a little help.

 

I have 2 tables:

 

1. DATA

- ID

- Code number

- Name

- Surname

 

2. ENTRIES

- ID

- Code number (same field like in DATA table)

- Timestamp

I need to create Table of statistics -> I would like to use Timestamp in ENTRIES table -> and create Month Stats

example:

January  8 (sum of rows with same timestamp-month entries)

February 10 (sum of rows with same timestamp-month entries)

etc.

 

I have table, Select created, its work but not correct.

I have this output:

 

YEAR  MONTH  SUM of Entries

2011 January    1

2011 January    1

2011 January    1

 

etc. (8 times January, then 10 times February)

 

Can you help me please?

Many Thanks.

Ok. I have mistakes, here, I know..  :confused:

 

$statAll="SELECT * FROM entries";
$statAllQ=mysql_query($statAll,$conn);


  
echo "<table border='0'>
<tr>
<th style=\"padding: 5px;\">Year</th>
<th style=\"padding: 5px;\">Month</th>
<th style=\"padding: 5px;\">Count</th>
</tr>"; 


while($row = mysql_fetch_array($statAllQ)):
  
echo "<tr style=\"text-align: center;\">";
   $yearx = date('Y', strtotime($row['timestamp']));
   echo "<td>" .$yearx. "</td>";
  echo "<td>";
$monthx = array(
1=>"január", 
   "február", 
   "marec",
   "apríl", 
   "maj", 
   "jun",
   "jul", 
   "august", 
   "september",
   "oktober", 
   "november", 
   "december");

$numb_month = date('n', strtotime($row['timestamp']));
$month_abbr = $monthx[$numb_month];
  echo $month_abbr;
  echo "</td>";



$stat=mysql_query("SELECT YEAR('timestamp'), MONTH('timestamp'), COUNT(*) FROM entries GROUP BY YEAR('timestamp'), MONTH('timestamp')")or die(mysql_error());
$rowsOut=mysql_num_rows($stat);


echo "<td>$rowsOut</td>";
  echo "</tr>";
                         

  endwhile;
echo "</table>";          

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.