PHP-MySQL-How to safely increment MySQL integer field?

Question

I wan to increment a field value safely using php and mysql.

1. what type of table/field must I use?

2. is there a minimum version of MySQL I must use?

3. what's the sql code for this, safe transaction for MySQL?

Answer 1

If you are talking about primary key then set id column as primary and auto_increment.

Increasing field is looking like that:

UPDATE table SET field = field + 1 WHERE id = 9

About MySQL version - use the newest you can. ;)
> 5.0 will be fine.

Answer 2

By what type of "table" I assume you mean storage engine. Anything that supports mutations (i.e. not "archive" or "black hole")

Any numeric field will do (tinyint, int, float, etc). That said, there's no special PHP code, just the SQL for incrementing the desired field:

UPDATE table SET field = field + 1 WHERE [...]

If you want a transaction, then pack the above query into a transaction. As for MySQL version, I agree with @hsz - use the most current version possible.

Answer 3

1.what type of table/field must I use?

--> The type of table depends on what you have planned for your application. It could be Innodb or Myisam. I suggest you to use numeric column so that you can increment/decrement them. Do NOT make it UNSIGNED if you plan to allow negative numbers.

Here's the limits that you might find useful in declaring your column length:

TINYINT (length)  - 1 - Integer with unsigned range of 0-255 and a signed range from -128-127
SMALLINT (length)  - 2 - Integer with unsigned range of 0-65535 and a signed range from -32768-32767
MEDIUMINT(length)  - 3 -  Integer with unsigned range of 0-16777215 and a signed range from -8388608-8388607
INT(length)   - 4 - Integer with unsigned range of 0-429467295 and a signed range from -2147483648-2147483647
BIGINT(length)   - 8 -  Integer with unsigned range of 0-18446744 and a signed range from
-9223372036854775808-9223372036854775807

2.is there a minimum version of MySQL I must use?

--> Just to use auto-increment? You are well off using an up to date version. I suggest something > 5.2.4, if it's possible.

3.what's the sql code for this, safe transaction for MySQL?

--> Sorry, don't have an answer for this at the moment.