Retrieve data from one table and insert/update in another table in mysql

204 views Asked by At

I want to retrieve data from three tables using some condition and then insert/update in another three tables. This is a very simple process, the script of which is developed in php. But the catch is that the records retrieved from each table is 100k+. PHP script runs only with small number of records and gives time out error for large data. Can anyone please suggest how to solve this issue. All the three tables data needs to be fetched at runtime. Below is my php script which gives timeout error

switch($action){
    case 'tequipebudget': 
        $oldPresta              =  budPrestaDataTransfer::getOldPresta('tequipebudget'); 
        $budProviderBudget      =  budPrestaDataTransfer::updatebudProviderBudget($oldPresta, 'budproviderbudget', 3); 
    break;
    case 'tequipebudgetjum': 
        $oldPrestaJum           =  budPrestaDataTransfer::getOldPresta('tequipebudgetjum'); 
        $budProviderBudgetJum   =  budPrestaDataTransfer::updatebudProviderBudget($oldPrestaJum, 'budproviderbudgetjum', 3); 
    break;
    case 'tequipebudgetavhisto':
        $oldPrestaAvHisto           =  budPrestaDataTransfer::getOldPresta('tequipebudgetavhisto'); 
        $budProviderBudgetAvHisto   =  budPrestaDataTransfer::updatebudProviderBudget($oldPrestaAvHisto, 'budproviderbudgetavhisto', 3); 
    break;
}
static public function getOldPresta($table) {
        $sql        = "SELECT Annee, CodeEntite, CodeProjet,  MtBudgetAEquipeKE, projet_id";  
        if($table == 'tequipebudgetavhisto') {
            $sql    .= " ,avenant_id ";
        }
        $sql        .= " FROM ".$table." WHERE Annee < 2020 ";
        $dbObj              =  budPDO::getInstance();
        $prestaList = $dbObj->getAllResults($sql);
        return $prestaList;
    }


    static public function updatebudProviderBudget($prestaList, $table, $autreId) {
        foreach($oldPresta as $key=>$val) {
            $sql      = "SELECT count(*) as cnt FROM ".$table." WHERE Annee = '".$val['Annee']."' AND CodeEntite = '".$val['CodeEntite']."' AND 
                             CodeProjet = '".$val['CodeProjet']."' AND projet_id = '".$val['projet_id']."' AND provider_id = '".$oldPresta['AuterId']."' ";
            $dbObj    =  budPDO::getInstance();
            $res      =  $dbObj->getOneRow($sql);

            if($res['cnt'] == 0){ // record does not exists in table
                $update  =  "INSERT INTO ".$table." SET Annee = '".$val['Annee']."', CodeEntite = '".$val['CodeEntite']."',
                                CodeProjet = '".$val['CodeProjet']."', cost = '".$val['MtBudgetAEquipeKE']."' ,
                                projet_id = '".$val['projet_id']."', provider_id = '".$autreId."',
                                addedon_date = '".NOW_CONST."' ";
                if($table == 'budproviderbudgetavhisto') {
                    $update .= " ,avenant_id= '".$val['avenant_id']."' ";
                }
            }else {
                $update  =  "UPDATE ".$table." SET Annee = '".$val['Annee']."', CodeEntite = '".$val['CodeEntite']."',
                                CodeProjet = '".$val['CodeProjet']."', cost = '".$val['MtBudgetAEquipeKE']."' ,
                                projet_id = '".$val['projet_id']."', provider_id = '".$autreId."',
                                modifiedon_date = '".NOW_CONST."' ";

                if($table == 'budproviderbudgetavhisto') {
                    $update .= " ,avenant_id= '".$val['avenant_id']."' ";
                }

                $update  .= " WHERE Annee = '".$val['Annee']."' AND CodeEntite = '".$val['CodeEntite']."' AND 
                             CodeProjet = '".$val['CodeProjet']."' AND projet_id = '".$val['projet_id']."' AND provider_id = '".$autreId."' ";

                if($table == 'budproviderbudgetavhisto') {
                    $update .= " AND avenant_id= '".$val['avenant_id']."' ";
                }
            }
            //echo "update -- " . $update. "<br><br>";
            $sth = $dbObj->pdo->prepare($update);
            $exec = $sth->execute();
        }
    }
1

There are 1 answers

0
MThiele On BEST ANSWER

You could increase the timeout in your PHP settings.

ini_set('max_execution_time','{number of seconds}');

Then you will probably also have to increase the memory limit.

ini_set('memory_limit', '2GB');

But it would be better to leave large data logic to the database. So if I were you, I would write a stored procedure / function and execute it with PHP only exec