mysql - Selective query -
i'm keeping database of accesses website (origin ip, refrer , landing page) can analisys of visitors , pages more interesting , wich need work (amongst other things)
now visitor ips don't interest me - google's or microsoft's robots have identified, or company's own ip (we have static ip hosting , mail server), have table holding ips.
i have query's access data both pages , date range, however, need show full access history on ips except ones on excluded ips table - want first access of each day
edit: per request, here's example of need: here's sample tables/data (also on sqlfiddler)
create table access ( id int(11), accessdate datetime, ip varchar(15), referer varchar(150), landpage varchar(150) ); create table removed ( ip varchar(15) ); insert access (id, accessdate, ip, referer, landpage) values (1, '2015-08-25 12:22:24', '123.123.123.123', 'www.google.com', 'www.mydomain.com'); insert access (id, accessdate, ip, referer, landpage) values (2, '2015-08-25 12:25:24', '123.123.123.123', 'www.mydomain.com', 'www.mydomain.com/sub1'); insert access (id, accessdate, ip, referer, landpage) values (3, '2015-08-25 13:22:24', '103.123.123.123', 'www.google.com', 'www.mydomain.com'); insert access (id, accessdate, ip, referer, landpage) values (4, '2015-08-25 13:23:24', '103.123.123.123', 'www.mydomain.com', 'www.mydomain.com/sub1'); insert access (id, accessdate, ip, referer, landpage) values (5, '2015-08-26 12:22:24', '123.123.123.123', 'www.google.com', 'www.mydomain.com'); insert removed (ip) values ('123.123.123.123');
i need query return like:
1 2015-08-25 12:22:24 123.123.123.123 www.google.com www.mydomain.com 3 2015-08-25 13:22:24 103.123.123.123 www.google.com www.mydomain.com 4 2015-08-25 13:23:24 103.123.123.123 www.mydomain.com www.mydomain.com/sub1 5 2015-08-26 12:22:24 123.123.123.123 www.google.com www.mydomain.com
basically, show entries ip 103.123.123.123 since isn't in table removed
while showing first entry of ip 123.123.123.123 of each day since in table removed.
is possible?
in addition strawberry's previous answer, there trick work, provided don't care row returned removed ip.
select access.* access left join removed on removed.ip = access.ip group date(accessdate), if(removed.ip null,access.id,removed.ip);
returns:
+------+---------------------+-----------------+------------------+-----------------------+ | id | accessdate | ip | referer | landpage | +------+---------------------+-----------------+------------------+-----------------------+ | 1 | 2015-08-25 12:22:24 | 123.123.123.123 | www.google.com | www.mydomain.com | | 3 | 2015-08-25 13:22:24 | 103.123.123.123 | www.google.com | www.mydomain.com | | 4 | 2015-08-25 13:23:24 | 103.123.123.123 | www.mydomain.com | www.mydomain.com/sub1 | | 5 | 2015-08-26 12:22:24 | 123.123.123.123 | www.google.com | www.mydomain.com | +------+---------------------+-----------------+------------------+-----------------------+
explanation: first rows grouped date (that part pretty obvious). rows have removed ip grouped ip (to keep 1 row each) while others left untouched (grouped unique field = no grouping happening).
again, trick works if not care row being returned removed ip.
Comments
Post a Comment