Logging user's time viewing a page

[shartken]

I am setting up a sight where users pay for time to view a live video stream. I am having a little trouble setting up how to log the user's time viewing a page, and then kick them out when they're time has expired if they are still viewing that page.

I was thinking of making a two tables, one called IN and one called OUT. It will have the user ID, date, and time.

I don't know how to update the OUT table when a user leaves the page, since it is hard to pass to the server when a browser window is closed or when the page is left.

One idea I had was to run an infinite for loop, using a META refresh command that updates a record every minute or so. I am also using flash, so I could make an imbedded movie that loops every minute to update the OUT table.

If anyone did this before, please let me know what the correct way to do this would be.

[kram 2.0]

It may be only possible to make this with Flash or something like Java...thinking right off the top of my head, it would be rather hard to keep count on the server side (because Client side they control) and still let it be accountable.

kram

[shartken]

I think you are right. You have to do this on the client side. I tried implementing this and found using two tables very difficult, because you have to match up which OUT time goes to which IN time. I just combined them into one table that looks like this:

Userid IN OUT TIMESTAMP ID(index)

I have allow nulls set for OUT, and when the user enters the website, a PHP script sets the userid, and in, fields so it looks like this:

userid IN OUT TIMESTAMP ID
2 2004-1113-160933 2004-1113-160933 1

This script also saves the ID of the above record in a session variable.
Then I have a flash object run a PHP script called which runs an update query that updates the OUT field every minute based on the ID stored in the session.

It will then have to run a GROUP_BY (userid) query of this table which will have a calculated field total_time AS SUM(out-in) WHERE 'userid=$current_userid'. This will create a value for total_time_used.

Then the PHP script will compare the total_time_used with the total_time_bought. If the total_time_used is greater than total_time_bought, it will bring them to a new page. If not, it will exit the script.

I don't know what type of load this will put on the server. I expect a maximum of 10 simultaneous page viewers, so the server will run this 2 query script, producing 20 queries a minute max, which isn't too bad.

I surprised that this service hasn't been developed before, with all the adult websites out there

Comments, anyone?

[shartken]

Oh, yeah. Once I have this developed, I will put it on my website. Open sourced all the way!!!