Add record for each array elements if missing in table

Question

I feel like I need a little push to break through this one.

I have this array of players $playerids = ['1', '2', '3', '4']

I have this array of periods $periods = ['March 2022', 'April 2022', 'May 2022']

I have a table like this (order by date_time desc already applied so I can get latest scores)

-----date_time------------period----------playerid-----score
2022-05-06 10:08:12------May 2022-----------3----------1614
2022-05-06 10:08:12------May 2022-----------1----------1545
2022-04-25 16:24:30------April 2022---------3----------1765
2022-04-25 15:32:40------April 2022---------2----------1753
2022-04-24 15:54:50------April 2022---------3----------1545
2022-03-27 06:18:12------March 2022---------4----------1570
2022-03-25 06:16:37------March 2022---------3----------1574
2022-03-24 15:54:50------March 2022---------2----------1753
2022-03-24 15:54:50------March 2022---------1----------1583

My aim is that for each period, each player must have at least 1 record, if not, we look at the latest score for that player from a previous period and we use it to add the score update for the missing period (date can be set to 1st of the month midnight). The sample above should turn into:

-----date_time------------period---------playerid-----score
2022-05-01 00:00:00------May 2022-----------4----------1570<-----new row
2022-05-01 00:00:00------May 2022-----------2----------1753<-----new row
2022-05-06 10:08:12------May 2022-----------3----------1614
2022-05-06 10:08:12------May 2022-----------1----------1545
2022-04-01 00:00:00------April 2022---------4----------1570<-----new row
2022-04-01 00:00:00------April 2022---------1----------1583<-----new row
2022-04-25 16:24:30------April 2022---------3----------1765
2022-04-25 15:32:40------April 2022---------2----------1753
2022-04-24 15:54:50------April 2022---------3----------1545
2022-03-27 06:18:12------March 2022---------4----------1570
2022-03-25 06:16:37------March 2022---------3----------1574
2022-03-24 15:54:50------March 2022---------2----------1753
2022-03-24 15:54:50------March 2022---------1----------1583

I have been thinking about using foreach for each array to check the condition but am afraid there maybe a faster solution. Any pointers are much appreciated.

Answer 1

After 5 days of refusing to let go, I have come up with this :)

Step 1 - Create a reference table with all periods for each player

$reference = $mysqli->query("create temporary table reference (periodid int(3), period varchar(15), playerid int, score int); ");

foreach ($playerids as $kp=>$pl) {
        foreach ($periods as $kd=>$pd) {
        $referenceins = $mysqli->query("insert into reference (periodid, period, playerid) values ('$kd', '$pd', '$pl')");
        }
    }

Step 2 - Using left join with reference and using if to determine a value to use for score for next row

foreach ($playerids as $kp=>$pl) {
    $reference2 = $mysqli->query("select periodid, period, playerid, date_time, @prev := IF(score > 0,score,@prev) AS score
                                from (select ref.periodid as periodid, ref.period as period, ref.playerid as playerid,
                                coalesce(tb.date_time, 'not found') as date_time,
                                coalesce(tb.score, 'not found') as score
                                FROM reference as ref 
                                LEFT JOIN table as tb
                                ON ref.period = tb.period AND ref.playerid = tb.playerid
                                WHERE ref.playerid = $pl
                                ORDER BY playerid asc, periodid asc) temp, (SELECT @prev := null) ini_value
                                ");
}