PHP / MYSQL - 将Excel / CSV上传到MySql表

Question

I recently built a web app which allows teachers to give grades to students and a lot of other stuff. Right now I'm adding a feature that gives the teacher the option to upload an excel/csv file with all the grades to the different students. My code seems fine however there are some issues with it and I can't figure out why. What happens is I get a bunch of blank records on my 'avaliacoes' table when I should only be getting 2 records.

How my excel looks :

1 (row) :  naluno, uc, tipo, nota
2 (row) : r2011251, BD, exame, 15
3 (row) : r2011223, BD, exame, 16

(the first row isn't accounted for when inserting into the table)

My HTML code for the form (inside Diretor-Curso.php) :

<form class="form-group" action="lancarnota3.php" method="post" enctype="multipart/form-data">
  <label class="control-label" for="uc">Enviar Ficheiro Excel/CSV com notas</label>

  <br>
  <input id="fileSelect" type="file" name="file" accept=".csv, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel" />  
  <br>

  <input type="submit" class="btn btn-default botao2" name="Submeter" value="Submeter"></button>

</form>

My PHP page (lancarnota3.php) which handles the upload :

<?php

if(isset($_POST["Submeter"]))
{
    $conn = mysqli_connect("localhost", "root", "", "teste");
    $conn->set_charset("utf8");

    // Check connection
    if($conn === false){
        die("ERROR: Could not connect. " . mysqli_connect_error());
    }
    //$filename=$_FILES["file"]["tmp_name"];
    if($_FILES["file"]["size"] > 0)
    {
        $file = fopen($_FILES["file"]["tmp_name"], "r");

        $count = 0;                                         
        while (($emapData = fgetcsv($file, 1000, ",")) !== FALSE)
        {

            $count++;                                      

            if($count>1){                                  
              $sql = "INSERT into avaliacoes (naluno, uc, tipo, nota) values ('$emapData[0]','$emapData[1]','$emapData[2]','$emapData[3]')";
              mysqli_query($conn, $sql);
            }                                              
        }
        fclose($file);
        include 'Diretor-Curso.php';
        echo "<script> replace('lancarnotas2'); </script>";
    }
    else
    {
        include 'Diretor-Curso.php';
        echo "<script> replace('lancarnotas6'); </script>";
    }
}


// close connection
mysqli_close($conn);
?>

Any idea how to make it work?

Answer 1

Are you sure that your data file doesn't contain empty line? Check the MySQL general log to see which queries were executed. I expect you will see the queries inserting the blank records in the log.

You could simply add a condition before the INSERT to check that you are trying to insert "non-empty" data, or perform additional validation to ensure that the data is indeed complete.