Logging custom data to the DB

Question

I'm working on a film site and need to log some custom information about a user's activity. On the site you can click a play button to start watching a film. (This link just takes you to Amazon Instant.) When a logged-in user clicks to start watching the film, I need to:

1. Set a "watching" flag in their account denoting that they started watching film_id at 2:57PM;
2. Next time they load a page on the site I need to check to see if they're "watching" any films;
3. If they are "watching" a film I need to display a modal that asks them to rate the film.

I know EE allows you to run queries as you see fit. Wondering if there is a recommended way to log and retrieve data that doesn't necessarily exist in the EE interface?

Answer 1

I would suggest you to create an extension/module hooked with "sessions_start". With the extension installation create a database table having following columns: - film_id (integer) - Member ID (integer) - start_time (datetime: UNIX timestamp) - film_length (in seconds)

Now you need to do following process: - When member started watching that film (i.e clicked on play button), insert new record in this database table - On page refresh, check the current time > start_time + film_length. remove that record accordingly. This would be checked if page is refreshed by any other member.

There may be case if your member has watched the film but still didn't refresh the page. In this case, this comparison will process.

• Now with the SQL you can fetch which member watching which film. For it, you can create module tags also.

I hope, this would help you.

Answer 2

You might save yourself quite a bit of trouble by adding the watching flag as a custom field in their member account and retrieving it through the appropriate tag. On the other hand, there's nothing inherently wrong with doing it the way you have specced.

Membership Tags (EE Docs): http://ellislab.com/expressionengine/user-guide/modules/member/#custom-profile-data-tag