Hello! Prompt, I have a certain variable i = 1; how to put the value of this variable in the PHPMySQL table so that each time the value of this variable increases by 1 in the table. For example, at first it was 1, I write it in the database, then I write 1 more so that the table already has 2, and so on. Is it even possible to do that?

  • what are you writing to? - splash58
  • I write down a variable in a DB. I only need this variable to be incremented by 1 each time while writing to the database. Can there be any request to do so? - Vasily
  • for example, set i = i + 1. You can make auto-increment field. you can write a trigger on the update event / write to the table .... no task - splash58

1 answer 1

MySQL has a regular AUTO_INCREMENT mechanism for solving this problem. However, it has a limitation, you can only provide this attribute with one column, with a unique index. This is usually the primary key. To solve the problem, triggers are often resorted to - special events that are triggered before or after performing INSERT , UPDATE , DELETE operations.

For definiteness, let there be a table tbl with a value column that takes integer values. In this case, we can create an insert trigger ( INSERT ) that will assign value maximum value within the table, incremented by one.

 CREATE TRIGGER `table_increment` BEFORE INSERT ON tbl FOR EACH ROW BEGIN SET NEW.`value` = (SELECT MAX(`value`) + 1 FROM tbl); END//

It is enough to insert into the tbl table tbl entry with the value of your variable in the value field and in all new inserts (where this value can no longer be specified), the value field will automatically receive the following values.

phpMyAdmin allows multiple requests to be executed at once. In order for the analyzer to be able to separate them from the trigger, which has semicolons in its composition, you need to change the sign of the end of the request (we have this //). In phpMyAdmin, this can be done in the Separator field. In the console, a special DELIMITER command is used for this.

 DELIMITER //