powerhouse has asked for the wisdom of the Perl Monks concerning the following question:

I am building a complex system. it's actually ONE script(index.cgi) that handles everything with the help of one Custom made module that handles the MySQL connection, errors and such.

Each page that is called is a configuration file, stored in a directory not accessible via the web.

Those files use the global database connection called in the custom module.

I store tons of different tables in the one database. Each serves it's own purpose.

Now I've added a custom made affiliate program to it. The reason I built my own, is because I don't want to take someone elses and re-write it into my system.

The problem I'm faced with, is this...

If I store the "banner" or "ad" impressions in the MySQL db, and say it's 25 in the impressions record, is there a MySQL command that will just ADD ONE to the current value? Or do I have to call it in one field, store the variable until the next line, and then increment it, then write it back?

If so, what if in the unlikely chance two people access it at the same EXACT same time? would it count both as only one?

ie They both get 25 and add one, then write back to it 26.

Is there an easier way?

thx,
Richard

Replies are listed 'Best First'.
Re: MySQL "Counter"?
by Heidegger (Hermit) on Feb 04, 2003 at 07:06 UTC
    You don't need to use a special MySQL command. You can use an SQL statement to increment a field in a table like this: 
    UPDATE mytable SET myfield = myfield + 1;

    [download]
    According to SQL standard, such a statement is atomic. No two scripts could intercept each other calls and increment it only once. P.S. When programming databases, try not to use any database-dependent functions. Try to be portable.
[reply]
[d/l]

      If you need to know what is the value after the update, do as follow:

      $dbh->do('UPDATE mytable SET myfield = LAST_INSERT_ID(myfield + 1)');
($myfield) = $dbh->selectrow_array('SELECT LAST_INSERT_ID()');

      [download]

      This will work only with MySQL.

      Ciao, Valerio

[reply]
[d/l]
Re: MySQL "Counter"?
by blokhead (Monsignor) on Feb 04, 2003 at 07:06 UTC
    How about UPDATE impressions SET count = count+1 WHERE id=? .. I would guess this is as atomic as you can get with SQL. Forgive me if my SQL is invalid, it's a little late at night, but you get the point.

    I can't say that I'm 100% sure if this statement is guaranteed to be atomic, and if I were really concerned about that, I would approach the problem differently:

    • Create a table for timestamped impression transactions. Every impression gets a single row in this table with relevant data plus a timestamp. This will be a volatile and dynamic table, with a lot of update and insert activity.
    • Have a batch job run every 5 to 10 minutes. It should collect (then delete) all the transactions stamped before the current time. It calculates the totals and adds them appropriately to your existing table that keeps track of total impressions. This batch job does not run concurrently with anything else, and it should be the only process that modifies the table with the totals. There aren't any race conditions, because if anything is added to the transaction table between the time the batch job reads and deletes, the timestamp of the new record will not be in the affected range.

    blokhead

[reply]
[d/l]
      I love your suggestion. I even am implementing it. Thank you. I do have a few questions. I guess it would be more of a "design" question though, I think. What do you think?

      Here are the questions... More of can I please have your "opinions".

      I created a table with an auto incrementing `rid` field(record id) and a `id` field(affiliate id) plus two other fields, one is for the (banner|text ad) id and the last one is for the type of "update" (impression, hit or sale (enum 'i','h','s')).

      I'm kind of lost though. I will setup a cron job to run every half hour to take the data and remove it from the table to put it in another.

      What should I do with it? I mean, should I create a "table" for EACH affiliate? Then store the data in that table? That would amount to a lot of affiliates if I play our cards right(we are going to pay them lots of money (recurring too)). So say I ended up doing what one of my competitors is doing, they have 8000 affiliates. If I even get half that, and I had one table for each affiliate, that would be a HUGE database, right? So, should I scrap the database, and use Flat File databases? What would work better a bigger Database or a just more disk space taken?

      I love having the database, it's much easier to maintain.

      Plus, I would like to keep it where they can see how Each different "banner" or "text" ad did, so they can see the performance of each. If I just add it to a small database, it would only maintain the OVERALL picture, not show how each different one did.
      Does that matter?, should that matter?

      I'm confusing myself :o)

      If you will comment on that. If nothing else, please just answer these few questions. Do Databases that have thousands of "tables" run pretty slow?
      Are Flat File Databases FASTER then MySQL? (non mod_perl)

      Well, I've said too much. Sorry. My brain is having way to many brain 'farts' :o), I am confusing myself. I guess I need to take a few days off work, It's been at least 8 months since I've taken a full day off. I guess that is the bad thing about working at home, your always at work...

      Thank you for any tips||wisdom you can share.

      Richard.

      Geeze, I guess I do need a break. I thought I submitted this, I've been looking every where for any response for 5 hours:'(! It was hidden in one of my about 30 windows open....
[reply]
Re: MySQL "Counter"?
by dws (Chancellor) on Feb 04, 2003 at 07:33 UTC
    do I have to call it in one field, store the variable until the next line, and then increment it, then write it back?

    Can you use an AUTO_INCREMENT field to assign IDs? If so, all you need to do to retrieve the value of the ID that you just assigned (by inserting into a table) is 

      SELECT LAST_INSERT_ID();

    [download]
    Check the MySQL docs for details.

[reply]
[d/l]

Back to Seekers of Perl Wisdom