[SOLVED] Count & display number of records per day

[hanscom]

Hi,

I use a MySQL db where one of the fields in a table is 'date'.

How can I count and display the records of the same date in php  while

the output for the last 30 days will be like:

 

Date                      Hits

2009-03-08            35

2009-03-09              0

2009-03-10            14

2009-03-11            27

 

Thanks in advance.

Regards, Hans

[corbin]

Depends what your table looks like.

 

 

You probably want either GROUP along with COUNT or GROUP with SUM.

[POG1]

Try something like this:

SELECT `date`, SUM(hits) FROM `tableNname` GROUP BY `date`;

[hanscom]

The table has the fields 'date' 'host' 'page'

I also tried :

SELECT date, COUNT(host) AS rowtotal FROM `tablename` GROUP BY date ORDER BY rowtotal DESC LIMIT 30;

The problem is the script for printing the output, perhaps by using a 'while' or 'for each' loop.

Can somebody provide an example?

[djlee]

im usually terrible at off the top of my head coding examples but we'll see, how does this work

 

$res = mysql_query("SELECT date, COUNT(host) AS rowtotal FROM `tablename` GROUP BY date ORDER BY rowtotal DESC LIMIT 30");

echo "<table>";

while($arr = mysql_fetch_assoc($res)) {

echo "<tr><td>".$arr['date']."</td><td>".$arr['rowtotal']."</td></tr>";

}

echo "</table>";

[hanscom]

Thanks for your help. It looks promising.

$res = mysql_query("SELECT date, COUNT(host) AS rowtotal FROM table GROUP BY date ORDER BY rowtotal DESC LIMIT 30");

 

Output:

2009-01-05 21:56:08 2

2009-02-02 02:29:26 2

2009-03-06 17:31:42 2

2009-03-10 15:41:57 1

2009-03-04 20:48:40 1

2009-03-01 13:48:48 1

2009-02-26 09:28:37 1

etc. .. ( 30 rows total)

  It looks like the query picked mixed dates.

 

When    ORDER BY date DESC LIMIT 30 in:

$res = mysql_query("SELECT date, COUNT(host) AS rowtotal FROM table GROUP BY date ORDER BY rowtotal DESC LIMIT 30");

 

Output:

2009-03-13 12:57:32 1

2009-03-13 12:57:23 1

2009-03-13 12:57:14 1

2009-03-13 10:14:12 1

2009-03-12 20:58:44 1

2009-03-12 20:58:35 1

2009-03-12 12:47:25 1

2009-03-11 21:54:22 1

2009-03-11 21:18:22 1

etc. .. (30 rows total)

 

Should be:

2009-03-13 12:57:32 4

2009-03-12 12:57:32 3

2009-03-11 12:57:32 2

etc. ..(30 rows)

Is this possible?

[fenway]

I don't understand what you mean.

[hanscom]

I try to get each day(date) followed by the total number of records of that day for the last

30 days.

[fenway]

Then you want to group by the "date' part of the datetime field only, right? Try "GROUP BY DATE(date)" instead.

[hanscom]

Solution found!

$res = mysql_query("SELECT DISTINCT(date(date)) AS day, count( * ) AS count FROM lacauserie_host GROUP BY day ORDER BY day DESC");

 

echo "<table>";

while($arr = mysql_fetch_assoc($res)) {

echo "<tr><td>".$arr['day']."</td><td> => </td><td>".$arr['count']."</td></tr>";

}

echo "</table>";

 

Thanks for hints. Regards

[fenway]

DISTINCT is not what you want....

[imrankhan]

 

Hi,

I use a MySQL db where one of the fields in a table is 'advertise_date'  is date time and other is "tender_title" it is Varchar becasue i have to enter their names but for generating report How can I count and display the records of the same date in php while if u have any confusion ask me

the output for the last 30 days will be like:

 

Date                      Tenders

2009-03-08            35

2009-03-09              0

2009-03-10            14

2009-03-11            27

 

Thanks in advance.

Regards, imran