Yannick
Yannick

Reputation: 11

MySQL: Count rows of database so far per day

I'm fairly new to SQL and PHP and have been burning my head over the following problem.

I'm looking for an SQL query (or in combination with PHP filtering) that could provide the following result: Basically a result for every day a row is added or removed + the amount of rows so far in that table.

Example:

2012-10-08 09:52:08    |   1 so far since beginning
2012-12-04 15:07:56    |   2 so far since beginning
2012-12-10 09:18:02    |   3 so far since beginning

...


2013-06-06 09:50:13    |   67 so far since beginning
2013-06-13 13:46:41    |   70 so far since beginning
2013-06-17 14:42:39    |   69 so far since beginning
2013-06-19 14:19:26    |   74 so far since beginning
2013-07-05 13:21:45    |   75 so far since beginning

Upvotes: 1

Views: 2006

Answers (4)

goat
goat

Reputation: 31813

select count(*) cnt
     , DATE(date_column) the_date
  from tbl
 group
    by date with rollup

note - if there are days without any entries, you will not get a row for that date in the result set from this sql query.

the with rollup modifier will add a single row, which will be the count of all rows. You can identify that row because it will have a null value for the_date. Alternatively, omit the with rollup and just sum the cnt column yourself.

Upvotes: 1

torr
torr

Reputation: 1316

To count entries in a database the query would be:

$query = SELECT COUNT(*) FROM `table`

This query will SELECT every entry in your table, and will count rows

To handle the connection you can use PDO or mysql_*.

I recommend you to use PDO since mysql_* is depreceted and as the official documentation say:

This extension is deprecated as of PHP 5.5.0, and will be removed in the future.

The code will look like this:

<?php
/** PDOs or MYSQL_* CONNECT HERE **/

echo date("Y-d-M h:m:s")."    |     ".$count." so far since beginning";
?>

Upvotes: 0

apartridge
apartridge

Reputation: 1820

You can use a GROUP BY to find today's records, and an subquery to find rows added before today. For instance,

SELECT DATE(t.datefield) AS date, COUNT(*) AS records_this_day, 
(SELECT COUNT(*) FROM mytable t2 WHERE t2.datefield < DATE(t.datefield)) AS records_before_this_day 
FROM mytable t GROUP BY DATE(t.datefield);

You will get results like this:

date        records_this_day records_before_this_day
2010-02-04  27               0
2010-02-05  2                27
2010-02-06  1                29
2010-02-07  1                30

But it is kinda slow. It may be better to just accumulate the value of records_this_day when you use this.

Edit: Obviously, if a row is deleted then history of that row is gone. So this may not be what you are looking for.

Upvotes: 2

Lance
Lance

Reputation: 4820

$sql = "SELECT COUNT(*) FROM table_name WHERE date = ''";

Upvotes: 0

Related Questions