MYSQLI Prepared statement update statement with where in array

Question

I managed to create a prepared statement that updates the hardcoded values within a SQL database table using mysqli prepared statement with the Where IN statement which is containing an Array..

I need to update the stats_accid column of table resetstats with the value $arrayresetid from table account based on the fetched names in variable $arrayresetname which are store in the username column of the table resetstats.

Here is my code:

if ($totalacc > 0) {
    $in7 = str_repeat('?,', count($arrayidc) - 1) . '?';
    $types7 = str_repeat('s', count($arrayidc));
    $sql7 = "SELECT email,name,group_name,id FROM account WHERE name IN ($in7) AND NOT group_name = 'test'";
    $stmt7 = $mysqli->prepare($sql7);
    $stmt7->bind_param($types7, ...$arrayidc);
    $stmt7->execute();
    $result7 = $stmt7->get_result();
    while ($rowid7 = $result7->fetch_assoc()) {
        $arrayresetid = $rowid7['id'];
        $arrayresetname = $rowid7['name'];
        $arrayresetnamereplace = str_replace(" (resetted)", "", $arrayresetname);
        $arraynamereset[] = $arrayresetnamereplace;
    }

    if ($stmt7 == true) {
        $in9 = str_repeat('?,', count($arraynamereset) - 1) . '?';
        $sql9 = "UPDATE resetstats SET statsfriendly = 1,stats_accid = ? WHERE username IN ($in9) ";
        $stmt9 = $mysqli->prepare($sql9);
        $arraynamereset[] = $arrayresetid;
        $stmt9->bind_param(str_repeat('s', count($arraynamereset)), ...$arraynamereset);
        $stmt9->execute();
        $stmt9->close();
    }
}

Example data table account:

id | name
865 | test name a
876 | test name b
888 | test name c

example data table resetstats (before update !):

statsid | username | statsfriendly | stats_accid
2 | test name a | 0 | 0
3 | test name b | 0 | 0
4 | test name c | 0 | 0

example data table resetstats (Exptected output AFTER update !):

statsid | username | statsfriendly | stats_accid
2 | test name a | 1 | 865 
3 | test name b | 1 | 876 
4 | test name c | 1 | 888

Answer 1

You should do this with a single query.

UPDATE resetstats AS r
JOIN account AS a ON r.username = REPLACE(a.name, ' (resetted)', '')
SET r.statsfriendly = 1, r.stats_id = a.id
WHERE a.name IN ($in7)