Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    Regular Coder Custard7A's Avatar
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    286
    Thanks
    32
    Thanked 33 Times in 33 Posts

    Native Integer Incrementing (Existing Value)

    Hi guys, I have a rather simple issue. I have a SMALLINT column, and I want this value to rise by one each-time I use that row (Basically this is recording the number of views on that row).
    At the moment I am doing this (Stripped-down for the example):

    PHP Code:

     
    // After selecting the row I want, with an associative array.

     
    $new_value = ++$result['views']; // Increment with php, and call an UPDATE..

     
    $mysqli->query("UPDATE table_name SET views = $new_value WHERE row_id = $variable"); 
    This seems like something might exists that can do this within MySQL. So, my questions would be..

    1. Is there a way to increment an existing column without specifically calling UPDATE on it?

    .. else..

    2. Is there a way to increment it with SQL, so I can just do this in the query?

  • #2
    UE Antagonizer Fumigator's Avatar
    Join Date
    Dec 2005
    Location
    Utah, USA, Northwestern hemisphere, Earth, Solar System, Milky Way Galaxy, Alpha Quadrant
    Posts
    7,691
    Thanks
    42
    Thanked 637 Times in 625 Posts
    There's nothing like that built-in to my knowledge. Your method is fine.

  • #3
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by Fumigator View Post
    There's nothing like that built-in to my knowledge. Your method is fine.
    Not to mine either. Unlike sequences in oracle (which I'm pretty sure can be applied as I see fit, although I'd still see issues getting it to do it during a select), mysql doesn't have an auto-increment except for insertion id's on surrogate keys.
    Pedant or Guelphdad may be able to verify; a custom procedure should let you get away with doing that. But then you need to execute via call, so I'm not sure if that would be more ideal or not.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    OF COURSE you should use a STORED PROCEDURE!

    Trivial.

    Example:
    Code:
    mysql> select * from foo;
    +-----------+-------+
    | viewcount | name  |
    +-----------+-------+
    |         0 | adam  |
    |         0 | bob   |
    |         0 | candy |
    +-----------+-------+
    
    mysql> delimiter //
    
    mysql> create procedure getAndBumpCount( nm varchar(20) )
        -> begin
        ->     update foo set viewcount = viewcount + 1 where name = nm;
        ->     select * from foo where name = nm;
        -> end;
        -> //
    
    mysql> delimiter ;
    
    mysql> call getAndBumpCount ('candy');
    +-----------+-------+
    | viewcount | name  |
    +-----------+-------+
    |         1 | candy |
    +-----------+-------+
    
    mysql> call getAndBumpCount ('candy');
    +-----------+-------+
    | viewcount | name  |
    +-----------+-------+
    |         2 | candy |
    +-----------+-------+
    
    mysql> call getAndBumpCount('adam');
    +-----------+------+
    | viewcount | name |
    +-----------+------+
    |         1 | adam |
    +-----------+------+
    
    mysql> select * from foo;
    +-----------+-------+
    | viewcount | name  |
    +-----------+-------+
    |         1 | adam  |
    |         0 | bob   |
    |         2 | candy |
    +-----------+-------+
    How hard is that?

    Why do you thing executing via CALL would be a bad thing, FouLu? Most DB experts would tell you that you should do *ALL* work via Stored Procedures and not give the casual DB client permissions to use anything *except* Stored Procs!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    Incidentally, the Stored Procedure will be roughly twice as efficient as making the two separate calls ( SELECT and then UPDATE ) as proposed in the first post.

    That's because your PHP code only needs to make one interprocess invocation of MySQL. People don't realize how very expensive interprocess calls are.

    The actual time spent in the DB is going to be roughly the same. The first query (whether it be the SELECT as first proposed or the UPDATE in my procedure) is going to ensure that the relevant record is fetched from disk to memory and so will be cached in memory for the second operation in both cases. So the disk-to-memory overhead will be the same and you are thus saving one interprocess call. Nothing to be sneezed at.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Regular Coder Custard7A's Avatar
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    286
    Thanks
    32
    Thanked 33 Times in 33 Posts
    Heey, that looks nice. I want to use stored procedures for everything it looks simple!

    update foo set viewcount = viewcount + 1 where..
    That's valid SQL, preforming addition on an integer field?

    Thanks for the tips too. I usually assume everything to do with the database is resource expensive, so things like doing one query instead of two if I can is usually in the back of my mind.

    That last comment about caching, do you mean that — in your example, for example — the select query will use the incremented viewcount from memory of the first line?
    Last edited by Custard7A; 11-16-2012 at 12:13 AM.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    The SELECT would use the incremented value even if caching wasn't involved. SQL guarantees that operations will be done in the order requested.

    The remark about caching has to do with the fact that, like most modern DB engines, MySQL makes use of a *HUGE* in-memory cache. Many many megabytes. (We actually had one system where we "tuned" MySQL to use 75% of available memory. Yes, you can do that. By default, though, it uses a lot less than that.)

    So when you make a query of any kind on a given record, the disk *BLOCK* that contains that record is loaded into memory. And the DB engine kind of assumes that the most recent record fetches are going to be needed again, so it KEEPS that block in memory as long as it can (that is, it caches it).

    In a typical MySQL installation, you can pretty much count on any record that was fetched till being in the cache many seconds or even a minute or two after it was fetched. And clearly if you do an UPDATE immediately followed by a SELECT as I do in the Procedure--or even if you do two separate queries, SELECT followed by UPDATE as in your first post--the record *WILL* still be in memory. So the second operation doesn't involve use of the disk at all. MySQL just remembers where it put that block in its memory cache and ZAM! It's all done in memory.

    (Of course, after an UPDATE, eventually that disk block needs to be written back out to the disk, but MySQL does a lazy update, trying to schedule the writes when no other user-requested operations are taking place at a higher priority.)
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Custard7A (11-16-2012)

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    And of course you can increment a field as shown there.

    You can do a lot more than that:
    Code:
    UPDATE sometable SET field1 = field1 + 1 + IF(field2 > 3, 7, -12 )
    WHERE id = 9102 AND field1 < 1000000
    and so on and so on.

    Heck, you can even "tack on" to text fields:
    Code:
    UPDATE sometable SET ticks = CONCAT( ticks,'*') WHERE id = 1817
    That would make the ticks field grow by one "*" every time the UPDATE is executed.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Custard7A (11-16-2012)

  • #9
    Regular Coder Custard7A's Avatar
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    286
    Thanks
    32
    Thanked 33 Times in 33 Posts
    Great, this should all make my scripts nice and neater! But, I see maybe a problem..

    Can I use PHP variables in stored procedures? I couldn't get them to work in views..

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    26,027
    Thanks
    79
    Thanked 4,436 Times in 4,401 Posts
    You can't use PHP variables *DIRECTLY*, but you can pass them in as parameters.

    For example, using the getAndBumpCount SP that I created, you would invoke it from PHP via:

    Code:
    $sql = "call getAndBumpCount ('$name')";
    $result = mysql_query( $sql );
    You can pass in as many arguments as you wish to a Stored Procedure and then use them by name in the body of the procedure.

    In my SP, I used
    Code:
    create procedure getAndBumpCount( nm varchar(20) )
    so the argument $name would end up in the MySQL variable nm and you can then use nm wherever you want in the SP.

    Some people use a naming convention for parameters. Such as P_NM, P_ID, P_Address, etc. Where the "P_" makes it clear that this variable is a "P"arameter to the stored procedure.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    Regular Coder Custard7A's Avatar
    Join Date
    Jul 2010
    Location
    Australia
    Posts
    286
    Thanks
    32
    Thanked 33 Times in 33 Posts
    Brilliant, OK, now to put it all to good use. Thanks for the expert advice Old Pedant, I feel a little more confident in my database operations now.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •