比较数据库中的上传数据和原始CSV数据

Question

Here is the code after I uploaded the raw file then tried to validate the raw file with the uploaded file to see if they match:

while ($db_fetch_row = sqlsrv_fetch_array($database_query)){        
    $db_eid = $db_fetch_row['eid'];
    $db_team_lead = $db_fetch_row['team_lead'];
    $db_role = $db_fetch_row['role'];
    $db_productivity = $db_fetch_row['productivity'];
    $db_quality = $db_fetch_row['quality'];
    $db_assessment = $db_fetch_row['assessment'];
    $db_staffed_hours = $db_fetch_row['staffed_hours'];
    $db_kpi_productivity = $db_fetch_row['kpi_productivity'];
    $db_kpi_quality = $db_fetch_row['kpi_quality'];
    $db_kpi_assessment = $db_fetch_row['kpi_assessment'];
    $db_kpi_staffed_hours = $db_fetch_row['kpi_staffed_hours'];     

    for($row = 2; $row <= $lastRow; $row++) {
        $eid = $worksheet->getCell('A'.$row)->getValue();
        $team_lead = $worksheet->getCell('C'.$row)->getValue();
        $role = $worksheet->getCell('B'.$row)->getValue();                                  
        $productivity = $worksheet->getCell('D'.$row)->getValue();
        $productivity1 = chop($productivity,"%");

        $quality = $worksheet->getCell('E'.$row)->getValue();
        $quality1 = chop($quality,"%");         

        $assessment = $worksheet->getCell('F'.$row)->getValue();
        $assessment1 = chop($assessment,"%");

        $staffed_hours = $worksheet->getCell('G'.$row)->getValue();
        $staffed_hours1 = chop($staffed_hours,"%");

        $kpi_productivity =  $worksheet->getCell('H'.$row)->getValue();
        $kpi_quality =  $worksheet->getCell('I'.$row)->getValue();
        $kpi_assessment =  $worksheet->getCell('J'.$row)->getValue();
        $kpi_staffed_hours = $worksheet->getCell('K'.$row)->getValue(); 

        if($db_eid == $eid) {
            echo "Raw and Uploaded file Matched";
        } else {
            echo "Did not match";
        }
    }
}

The output always didn't match, as you can see below:

Answer 1

Of course it outputs that. Let's think it through:

For every row in the DB, you are checking EVERY row in the CSV. Presumably there is only ONE row in the CSV that has the same ID as the row in the DB, so that means if there are 100 records in the CSV, it will output "no match" 99 times (assuming there is 1 record in the CSV that does match).

One approach is to separate it into a function and attempt to find the matching row, as follows:

// Loop over all DB records
while ($db_fetch_row = sqlsrv_fetch_array($database_query)) {
    // call our new function to attempt to find a match
    $match = find_matching_row( $db_fetch_row, $worksheet );
    // If there's no match, output "didn't find a match"
    if ( ! $match ) {
         echo '<br>DID NOT FIND A MATCH.';
    // If there IS a match, $match represents the row number to use....
    } else {
         var_dump( $match );
         // Do your work on the match data...
         $team_lead = $worksheet->getCell('C'.$match)->getValue();
         // ...etc  
    }
}

/**
 * Attempt to find a row from the CSV with the same ID as the DB
 * record passed in.
 *
 * @param array $db_fetch_row
 * @param mixed $worksheet
 */
function find_matching_row( $db_fetch_row, $worksheet ) {
    $db_eid = $db_fetch_row['eid'];
    // Youll need to calculate $lastRow for this to work right..
    $lastRow = .... ;

    // Loop through the CSV records
    for($row = 2; $row <= $lastRow; $row++) {
            // If the IDs match, return the row number
            if($db_eid == $worksheet->getCell('A'.$row)->getValue() ){
                return $row;
            }
    }

    // If no match, return FALSE
    return FALSE;
}