why there is an extra record stored in mysql database?

Tags: , ,



i created a csv to mysql database importer. Code-

<?php 

include_once("connection.php");


if(isset($_POST["import"])){
$filename =  $_FILES["file"]["tmp_name"];

if($_FILES["file"]["size"] > 0){
    $file = fopen($filename, "r");

    while(($column = fgetcsv($file,10000, ",")) !== FALSE){
        $sqlInsert = "INSERT INTO datasets (name,email,phone) values('" . $column[0] . "', '". $column[1] . "', '" . $column[2] . "')";
        $result = mysqli_query($con,$sqlInsert);

        if(!empty($result)){
            echo "CSV data imported into database !";
        }
        else{
            echo "Error importing data into database ...";
        }
    }
    
}
}

?>

And form code is –

<form class="form-container my-5" action="backend/api.php" method="POST" enctype="multipart/form-data">
    <div class="form-group">
        <div class="input-group mb-3">
            <div class="input-group-prepend">
                <span class="input-group-text" id="inputGroupFileAddon01">Upload</span>
            </div>
            <div class="custom-file">
                <input type="file" name="file" accept=".csv" class="custom-file-input" id="inputGroupFile01" aria-describedby="inputGroupFileAddon01">
                <label class="custom-file-label" for="inputGroupFile01">Choose file</label>
            </div>
        </div>
    </div>
    <button type="submit" name="import" class="btn btn-primary">Submit</button>
</form>

But, the record should looks like this- enter image description here

So, i marked red , the unexpected data. I cant find out why it shows like that? any errors in code?

Answer

You need to skip the header row of your CSV file. Just don’t execute the SQL statement for the first row.

Your corrected code should look something like this:

<?php

include_once "connection.php";

if (isset($_POST["import"])) {
    $filename = $_FILES["file"]["tmp_name"];

    if ($_FILES["file"]["size"] > 0) {
        $file = fopen($filename, "r");
        
        $stmt = $con->prepare('INSERT INTO datasets (name,email,phone) values(?,?,?)');

        $headerRow = true;
        while (($column = fgetcsv($file, 10000, ",")) !== false) {
            if ($headerRow) {
                $headerRow = false;
                continue;
            }
            $stmt->bind_param('sss', $column[0], $column[1], $column[2]);
            $stmt->execute();
        }
        echo "CSV data imported into database !";
    }
}


Source: stackoverflow