Increment int value in MySql

Question

I am trying to get a value of an int from a field in a MySql database and increment it by 1 when a new record is added. What is happening, is that when the record is inserted it is placing a 1 and not adding one to the value of the field. For example, in the last record, the value is 10 so after running the query the value should be 11.

I am struggling to see why this is not working and would be grateful if someone could offer any advice as to how to amend my code to a working solution. Many thanks

php code

function get_ref(){
     $query = "SELECT MAX(`id_usr`) AS `max` FROM `user_usr`";
     $result = mysql_query($query) or die(mysql_error());
     $row = mysql_fetch_assoc($result);
     $max = $row['max'];    

    $sql = 'select idcom_usr'           
    . ' from user_usr'
    . " where id_usr = '$max'"
    . ' order '
    . ' by id desc'
    . " limit 1";
    $result = mysql_query($sql);
    $ref = mysql_result($result,0,"idcom_usr");
    return $ref + 1;

    }

Have you considered using an auto increment field in the database instead of solving your problem in code? — Micha Wiedenmann, Jun 07 '13 at 09:27
I assume you've considered making the column auto-increment, and have decided against that for some reason? — dKen, Jun 07 '13 at 09:27
[**Please, don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — Madara's Ghost, Jun 07 '13 at 09:28
the id_usr is an auto-inc and primary key. I was under the impression that there can only be 1 auto-inc in a table. Thanks — user1532468, Jun 07 '13 at 09:38
If you post some sample data and explain on it what you're trying to achieve that might help you to get your answer faster — peterm, Jun 07 '13 at 09:39
peterm I thought that the question was explained correctly. But here goes. Very simply, there are 2 fields. First field is id and is auto-inc. The second field is an INT and contains just a number. Lets say 10. When I add a new record, I need to increment the value of the second field so 10 becomes 11. So I thought thast by getting the value of the ID I could then get this second value and just increment it by +1 as in my code. Thanks — user1532468, Jun 07 '13 at 09:42
@user1532468 - I realize you want to increment a number. However, what is the *end* goal of your feature. If you increment this number via php then you will create such a hole in the feature you're coding because you have to take concurrency into account - what happens if 2 people obtain the same result for MAX(usr_id)? Both increment the number and both send the same number to the MySQL. Also, you can have only one auto_increment. — N.B., Jun 07 '13 at 09:42
N.B Thanks. What would be the correct way to go. Please see my comment to peterm — user1532468, Jun 07 '13 at 09:43
After reading the last comment you made, this looks like a job for an after insert trigger. You should implement it as a pure MySQL solution without having any php in it. — N.B., Jun 07 '13 at 09:44
N.B How would I implement a trigger. That is not something I am familiar with. Thanks — user1532468, Jun 07 '13 at 09:49
@user1532468 - google is your friend in this case, there are many tutorials and examples on that topic — N.B., Jun 07 '13 at 09:50
@user1532468 Post your table structure and sample data (a few rows) and we can help — peterm, Jun 07 '13 at 09:51
@peterm I cannot post any client data. How is the best way to post structure. — user1532468, Jun 07 '13 at 10:06
@user1532468 Then make fake data. The best way is to create [sqlfiddle](http://sqlfiddle.com). Otherwise post `CREATE TABLE` statement and comma delimited data in your original question. — peterm, Jun 07 '13 at 10:11
@peterm I have created sample at http://sqlfiddle.com/#!2/9e3e8/6 but I have never used before and get error. anyway, data is there. thanks — user1532468, Jun 07 '13 at 10:46

score 3 · Answer 1 · edited May 25 '19 at 11:53

You can have the column auto incrementing. This way you offload the work from PHP to MySQL, which is always a good thing.

Oh yeah, and Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

peterm · Accepted Answer · 2013-06-07T11:15:46.877

You can try to achieve your goal using a trigger

CREATE TRIGGER tg_user_usr_insert
BEFORE INSERT ON user_usr
FOR EACH ROW
    SET NEW.idcom_usr = (SELECT COALESCE(MAX(idcom_usr), 0) + 1 FROM user_usr);

Then when you insert new rows

INSERT INTO user_usr (idcom_usr) VALUES (0),(0);

Values for idcom_usr will be assigned by the trigger.

Here is SQLFiddle demo.

Even if for some reason you do it in php instead of two queries you need only one

SELECT COALESCE(MAX(idcom_usr), 0) + 1 next_value 
  FROM user_usr

Note: this approach is prone to errors under heavy load due to concurrency.

Increment int value in MySql

2 Answers2