I want to count the number of times each record is selected by a mysql query . . .

here's the scenario, the site carries property listing, both free and paid, I want to increment the number of views that each record is viewed as it is selected - if possible.

I think that would be more efficient than having a SELECT followed by an UPDATE that found the same records and incremented a field.

Any suggestions ?

You want to do all this just by using sql or by using a server side script?

Hi,

yes, if possible I would like to do it in one query, but I've looked around at sub-queries and I don't think it will work as my understanding of sub-queries is that they can't do an UPDATE.

The coding is mostly server-side, I started off with dw, but found that I needed to hand-code some of the more 'interesting' parts. The idea is to be able to tell advertisers how many times their entries have been shown so they know whether they are getting value for money - that could be a two-edged sword! If they don't get many views then they might not renew.

My current plan is to have two queries, the first will perform the SELECT that will show the records that the user wants to see, then I'll have another almost identical query that finds the same records and updates the viewed count. Sounds messy to me but at the moment it's the best idea I have.

I have got distracted a little sorting out another problem but I'll be back to this is a few days.