19/04/2013 - MYSQL
Example below is a very simple MySQL view and designed to give you ideas if you would want to take it to the next step.
CREATE TABLE `activity` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ip` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`timestamp` datetime NOT NULL,
`url` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`user_id` int(10) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_activity_1_idx` (`user_id`),
CONSTRAINT `fk_activity_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO activity
(ip, timestamp, url, user_id)
VALUES
('1.1.1.1', '2015-04-01 18:10:59', '/url_one', 1),
('1.1.1.1', '2015-04-02 10:06:11', '/url_two', 1),
('2.2.2.2', '2015-04-01 21:00:00', '/url_one', 2),
('2.2.2.2', '2015-04-03 23:01:40', '/url_one', 2),
('3.3.3.3', '2015-04-02 02:23:30', '/url_one', 3),
('4.4.4.4', '2015-04-01 03:00:14', '/url_two', 4),
('5.5.5.5', '2015-04-01 13:30:33', '/url_one', 5);
mysql> SELECT * FROM activity;
+----+---------+---------------------+----------+---------+
| id | ip | timestamp | url | user_id |
+----+---------+---------------------+----------+---------+
| 1 | 1.1.1.1 | 2015-04-01 18:10:59 | /url_one | 1 |
| 2 | 1.1.1.1 | 2015-04-02 10:06:11 | /url_two | 1 |
| 3 | 2.2.2.2 | 2015-04-01 21:00:00 | /url_one | 2 |
| 4 | 2.2.2.2 | 2015-04-03 23:01:40 | /url_one | 2 |
| 5 | 3.3.3.3 | 2015-04-02 02:23:30 | /url_one | 3 |
| 6 | 4.4.4.4 | 2015-04-01 03:00:14 | /url_two | 4 |
| 7 | 5.5.5.5 | 2015-04-01 13:30:33 | /url_one | 5 |
+----+---------+---------------------+----------+---------+
7 rows in set (0.00 sec)
View below will return corresponding records for given ip in descending order by default.
DROP VIEW IF EXISTS all_activities_view;
CREATE VIEW all_activities_view AS
SELECT * FROM activity ORDER BY timestamp DESC;
mysql> SELECT * FROM all_activities_view WHERE ip = '1.1.1.1';
+----+---------+---------------------+----------+---------+
| id | ip | timestamp | url | user_id |
+----+---------+---------------------+----------+---------+
| 2 | 1.1.1.1 | 2015-04-02 10:06:11 | /url_two | 1 |
| 1 | 1.1.1.1 | 2015-04-01 18:10:59 | /url_one | 1 |
+----+---------+---------------------+----------+---------+
2 rows in set (0.00 sec)