Why do I get duplicate data when import CSV file into MySQL with php

Question

When submit button clicked, I get duplicate date in MySQL database, which means a single row in CSV file is inserted twice and get identical records in database.

My php code goes here.

if(isset($_POST['submit'])){
if( $_FILES['file']['name']){
    $filename = explode('.',$_FILES['file']['name']);
    if ($filename[1] == 'csv'){
        $handle = fopen($_FILES['file']['tmp_name'],"r");
        if($_POST['dataType']=='aType'){
            while ($data = fgetcsv($handle)){
                $item0 = mysqli_real_escape_string($con,$data[0]);
                $item1 = mysqli_real_escape_string($con,$data[1]);
                $item2 = mysqli_real_escape_string($con,$data[2]);
                $item3 = mysqli_real_escape_string($con,$data[3]);
                $sql = "insert into `table_XX`(`Column_A`,`Column_B`,`Column_C`,`Column_D`) VALUES ('$item0','$item1','$item2','$item3')";
                mysqli_query($con,$sql);
            }
            if(!(mysqli_query($con,$sql))) echo "something is wrong!";
            fclose($handle);
        }
    }
}

}

My Html code goes here.

<form method="post" action="import.php" enctype="multipart/form-data">
  <select name="dataType" class="form-control">
     <option selected="selected">pls select data</option>
     <option>aType</option>
  </select>
  <input type="file" name="file">
  <input type="submit" name="submit" value="submit">
</form>

What's wrong with my code? Thanks.

Edit:

Later I know that this line of code cause this mistake.

if(!(mysqli_query($con,$sql))) echo "something is wrong!"

However, it is a bad habit to use code like this to check our query?

if(mysqli_query($con,$sql))

By the way, how can I use a Array to store the value from

mysqli_real_escape_string

instead of using this x4

$item0 = mysqli_real_escape_string($con,$data[0]);
$item1 = mysqli_real_escape_string($con,$data[1]);
$item2 = mysqli_real_escape_string($con,$data[2]);
$item3 = mysqli_real_escape_string($con,$data[3]);

well I have just learn a little about file with php code, so might have a lot of mistakes, but I just cannot find them.@Fabian Picone — Steve
– Steve, Commented Apr 4, 2017 at 13:46
Is your code in a function? Maybe the function is called up twice. Can you show more from your code? — fabpico
– fabpico, Commented Apr 4, 2017 at 13:50
emm,actually I found this if(!(mysqli_query($con,$sql))) echo "something is wrong!". this code was right before fclose($handle) ..may be that's the reason the mysqli_query is called up twice @Fabian Picone — Steve
– Steve, Commented Apr 4, 2017 at 13:59
Could your tell me how to use an array to store the value from mysqli_real_escape_string, I know I can use a loop to do with it, but fail again and again, it's so frustrating.@Fabian Picone — Steve
– Steve, Commented Apr 4, 2017 at 14:14

fabpico · Accepted Answer · 2017-04-04 15:00:13Z

1

The reason is because mysqli_query() is called twice. Also in if() it executes itself! If you want to check the result of mysqli_query() safe it in an variable like $queryResult = mysqli_query() and then if($queryResult).

Replace mysqli_query($con,$sql); with

if(!mysqli_query($con,$sql)) {
    echo "something is wrong!";
}

And Remove: if(!(mysqli_query($con,$sql))) echo "something is wrong!";

ADDITION Array solution

Replace

$item0 = mysqli_real_escape_string($con,$data[0]);
$item1 = mysqli_real_escape_string($con,$data[1]);
$item2 = mysqli_real_escape_string($con,$data[2]);
$item3 = mysqli_real_escape_string($con,$data[3]);

with (not tested)

function escapeArray(array $data) {
    foreach($data as $i => $item) {
        $data[$i] = mysqli_real_escape_string($item);
    }
    return $data;
}

$data = escapeArray($data);

Then either do VALUES (" . $data[0] . "," . $data[1] . ", " . $data[2] . "," . $data[3] . ")

or

VALUES (" . implode(',', $data) . ")

edited Apr 4, 2017 at 15:00

answered Apr 4, 2017 at 14:26

fabpico

2,9864 gold badges32 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Steve Over a year ago

Thanks a lot ! Very kind of you !

Collectives™ on Stack Overflow

Why do I get duplicate data when import CSV file into MySQL with php

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related