I am working on movie database in php. I have a large form in which the user can add cast and crew names into a table called tbl_cast_person and tbl_crew_person. When a duplicate name is met and discovered by UNIQUE keyword in a table I store theese duplicate values in a single table called tbl_dup_actor. I then try to update this tbl_dup_actor-table with a counter so the output should look like below

George Henry-2
George Henry-3
George Henry-4
Matthew Fox-2
Matthew Fox-3
Matthew Fox-4

I can only get it to work so it looks like the output below this line

George Henry-2
George Henry-3
George Henry-4
Matthew Fox-5
Matthew Fox-6
Matthew Fox-7

My loop for updating the the table tbl_dup_actor looks at the moment like this

while ($query = $select_stmt->fetch(PDO::FETCH_ASSOC)) {
                
    $counter  ;
                
    $id = $query["id"];
    $name=$query["person_name"];                                                            
    $person = $name . '-' . $counter;
                                                                        
    $update_stmt->bindValue(":person_name",
    $person,PDO::PARAM_STR);
                    
    $update_stmt->bindValue(":number",$id,PDO::PARAM_INT);

  }

  $update_stmt->execute();
                            
}

My update sql looks like this

UPDATE tbl_dup_actor SET person_name = :person_name WHERE id = :number

My question is how can I make the counter reset back to 2 each time when a new value is met in the select loop in which I do the updating ? I hope I have described my problem well to some extent anyway. Many Thanks for viewing my post. I know I am a little unclear about one or two things but I have tried my best to be clear here.

CodePudding user response：

you can do this directly in MySQL, but it relies on that he table has a unique identifier. In the example it is id, but can be every other UNIQUE column

Update To extend my answer:

the Query

SELECT u1.name, CONCAT(u1.name ..

Showas in the result what the CONCAT in combination with ROW_NUMBER makes. the result is shown in the column newname in the result set below

So to update the table, you need to run an UPDATE that is a bit tricky so i suppluied it as well.

Caution

Before you UPDATE any table in a production test it extensively in an test enviroment.

And even before udpating your live tables, first make a complete Backup and test if ou can restore it to the original state if something happens.

CREATE TABLE usermeta
    (id MEDIUMINT NOT NULL AUTO_INCREMENT,
    `name` varchar(100)
    ,
     PRIMARY KEY (id))
;
    
INSERT INTO usermeta
    (`name`)
VALUES
    ('George Henry'),
    ('George Henry'),
    ('George Henry'),
    ('Matthew Fox'),
    ('Matthew Fox'),
    ('Matthew Fox'),
    ('Aston Martin')
;

SELECT
u1.`name`, CONCAT(u1.`name`,'-',ROW_NUMBER() OVER(PARTITION BY u1.`name` ORDER BY u1.`id`)   1) newname
FROM 
usermeta u1 JOIN (SELECT `name` , COUNT(*) FROM usermeta GROUP BY `name` HAVING COUNT(*) > 1) u2
ON u1.`name` = u2.`name`

name         | newname       
:----------- | :-------------
George Henry | George Henry-2
George Henry | George Henry-3
George Henry | George Henry-4
Matthew Fox  | Matthew Fox-2 
Matthew Fox  | Matthew Fox-3 
Matthew Fox  | Matthew Fox-4

UPDATE usermeta u1,
(SELECT
u1.`id`, CONCAT(u1.`name`,'-',ROW_NUMBER() OVER(PARTITION BY u1.`name` ORDER BY u1.`id`)   1) newname
FROM 
usermeta u1 JOIN (SELECT `name` , COUNT(*) FROM usermeta GROUP BY `name` HAVING COUNT(*) > 1) u2
ON u1.`name` = u2.`name` ) u3
SET u1.`name` = u3.`newname`
WHERE u1.`id` = u3.`id`

SELECT * FROM usermeta

id | name          
-: | :-------------
 1 | George Henry-2
 2 | George Henry-3
 3 | George Henry-4
 4 | Matthew Fox-2 
 5 | Matthew Fox-3 
 6 | Matthew Fox-4 
 7 | Aston Martin

db<>fiddle here

CodePudding user response：

First off, I changed the database design. I normalised your tables as follows:

persons (id, firstname, lastname)
cast (id, persons.id)
crew (id, persons.id)

CREATE TABLE persons (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, firstname varchar(255) NOT NULL, lastname varchar(255) NOT NULL);

CREATE TABLE cast (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, person INT NOT NULL, CONSTRAINT `fk_cast_person_id` FOREIGN KEY (person) REFERENCES persons (id) ON DELETE CASCADE ON UPDATE CASCADE);

CREATE TABLE crew (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, person INT NOT NULL, CONSTRAINT `fk_crew_person_id` FOREIGN KEY (person) REFERENCES persons (id) ON DELETE CASCADE ON UPDATE CASCADE);

INSERT INTO persons (firstname, lastname) VALUES ('Anton', 'Arabella'), ('Anton', 'Arabella'), ('Anton', 'Arabella'), ('Bruno', 'Banane'), ('Caroline', 'Champagner'), ('Bruno', 'Banane'), ('Bruno', 'Banane'), ('Bruno', 'Banane');

INSERT INTO cast (person) VALUES (1), (2), (3), (7), (8);
INSERT INTO crew (person) VALUES (4), (5), (6);

Also, I have my SQL query handle the counters for the duplicate entries.

-- Replace the JOIN operand as needed

SELECT ROW_NUMBER() OVER (PARTITION BY p.firstname, p.lastname ORDER BY p.firstname ASC, p.lastname ASC) id, p.firstname, p.lastname
  FROM persons p
  JOIN (SELECT firstname, lastname -- get the duplicate names
          FROM persons
          JOIN cast ON persons.id = cast.person
      GROUP BY firstname, lastname
        HAVING COUNT(firstname) > 1) duplicates ON p.firstname = duplicates.firstname AND p.lastname = duplicates.lastname
   JOIN cast ON p.id = cast.person;

As you can see, my approach is quite different. The output also includes the very first pair of (firstname, lastname) indicated by the id = 1.

Sample output:

 ---- ----------- ---------- 
| id | firstname | lastname |
 ---- ----------- ---------- 
|  1 | Anton     | Arabella |
|  2 | Anton     | Arabella |
|  3 | Anton     | Arabella |
|  1 | Bruno     | Banane   |
|  2 | Bruno     | Banane   |
 ---- ----------- ----------

Cheers