How do I manage new data entry in mysql table when parsing from CSV, marking the existing ones as 0 and the new as 1?

See more:

PHP

MySQL

hi, all i have this php function which is supposed to get data from a CSV file containing my benchmark results and populate/update the "RenderData" table in my MySQL DB.

function insertOrUpdateRenderData($conn, $machineModelID, $cpuModelID, $softwareID, $renderEngineID, $resolutionID, $runNumber, $renderingTime, $userID)
{
    // Escape and format inputs
    $machineModelID = mysqli_real_escape_string($conn, $machineModelID);
    $cpuModelID = mysqli_real_escape_string($conn, $cpuModelID);
    $softwareID = mysqli_real_escape_string($conn, $softwareID);
    $renderEngineID = mysqli_real_escape_string($conn, $renderEngineID);
    $resolutionID = mysqli_real_escape_string($conn, $resolutionID);
    $runNumber = mysqli_real_escape_string($conn, $runNumber);
    $userID = mysqli_real_escape_string($conn, $userID);
    $renderingTime = mysqli_real_escape_string($conn, $renderingTime); // Escape the time string

    // Mark all records as old
    $updateAllRecordsQuery = "UPDATE RenderData SET IsValid = 0 WHERE UserID = $userID";
    if (!$conn->query($updateAllRecordsQuery)) {
        echo "Error updating old records: " . $conn->error;
        return;
    }

    // Check if record exists
    $selectQuery = "SELECT RenderDataID FROM RenderData WHERE MachineModelID = $machineModelID AND CPUModelID = $cpuModelID AND SoftwareID = $softwareID AND RenderEngineID = $renderEngineID AND ResolutionID = $resolutionID AND RunNumber = $runNumber AND UserID = $userID";
    $result = $conn->query($selectQuery);

    if ($result && $result->num_rows > 0) {
        // Update existing record
        $row = $result->fetch_assoc();
        $renderDataID = $row['RenderDataID'];
        $updateQuery = "UPDATE RenderData SET TotalRenderingTime = '$renderingTime', IsValid = 1 WHERE RenderDataID = $renderDataID";
        if (!$conn->query($updateQuery)) {
            echo "Error updating record: " . $conn->error;
        }
    } else {
        // Insert new record
        $insertQuery = "INSERT INTO RenderData (MachineModelID, CPUModelID, SoftwareID, RenderEngineID, ResolutionID, RunNumber, TotalRenderingTime, UserID, IsValid) VALUES ($machineModelID, $cpuModelID, $softwareID, $renderEngineID, $resolutionID, $runNumber, '$renderingTime', $userID, 1)";
        if (!$conn->query($insertQuery)) {
            echo "Error inserting record: " . $conn->error;
        }
    }
}

the expected behavior is the new data (the ones parsed from the CSV) should be the only ones considered (set as 1), because they are the last session of the benchmark, while the ones already present in the table belong to the old session and should be discarded (set as 0). that's to prevent a mix-up of results. the user should only see the results of his last session. for example, the user could make a complete benchmark and upload 9 results. then another one, incomplete, with only 3 results. when visualizing the results the 9 shouldn't be considered, only the 3 ones should.

the given code is partially working but it marks with 1 only the last of the 3 records. the other 2 remains at 0. the remaining ones in the table(old ones) are correctly marked as 0. any suggestion?

What I have tried:

i tried several different approaches, verifying the code with the AI, which was unable to give me a working solution.