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

Hello:

I create a CGI to administrate news items, I've created the database on MySQL. Each of the news items has a timestamp field to know the date and time each of the news where submited.

When a user sees a news item I run a function that adds the number of views that item has: 

sub add_views{

my $id = shift;
my $current_views = shift;

   my $new_views = $current_views + 1;

my($dbh,$sth);
$dbh = BaboonDB->connect();
   $sth = $dbh->prepare('UPDATE news SET views=? WHERE id=?') or die("
+Couldn't prepare statement: " . $dbh->errstr);
   $sth->execute($new_views,$id) or die("Couldn't execute statement: "
+ . $sth->errstr);
   $sth->finish;
$dbh->disconnect;

return($new_views);

}

[download]
The code runs correctly and adds the views without a problem. The thing is that when I update the views field, the timestamp field is also updated.

What I would like to know if there is a way to avoid this?

THANKS

Replies are listed 'Best First'.
Re: Bug on MySQL? timestamp problem
by antirice (Priest) on Aug 11, 2003 at 23:11 UTC

    Straight from the MySQL manual:

    The TIMESTAMP column type provides a type that you can use to 
automatically mark INSERT or UPDATE operations with the current date
and time. If you have multiple TIMESTAMP columns, only the first 
one is updated automatically. 

Automatic updating of the first TIMESTAMP column occurs under any 
of the following conditions: 

1. The column is not specified explicitly in an INSERT or 
   LOAD DATA INFILE statement. 
2. The column is not specified explicitly in an UPDATE 
   statement and some other column changes value. (Note 
   that an UPDATE that sets a column to the value it 
   already has will not cause the TIMESTAMP column to be 
   updated, because if you set a column to its current 
   value, MySQL ignores the update for efficiency.) 
3. You explicitly set the TIMESTAMP column to NULL.

    [download]

    Try using a DATETIME type and when you insert pass it a value of NOW(). Do not use a placeholder for this particular column or it will attempt to pass NOW() as a string. Also, you can do set timestamp_field=timestamp_field in your update query if you don't wish to alter the table structure.

    Hope this helps.

    antirice    
    The first rule of Perl club is - use Perl
    The     ith rule of Perl club is - follow rule i - 1 for i > 1

[reply]
[d/l]
Re: Bug on MySQL? timestamp problem
by liz (Monsignor) on Aug 11, 2003 at 23:12 UTC
    Hmmm... not really a Perl question.

    Nonetheless, isn't it a MySQL feature that the first timestamp field in a record that is not specified in an UPDATE (or has the value NULL specified) is automagically updated with the current timestamp?

    I usually have a timestamp field named "updated" as the first timestamp field in a table layout just for that reason.

    You can avoid this behaviour by adding (assuming your field name is "datum") datum=datum to your UPDATE statement. Looks funny, but it works for me.

    Hope this helps.

    Liz

[reply]
[d/l]
Re: Bug on MySQL? timestamp problem
by Anonymous Monk on Aug 11, 2003 at 23:28 UTC

    In your homenode you say that you have Paul Dubois's books (MySQL and MySQL and Perl for the web)

    Both books explain in great detail how MySQL deals with TIMESTAMP fields.

    Don't be shy. Take those books out of your shelves and actually read them.

[reply]
      He owns four Perl books, none of which are the Camel. God help him...
[reply]
Re: Bug on MySQL? timestamp problem
by chromatic (Archbishop) on Aug 12, 2003 at 01:15 UTC

    Another option is to create a separate table to store the views. By the way, it's usually better to let the database handle the increment. That way, you avoid race conditions.

    UPDATE news_views SET views = views + 1 WHERE news_id = ?
[reply]
[d/l]
Re: Bug on MySQL? timestamp problem
by CountZero (Bishop) on Aug 12, 2003 at 05:48 UTC

    Something else entirely.

    From your code I gather that you must have first SELECT-ed the number of views this news-item had before ($current_views). Then your sub updates (increments) the number of views.

    However, you seem to re-connect to the database ($dbh = BaboonDB->connect()) to do this, although presumably you just made a connection (to get the current number of views of this news-item). Did you disconnect from the database after getting the number of views? I don't think it is necessary to disconnect and re-connect at such short intervals as connecting is a fairly "expensive" operation and it will slow your script down.

    My suggestion is to do one connect at the beginning of the script and one at the end. Or even (if you are using an Apache server), to use Apache::DBI to pool/cache your connects.

    CountZero

    "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom