-1

I'd like to clone all the rows that have YEAR=2020, and assign the GAMECODE field to be the value generated by the php function.

However at the moment that gamecode is only generated once so I am getting a "Duplicate entry" error.

$number = generateGameCode();   

$sql = "INSERT INTO games(gamecode, year, rounds)
        SELECT '".$number."', year, rounds
        FROM games
        WHERE year = 2020";

How do I assign a unique newly generated game code for each matching row?

Thanks in advance!

UPDATE

I have to use my php function to generate the value. I cannot just set GAMECODE to auto_increment number in the db.

onlybarca6
  • 35
  • 4
  • You can't call a php function from within a SQL query. You can either read and update rows one by one in php (which is inefficient), or rebuild your php function as a SQL function or stored procedure. – GMB Sep 18 '20 at 21:53

1 Answers1

0

You don't. You let the database do that work. This requires restructuring the table. But the table should look like:

create table games (
    gamecode int auto_increment primary key,
    year int,
    rounds int
);

Then, you don't insert the gamecode (which I would call gameid but you can call it whatever you want). It is generated automatically. Your query looks like:

insert into games (year, round)
    select year, round
    from games
    where year = 2020;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786