Hi all,
This is my first post on MySQL.
Wondering if someone could help me with an sql statement
MySQL version 4.0.18
I have three tables:
1. visitor_data (Visitor_ID, IP Address)
2. visit_data (Visit_ID, Visitor_ID)
3. page_data (Page_ID, Visit_ID, Visitor_ID, URI, Page_Time)
If you look closely you can see the relationship in these tables and you
might guess I'm tracking what a user is doing on this site.
visitor_data obviously records only unique visitors, I'm collecting a few
other things as well as what I've written, such as employing a cookie,
tracking their current local time, etc to build an effective method of
knowing who is coming back.
visit_data records each visit to the site and references from the
visitor_data.Visitor_ID. This is only done once a session.
page_data records every move the user makes.
I need to pull data out of these tables to make a graphical table
displaying: Visitor ID, Entry Page, Exit Page and Session Duration. There
will be one row per visit.
a Statement like:
SELECT *
FROM visitor_data, visit_data, page_data
WHERE visitor_data.Visitor_ID = visit_data.Visitor_ID
AND visit_data.Page_ID = page_data.Page_ID
GROUP BY visit_data.Page_ID
would return a all the rows, where I want to limit the pages that appear
first and last in each visit by getting the minimum and maximum Page_Time,
which incidentally is a UNIX timestamp.
So far I've made multiple statements pulling out all the data and using PHP
to formulate the information. But I know there is a better way using mysql.
Only thing is I haven't got the faintest idea how to do it in one query and
keep overheads low. I start to get lost when I'm doing select statements
within select statements.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
