Newbie: Mysql Select -> creating month statistics

[mishina]

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.

[awjudd]

Please provide us with your current query.

 

~juddster

[mishina]

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

 

$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>";          

[fenway]

That's not a query, that's php.

[mishina]

Well, ok.. there are some queries (i guess, false), i quess.. but ok, what to do, if i want to got some answer?

I need to move it to another forum? Or what?

[mikosiko]

you have 2 queries... you can do the same with just 1 query... the rest is just a matter of how you display the results.

 

try to write just the query and modify your code... post back your doubts.