Hello everyone!

We have been investing plenty of personal time and energy for many years to share our knowledge with you all. However, we now need your help to keep this blog running. All you have to do is just click one of the adverts on the site, otherwise it will sadly be taken down due to hosting etc. costs. Thank you.

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.


Dummy table


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


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;

Call


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)