Fairly Simple Question - how to increment view count?

Increment view count

[iamvela]

What is the most efficient way to increase the view count of a record e.g. I want to display the number of times a video has been displayed?

Do I have to do read the view count, increment it for each time the video is selected, and then write it back? Seems rather inefficient. Can I use any kind of built-in auto-increment functioality?

TIA.
--

[wheelie34]

Here's one I use often

UPDATE tablename SET column=column+1

HTH

[iamvela]

That is definitely helpful but. it still means an additional update query every time I do a select on that row?

typically I will have a Select * from table where some_condition

This will give me back a bunch of records, I'd like to increment each of these records.

Thanks again!

[phranque]

do it all in one query:
UPDATE tablename SET column=column+1 where some_condition

[iamvela]

Again VERY HELPFUL (thanks!) but still I don't see how e.g.

UPDATE video_table SET viewed_count=viewed_count+1 WHERE
SELECT * FROM video_table WHERE DATE='today'

But then how will get the results of my select back? Am I being dense? :(

TIA,

[trillianjedi]

This will give me back a bunch of records, I'd like to increment each of these records.

You do not need to read first - the count=count+1 will increment without a read.

I'm not quite sure how you've designed the database scheme, but typically you would have something like the following fields:-

video_file
view_count

Then you would use this, each time the 'play' button is clicked on the video 'thisone.flv':-

UPDATE video_table SET view_count=view_count+1 WHERE video_file = 'thisone.flv';

....and view with:-

SELECT * FROM video_table;

But your post above indicates that you may be looking to get daily stats, eg a video was viewed X times today, in which case the schema and updates will be slightly different.

If you could expand on exactly what it is you're trying to achieve ?

[phranque]

i think made some incorrect assumptions about video_table.
perhaps you could describe it.

maybe you want something more like this:
SELECT COUNT(*) FROM video_table WHERE DATE='today'

[ZydoSEO]

Which DBMS are you using? MySQL? Oracle? SQL*Server? Your options will vary accordingly.

It sounds like a situation where a database trigger should be used. Most DBMSs have 'triggers' which can cause a database action or side effect to be performed anytime a row is inserted, updated, deleted, or selected. You can specify different triggers for each type of action - insert, update, delete, select.

Many DBMSs also have multiple versions of each of theses trigger types like BEFORE/PRE INSERT triggers and AFTER/POST INSERT triggers. For example, you can tell the DMBS that any time an INSERT is requested, perform the BEFORE INSERT trigger. These triggers typically have access to all of the field values about to be 'inserted' so that you can run validation on them or trigger updates to other tables as a side effect. Then if the BEFORE INSERT trigger succeeds, the DBMS will attempt the INSERT. Once the INSERT is done you can perform an AFTER INSERT trigger to do something different like log an audit row in another table stating. We use them for logging InsertDateTime, InsertHost, InsertApplication, UpdateDateTime, UpdateHost, UpdateApplication on most tables. What is cool is you set up the triggers and then forget about them. They are automatically executed each time.

However, you should be aware that you can take a performance hit if you set up too many triggers. And you'll need to remember they are there. There might be times when you will not want the triggers to run (when doing maintenance or manual updates to the table) and you need to know how to handle this.

Sounds like you need a SELECT trigger to update your VIEW_COUNT. It might need to be implemented with a BEFORE or AFTER SELECT trigger depending on your schema and whether your DB supports you getting the list of fields being selected.

For example, you might use a BEFORE TRIGGER as follows. When you first INSERT the row into the table, insert it with VIEW_COUNT=0. A BEFORE SELECT trigger could possibly look at the list of fields you are selecting (depending on the DB and whether this is supported) and any time VIEW_COUNT is in the list of fields to be selected it can UPDATE video_table SET view_count = view_count + 1 where it's the current row about to be selected.

Or you could initialize the VIEW_COUNT field to 1 when the row is first inserted and set up an AFTER SELECT trigger so that after a row is selected from the table, it will increment the VIEW_COUNT field.

If possible I would have the trigger ONLY modify VIEW_COUNT if it is in the list of fields SELECTED. This way you can still query the table yourself manually without incrementing the field value.

Read up on triggers if you are not familiar with them. I typically avoid them where possible because there are performance hits and side effects. But in certain situations like this one, they can make life much simpler and be very useful.

[edited by: ZydoSEO at 1:44 pm (utc) on Aug. 12, 2008]

[iamvela]

Ok, so here I am trying to describe the problem differently to make it simple. As phranque rightly said I should just show you what we have built :)

Users can highlights parts of any web page (think delicious on steroids). When users come and look at a collection of highlights (their own or others' highlights) then I want the view count for the highlight to be incremented: since user has essentially seen the highlight at that point. (unlike having to open a video at a time, the user can view any number of highlights on a page, and once the highlight is displayed I'd like to increment it's view count) I am trying to find an elegant solution for this.

<snip>

Again, thanks in advance!

[edited by: engine at 7:59 am (utc) on Aug. 14, 2008]
[edit reason] No urls, thanks [/edit]

[phranque]

actually i was suggesting that you describe the db schema rather than the application...