· 7 years ago · Feb 21, 2019, 06:22 PM
1+-----+------------+--------------+
2| ID | USERNAME | ADDED |
3+-----+------------+--------------+
4| 1 | Vlad | 1347386878 |
5+-----+------------+--------------+
6| 2 | Test | 1347386578 |
7+-----+------------+--------------+
8
9SELECT date(added), COUNT(id) AS num_registered
10FROM users
11WHERE added < CURDATE()
12AND added > CURDATE() - INTERVAL 7 DAYS
13GROUP BY date(added) LIMIT 1, 7
14
15$mysql_query = mysql_query('SELECT added, DATE(added), COUNT(id) AS num_reg FROM users_test WHERE added < (UNIX_TIMESTAMP() - (7 * 24 * 60 * 60)) GROUP BY DATE(added) LIMIT 1, 7') or die(mysql_error());
16while($row = mysql_fetch_array($mysql_query))
17{
18 $month = date('F', $row['added']);
19 $day = date('j', $row['added']);
20 $textbuilder .= '
21 <li>
22 <a href="#" title="'.$month.' '.$day.', '.$row['num_reg'].' registered">
23 <span class="label">'.$day.'</span>
24 <span class="count" style="height: 20%">('.$row['num_reg'].')</span>
25 </a>
26 </li>';
27}
28
29CREATE TABLE IF NOT EXISTS `users_test` (
30 `id` int(10) NOT NULL,
31 `username` varchar(60) NOT NULL,
32 `added` int(10) NOT NULL,
33 PRIMARY KEY (`id`)
34) ENGINE=InnoDB DEFAULT CHARSET=latin1;
35
36INSERT INTO `users_test` (`id`, `username`, `added`) VALUES
37(1, 'Test', 1347303641),
38(2, 'Test1', 1347217241),
39(3, 'Test2', 1347130841),
40(4, 'Test3', 1347044441);
41
42SELECT
43 DATE(Added), Count()
44FROM
45 Users
46WHERE
47 Added > ( UNIX_TIMESTAMP() - ( 7 * 24 * 60 * 60 ) )
48GROUP BY
49 DATE(Added)
50LIMIT
51 1, 7
52
53SELECT FROM_UNIXTIMESTAMP(added, , '%b %D, %Y'), COUNT(id) AS num_registered
54FROM users
55WHERE added > unix_timestamp() - ( 7 - 86400 )
56GROUP BY FROM_UNIXTIMESTAMP(added, , '%b %D, %Y')
57
58SELECT DATE(Added), COUNT(id) AS num_registered FROM
59 (SELECT id, username, FROM_UNIXTIME(added) AS added FROM users) u
60WHERE added < CURDATE()
61 AND added > CURDATE() - INTERVAL 7 DAYS
62GROUP BY DATE(added) LIMIT 1, 7