CSV upload - flag discrepancy with PHP/SQL upon preview

[thenorman138]

I have a current site that allows for a CSV (235 fields) to be uploaded daily and insert into a database/table. This all works perfectly, but there is now a need for a QA/QC side of this. There are several conditions to check, but I think if I find a way to do this for one I can use it across the board.

 

An example: There is one column in particular that is supposed to be within 95.3% and 101.8% in order to pass. If someone entered 98% but accidentally hit FAIL, then the PHP should catch this.

 

When the CSV is uploaded, the user hits preview button and it shows the whole CSV file in an html preview table before the user can submit. I want this to show any discrepancy in red text or something like that, so in the case above I would list the FAIL field in red so the user knew to change this to PASS before submitting. 

 

Here is the code for the preview table:

 

    if(isset($_POST['preview']))

{

ini_set('auto_detect_line_endings', true);

 

$file = $_FILES["file"]["tmp_name"];

$handle = fopen($file, "r");

$maxPreviewRows = PHP_INT_MAX;  // this will be ~2 billion on 32-bit system, or ~9 quintillion on 64-bit system

$hasHeaderRow = true;

 

echo '<table>';

 

/*WE WILL NEED TO QA CONDITIONS AND HIGHLIGHT IN RED HERE. ALSO NEED BORDER STYLINGS*/

 

if ($hasHeaderRow) {

   $headerRow = fgetcsv($handle);

   echo '<thead><tr>';

   foreach($headerRow as $value) {

       echo "<th>$value</th>";

   }

   echo '</tr></thead>';

}

 

echo '<tbody>';

 

$rowCount = 0;

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

   echo '<tr>';

   foreach($row as $value) {

       echo "<td>$value</td>";

   }

   echo '</tr>';

 

   if (++$rowCount > $maxPreviewRows) {

       break;

   }

}

echo '</tbody></table>';

 

}

    ?>

 

The full CSV submission code is very long because I explicitly define all 235 fields in an array and in the SQL statement, but here's an idea:

 

    while (($filesop = fgetcsv($handle)) !== FALSE) {

 

    $coldata = array();

 

    $coldata["orderNumber"] = $filesop[0];

    $coldata["workOrderPacket"] = $filesop[1];

    $coldata["workOrderNum"] = $filesop[2];

    $coldata["lowSideMIUNumArriv"] = $filesop[3];

    $coldata["lowSideMIUNumDepart"] = $filesop[4];

    $coldata["highSideMIUNumArriv"] = $filesop[5];

    //These go on up to 235, but this builds an array for the whole CSV

 

    $table_cols = array();

    /*staging table, this also lists all 235 because everything goes to the staging table first*/

    $table_cols[0] = "orderNumber,workOrderPacket,workOrderNum,lowSideMIUNumArriv";

 

    $tablenames = array("staging");

 

 

 

    for($tableno = 0;$tableno < sizeof($tablenames);$tableno++){

$q = "";

 

$col_list = '`'.str_replace(',','`,`',$table_cols[$tableno]).'`';

$q .= "INSERT INTO ".$tablenames[$tableno]." (".$col_list.") VALUES (";

$last_id = mysqli_insert_id($connect);

$cols = explode(",",$table_cols[$tableno]);

$data = array();

foreach($cols as $key => $fldname) {

 

$data[] = "'".mysqli_real_escape_string($connect, $coldata[$fldname])."'";

 

}

 

/*INSERT INTO STAGING TABLE - INITAL CSV UPLOAD*/

$q .= implode(",",$data).");";

 

 

Now all I need is an idea on how to create statement conditions like the example I stated above so that it can hopefully flag the issue in the preview table, if anyone has any ideas.

[requinix]

1. Is the PASS/FAIL an actual value in the CSV data?

2. Is the PASS/FAIL (a) determined only by the data contained within the CSV and (b) something your script can determine on its own?

 

Sounds like the answers to both are "yes", in which case the PASS/FAIL value is unnecessary to your code (you can figure it out yourself) - but you do want to show it back to the submitter so they can see whether there was a mistake in the data?

 

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

Do your validation at the beginning of that loop,

$pass = ($row[123] >= 0.953 && $row[123] then compare what you decide with the PASS/FAIL from the row. Use classes with CSS, like

echo '';then you can decide how to actually style the failed (or even passed) rows later whenever you feel like it.

[thenorman138]

Thank you so much, I think this makes total sense now. And I could style the table row to color the text of the flagged row?

[requinix]

Yeah. CSS looks like

tr.passfail-nomatch {
	color: red;
}

[thenorman138]

Awesome, thank you so much!