Hi,
I have the following tables in my database for proxy logs. I have broken down the URL for each line into the following 4 tables:
url_schemes e.g. http url_servers e.g. www.google.com url_paths e.g. /search url_queries e.g q="searchstring"
To get a list of the websites a user has surfed I run the following SQL:
SELECT iu.time as time, INET_NTOA(iu.ip) as ip, concat(usc.scheme,"://",us.server,up.path) as url,uc.category as category,ua.useragent as useragent
FROM internet_usage iu
INNER JOIN url_visit uv
ON iu.urlid=uv.urlid
INNER JOIN url_servers us
ON us.id=uv.url_server_ID
INNER JOIN url_paths up
ON up.id=uv.url_path_ID
INNER JOIN url_queries uq
ON uq.id=uv.url_query_ID
INNER JOIN url_schemes usc
ON usc.id=uv.url_scheme_ID
INNER JOIN url_categories uc
ON uc.id=uv.url_category_ID
INNER JOIN user_agents ua
ON iu.useragent_ID=ua.ID
WHERE iu.uid="u111111"
ORDER BY iu.time
This works fine but the problem is that some URLs have queries so I need to do
SELECT iu.time as time, INET_NTOA(iu.ip) as ip
concat(usc.scheme,"://",us.server,up.path) as url
OR
concat(usc.scheme,"://",us.server,up.path,"?",uq.query) as url,ua.useragent as useragent FROM ....
WHERE iu.uid="u111111" AND uq.query<>" ";
Can anyone tell me how to do this please? Or if this doesn't make sense, let me know and I'll send some more information.
Thanks to the list as always.
js.
_________________________________________________________________
Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
