MySQL:每天计算数据库的行数

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

我对SQL和PHP相当陌生，并且对下面的问题感到头疼。

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.

我正在寻找一个SQL查询(或与PHP过滤结合使用)，它可以提供以下结果:基本上每天添加或删除一行的结果+该表中到目前为止的行数。

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

4 个解决方案

#1

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

您可以使用GROUP BY查找今天的记录，并使用子查询查找今天之前添加的行。例如,

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.

但是有点慢。在使用records_this_day时，最好只积累records_this_day的值。

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

编辑:显然，如果删除了一行，那么该行的历史就会消失。所以这可能不是你要找的。

#2

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.

注意——如果有天没有任何条目，那么您将不会从这个sql查询的结果集中获得该日期的行。

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.

with rollup修饰符将添加一行，这将是所有行的计数。您可以识别这一行，因为它将为the_date提供一个空值。或者，省略with rollup，自己对cnt列求和。

#3

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

#4

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_*.

要处理连接，可以使用PDO或mysql_*。

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

我建议您使用PDO，因为mysql_*是在前面的，正如官方文档所说:

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

这个扩展在PHP 5.5.0中已经被弃用，以后将被删除。

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

#1