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

My question is short, how can I update a mysql row ++ (auto-increment a specific field)? It's not the key, either, so I don't think I could add the auto_increment. Correct me if I'm wrong though. 
      my $data = qq(UPDATE now_time SET time= NOW(), location="$ENV{'H
+TTP_REFERER'}", count=... WHERE ip="$userip");
      my $sth = $dbh->prepare($data);
      $sth->execute() or die $dbh->errstr;

[download]
I'm trying to add +1 to the count field without having to do a separate call to the database just to pull in that value. Is there anyway I can do field_name = field_name +1?

Replies are listed 'Best First'.
Re: Updating a mysql row
by imp (Priest) on Feb 22, 2007 at 21:34 UTC
    Yes, do it exactly as you suggested... except you really should use bound parameters to avoid SQL injection attacks. Alternately you can use DBI's quote method

    Something like this: 

    my $sql = q{UPDATE now_time SET time= NOW(), location=?, count=count+1
+ WHERE ip=?};

$dbh->do($sql,{}, $ENV{HTTP_REFERER}, $userip)
  or die $dbh->errstr;

    [download]

[reply]
[d/l]
Re: Updating a mysql row
by Herkum (Parson) on Feb 22, 2007 at 22:41 UTC
    I think the proper reply should be "Did you try it yourself?" You would have known the answer in a minute, which is shorter than the time that it took for you to post the question and then wait for a response.
[reply]
      I absolutely disagree with your response to the OPs question, and thus downvoted you.

      Especially someone unfamiliar with MySQL and the effects of uncertain statements, it's not necessarily better to try before you ask, moreso if they are working with a full database.

      What should happen if it did update their database in such a way they did not intend and ruin their current database? Asking, in my opinion, should never be criticized. I'd sure prefer many professions to ask for help before trying it.

[reply]
        I wouldn't recommend running it on an active table, but testing it in a throwaway table is reasonably easy. From the mysql commandline client (using mysql 4.0 ) 
        mysql> create database pmtest;
Query OK, 1 row affected (0.09 sec)

mysql> use pmtest;
Database changed

mysql> create table t (id smallint, count smallint, time datetime);   
+                  Query OK, 0 rows affected (0.01 sec)

mysql> insert into t values (1,1,now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (2,1,now());
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values (3,1,now());
Query OK, 1 row affected (0.00 sec)

mysql> update t set count=count+1, time=now() where id=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> update t set count=count+1, time=now() where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from t;
+------+-------+---------------------+
| id   | count | time                |
+------+-------+---------------------+
|    1 |     1 | 2007-02-22 19:26:39 |
|    2 |     3 | 2007-02-22 19:27:06 |
|    3 |     1 | 2007-02-22 19:26:45 |
+------+-------+---------------------+
3 rows in set (0.01 sec)

        [download]
[reply]
[d/l]

        There should be no reason that a simple test cannot written to see if your code is working correctly. I don't understand why you think I was advocating that he run any code in production.

        Asking, in my opinion, should never be criticized

        Asking was not the problem, they just needed to check their work. If you have a person who is unwilling to try anything, how will they learn?

[reply]

Back to Seekers of Perl Wisdom