Ask a database question. there is a table containing the data stored every day and how to retrieve the number of data entries per month. This is the ec_ip_article table, and iptime is the daily timestamp. Is there a simple way to get an SQL statement every month, and then count the number, that is, there are too many SQL statements.
Reply to discussion (solution)
select FROM_UNIXTIME(iptime,'%Y-%m') as m, count(*) as cnt from ec_ip_article group by 1
select FROM_UNIXTIME(iptime,'%Y%m%d'),count(0) from ec_ip_article group by FROM_UNIXTIME(iptime,'%Y%m%d')
select FROM_UNIXTIME(iptime,'%Y%m%d'),count(0) from ec_ip_article group by FROM_UNIXTIME(iptime,'%Y%m%d')
Select FROM_UNIXTIME (iptime, '% Y % M'), count (0) from ec_ip_article group by FROM_UNIXTIME (iptime,' % Y % M ')
SELECT ip_id,count(ip_id) count,FROM_UNIXTIME(iptime, '%Y%m%d') date FROM test GROUP BY FROM_UNIXTIME(iptime, '%c') ORDER BY iptime
select FROM_UNIXTIME(iptime,'%Y-%m') as m, count(*) as cnt from ec_ip_article group by 1
Is % Y-% m in the timestamp?
This is the ec_ip_article table, and iptime is the daily timestamp. Is there a simple way to get an SQL statement every month, and then count the number, that is, there are too many SQL statements.
$sql="select FROM_UNIXTIME(iptime,'%Y-%m') as m, count(*) as cnt from ec_ip_article group by 1 where user_id=392";$result=mysql_query($sql);$row=mysql_fetch_row($result);dump($row);
Why can't I retrieve it?
select FROM_UNIXTIME(iptime,'%Y-%m') as m, count(*) as cnt from ec_ip_article group by 1
$sql="select FROM_UNIXTIME(iptime,'%Y-%m') as m, count(*) as cnt from ec_ip_article group by 1 where user_id=392";$result=mysql_query($sql);$row=mysql_fetch_row($result);dump($row);
Why can't I retrieve it?
Impossible! How did you write it?
Test example
mysql_connect();mysql_select_db('test');mysql_query("create temporary table ec_ip_article ( iptime int )");$a = array('2015-02-10', '2015-02-20', '2015-03-10','2015-04-10','2015-04-21',);foreach($a as $t)mysql_query(sprintf("insert into ec_ip_article values (%d)", strtotime($t)));$sql = "select FROM_UNIXTIME(iptime,'%Y-%m') as m, count(*) as cnt from ec_ip_article group by 1";$rs = mysql_query($sql);while($r = mysql_fetch_assoc($rs)) echo join(' ', $r), PHP_EOL;
2015-02 22015-03 12015-04 2
Test example
mysql_connect();mysql_select_db('test');mysql_query("create temporary table ec_ip_article ( iptime int )");$a = array('2015-02-10', '2015-02-20', '2015-03-10','2015-04-10','2015-04-21',);foreach($a as $t)mysql_query(sprintf("insert into ec_ip_article values (%d)", strtotime($t)));$sql = "select FROM_UNIXTIME(iptime,'%Y-%m') as m, count(*) as cnt from ec_ip_article group by 1";$rs = mysql_query($sql);while($r = mysql_fetch_assoc($rs)) echo join(' ', $r), PHP_EOL;
2015-02 22015-03 12015-04 2
In the ec_ip_article table, if there is a user ID field next to it, it is not feasible to add the where field directly when calculating the number of times of each user per month.