How to update records when importing csv file

Question

I want to update existing records in a table using an excel csv import file.

the table name is aflossingen and has the columns: AflossingID, VoorschotID, Aflossingdatum, Aflossingsbedrag, Afgelost, Saldo.

This is the code PHPRad has generated, which can only do an INSERT:

function import_data(){
        if(!empty($_FILES['file'])){
            $finfo = pathinfo($_FILES['file']['name']);
            $ext = strtolower($finfo['extension']);
            if(!in_array($ext , array('csv'))){
                $this->set_flash_msg("Document formaat niet ondersteund", "danger");
            }
            else{
            $file_path = $_FILES['file']['tmp_name'];
                if(!empty($file_path)){
                    $request = $this->request;
                    $db = $this->GetModel();
                    $tablename = $this->tablename;
                    $options = array('table' => $tablename, 'fields' => '', 'delimiter' => ',', 'quote' => '"');
                    $data = $db->loadCsvData( $file_path , $options , false );
                    if($db->getLastError()){
                        $this->set_flash_msg($db->getLastError(), "danger");
                    }
                    else{
                        $this->set_flash_msg("Data imported successfully", "success");
                    }
                }
                else{
                    $this->set_flash_msg("Error uploading file", "danger");
                }
            }
        }
        else{
            $this->set_flash_msg("No file selected for upload", "warning");
        }
        $this->redirect("aflossingen");
    }

This is my code. Nothing happens:

function import_data(){
    if(isset($_POST["importcsv"])){

        $file = $_FILES["csv_file"]["tmp_name"];
        $handle = fopen($file,"r");

        while ($row = fgetcsv($handle)) {

            $id = $row[0];
            $Aflossingdatum = $row[2];
            $Aflossingsbedrag = $row[3];
            $Afgelost = $row[4];
            $Saldo = $row[5];

            $sql = "UPDATE aflossingen SET Aflossingdatum = Aflossingdatum,Afgelost =  Afgelost, Saldo = Saldo WHERE AflossingID = AflossingID";
            $update_data_stmt = mysqli_stmt_init($connection);

            if (!mysqli_stmt_prepare($update_data_stmt, $sql)){
                die("Something went wrong with the upload. " . mysqli_error($connection));
            } else {
                mysqli_stmt_bind_param($update_data_stmt, "ss", $Aflossingdatum, $id, $Aflossingsbedrag, $Afgelost, $Saldo);
                mysqli_stmt_execute($update_data_stmt);
                if ($id == "AflossingID" && $Aflossingdatum == "Aflossingdatum"){
                    echo "";
                } else {
                    echo "Lead <b>{$id}</b>'s response was updated to <b>{$Aflossingdatum}</b>.</p>";
                }
            } 

        }

    }
$this->redirect("aflossingen");
}

What RDMBS are you using? In MySQL, you can use the `LOAD DATA INFILE` query to load from a CSV file, and it has a `REPLACE` option that will replace rows with the same unique key. — Barmar, Jun 22 '22 at 14:24
phpMyAdmin isn't a database. It's a web application for managing and querying MySQL. — Barmar, Jun 22 '22 at 15:52

score 0 · Answer 1 · answered Jun 22 '22 at 14:39

You're not creating the prepared statement correctly. It needs to have ? placeholders where the variables will be substituted.

And you need to have as many characters in the type string argument to mysqli_stmt_bind_param() as you have variables. And the variables have to be in the same order as in the query, so $id must be last.

You don't need to prepare the statement and bind parameters each time through the loop. Just prepare it once, and bind the parameters to reference variables that will be updated during the loop.

You were missing Aflossingsbedrag from your UPDATE query, I added it back.

Instead of checking whether $id == 'AflossingID' to skip the header row, I simply read the first line of the file before going into the loop that calls fgetcsv(). If you prefer to do it by checking the column value (in case there's no header) you should do it before executing the query, and skip the update (you can use continue; to go to the next iteration of the loop)

function import_data(){
    if(isset($_POST["importcsv"])){
        $file = $_FILES["csv_file"]["tmp_name"];
        $handle = fopen($file,"r");

        $sql = "UPDATE aflossingen SET Aflossingdatum = ?, Aflossingsbedrag = ?, Afgelost = ?, Saldo = ? WHERE AflossingID = ?";
        $update_data_stmt = mysqli_stmt_init($connection);
        mysqli_stmt_bind_param($update_data_stmt, "sssss", $Aflossingdatum, $Aflossingsbedrag, $Afgelost, $Saldo, $id);
        if (!mysqli_stmt_prepare($update_data_stmt, $sql)){
            die("Something went wrong with the upload. " . mysqli_error($connection));
        }

        fgets($handle); // Skip header row

        while ($row = fgetcsv($handle)) {

            $id = $row[0];
            $Aflossingdatum = $row[2];
            $Aflossingsbedrag = $row[3];
            $Afgelost = $row[4];
            $Saldo = $row[5];

            if (mysqli_stmt_execute($update_data_stmt)) {
                echo "Lead <b>{$id}</b>'s response was updated to <b>{$Aflossingdatum}</b>.</p>";
            } else {
                echo "Something went wrong when updating $id. " . mysqli_stmt_error($update_data_stmt);
            }
        } 
    }
}

Thank you, but this hasn't given the results I'm looking for. It doesn't update nor insert — Unknown04, Jun 22 '22 at 15:54
I think what you're looking for is `INSERT INTO ... ON DUPLICATE KEY UPDATE ...` See https://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists — Barmar, Jun 22 '22 at 16:00

How to update records when importing csv file

1 Answers1