How delete large amount of rows efficiently

Question

After read many post about "delete massive amount of rows", I tried several answer for my problem but not work like hope.

Explanation : I've been commendited to transcript a desktop application to a web app. The application have to main goal a update of several sql tables.

When I was working on it, I've been informed to change the truncate method to a delete method. So now we don't do a Truncate and Insert, but a Replace/Insert and Delete all rows not replace/insert into the table.

Here some code :

    $codeliste = "";
    $req2 = "SELECT projection.article.ean, projection.article.artic
    FROM projection.article
    WHERE projection.article.annule is null and projection.article.ean is not null";
    $req_res2 = mssql_query($req2);
    if (mssql_num_rows($req_res2) > 0) {
        $nbc = 0;
        while ($result = mssql_fetch_row($req_res2)) {
            if(trim($result[0]) <> "") {
                if($codeliste == "") $codeliste = "'".$result[0]."'";
                else $codeliste .= ",'". $result[0]."'";
                
                $req = "REPLACE INTO fean ( bar_code , article ) VALUES ('". trim($result[0]) ."','". trim($result[1]) ."')";
                if($connexion->exec($req)) {
                    $nbc++;
                }else{
                    echo "<br/>Fail Replace fean";
                }
            }
        }
        echo "<br/>Exported Ean : ".$nbc."<br/>";
    }else{
        echo "Fail request at projection.article of ean";
    }
    
    if($codeliste <> "") {
        
        $req = "DELETE FROM fean WHERE bar_code NOT IN (".$codeliste .")";
        if($connexion->exec($req)) {
            echo "Success delete Ean not export";
        }else{
            echo "Fail at DELETE fean";
        }
    }else{
        echo "codeliste is empty";
    }

More Information : I'm not allowed to truncate tables, I can't change the configuration of php.ini and conf.ini, I'm working on a delete up to 50.000~100.000 rows.

With the code above I obtain "SQLSTATE[HY000]: General error: 2006 MySQL server has gone away"

After that I tried tempory table, I obtain for result a loading page who never end (to be sure I waited 1h and no result from the page).

Then I tried to delete 5.000 rows by 5.000 rows with a loop, no more success.

Thanks in advance.

---

EDIT : I do some research and try to change the way I was deleting for a other way to the NOT IN in the code above, like CBroe suggest.

But I've got one more time "Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away'" when I load my page. Have you another idea of way to make this page Delete without gone away ?

---

EDIT2 : I've had some change in my code in the objectif to send less request to the database mysql. But I've been inform that my request have cause some lag issue because my delete request stay on sleep state in mysql. To be honest i don't know why, if someone could explain me why and maybe how I can avoid this problem in futur I would be grateful.

Here my code now : my goal was to reduce the number of request sql I send to the database.

        $codeliste = array();$codesuppr = array();
        $req2 = "SELECT projection.article.ean, projection.article.artic
        FROM projection.article
        WHERE projection.article.annule is null and projection.article.ean is not null";
        $req_res2 = mssql_query($req2);
        if (mssql_num_rows($req_res2) > 0) {
            $nbc = 0;
            while ($result = mssql_fetch_row($req_res2)) {
                if(trim($result[0]) <> "") {
                    array_push($codeliste, trim($result[0]));
                    $req = "REPLACE INTO fean ( bar_code , article ) VALUES ('". trim($result[0]) ."','". trim($result[1]) ."')";
                    if($connexion->exec($req)) {
                        $nbc++;
                    }else{
                        echo "<br/>Fail Replace fean";
                    }
                }
            }
        echo "<br/>Exported Ean : ".$nbc."<br/>";
    }else{
         echo "Fail request at projection.article of ean"
    }
    
    if (!empty($codeliste)) {
        $req = "SELECT bar_code FROM fean";
        $resultats = $connexion->query($req);
        foreach($resultats AS $resultat) {
            if (!in_array(trim($resultat), $codeliste)) {
                array_push($codesuppr, trim($resultat));
            }
        }
        if (!empty($codesuppr)) {
            $total= 0;
            $nbc = 0;
            $codeliste = array();
            foreach($codesuppr AS $code_barre) {
                if(count($codeliste) < 500 && ($total + count($codeliste)) < count($codesuppr)) {
                    array_push($codeliste, $bar_code);
                }else{
                    array_push($codeliste, $bar_code);
                    $req = "DELETE FROM fean WHERE bar_code IN ('".implode("','",$codeliste)."')";
                    if($connexion->exec($req)) {
                        $nbc++;
                    }else{
                         echo "Fail at DELETE fean";
                    }
                    $total .= count($codeliste);
                    $codeliste = array();
                }
            }
            echo "Success delete Ean not export";
        }
    }else{
        echo "codeliste is empty<br/>";
    }

---

EDIT3 : After looking mysql, it's seems Replace send too many request at mysql who send back : Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 2006 MySQL server has gone away', and finish in sleep command.