If there is a “line” entry in the database, insert the “line-1” entry, if there is a “line-1” entry, insert “line-2”?

Question

PHP

Only a loop with a constant comparison of my line and lines from the database comes to mind.
Does anyone have a more elegant solution?
Share your idea
Thank you

Answer 1

I would change the structure a little by adding one extra field and a unique index on the name field ...
In short...
Table:

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `count` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

When inserting records, I would use the following query:

INSERT INTO `test` (`name`) VALUES ('строка') 
ON DUPLICATE KEY 
UPDATE `count` = `count` + 1;

What is the result?
- As a result, there will be no extra volumes of data in the database...
- Only one insert request;
- There will be a number in the count field - how many times this row was entered into the database...
UPDATE:
Based on the author's comments and details, I am supplementing the answer...

You can for example use Redis ( increment )...
Where the key would be "string" (in this case kot for example).
Before recording, we dive into Redis, tweak the "counter" and then take this value and write it to MySQL in the slug field :
kot - COUNTER_VALUE
As a result, one request in Redis and one INSERT in MySQL ...
Do you understand?)
PS: Redis can also be used on Replace MySQL too... using my first option in the answer as a counter instead of Redis... but here it will take a couple of queries more... which is not critical in this case, I think...

Answer 2

From time to time I use the Zelenin\yii\behaviors\Slug extension in Yii to generate a slug, but I never looked "under the hood" how it works there. Now I looked - the author in the cycle adds a number to the generated slug and checks if there is such an entry in the table.