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 function and designed to give you ideas if you would want to take it to the next step.


Facts



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)

Function


Function below will return corresponding url for given ip.


DROP FUNCTION IF EXISTS get_latest_ip_visit;

DELIMITER $$

CREATE FUNCTION get_latest_ip_visit (IN_ip VARCHAR(15))
RETURNS VARCHAR(250)
BEGIN
DECLARE OUT_url VARCHAR(250);

SELECT url INTO OUT_url
FROM activity
WHERE `ip` = IN_ip
ORDER BY `timestamp` DESC
LIMIT 1;

RETURN OUT_url;
END$$

DELIMITER ;

Call


mysql> SELECT get_latest_ip_visit('1.1.1.1') AS Url;
+----------+
| Url |
+----------+
| /url_two |
+----------+
1 row in set (0.00 sec)