CODEWITHSUNDEEP
phpmysqlcsv
user1990386
user1990386

Reputation: 23

csv file is inserting a blank row in the last of other rows into mysql table

when i am inserting records from csv file to mysql table it also insert a blank row with other rows last of the other record.

Here is my code:

<?php
     $connect = mysql_connect('localhost','root','root');
    if (!$connect) {
    die('Could not connect to MySQL: ' . mysql_error());
    }
   $cid =mysql_select_db('bizin490_devcredit',$connect);
      define('CSV_PATH', '/home/ubc/Documents/');
     $csv_file = CSV_PATH . "test.csv";
            $csvfile  = fopen($csv_file, 'r');
            $theData  = fgetcsv($csvfile);
            $i = 0;
            while (!feof($csvfile)) {
                $csv_data[] = fgets($csvfile, 1024);
                $csv_array  = explode(",", $csv_data[$i]);
                $insert_csv = array();
                $insert_csv['name']  = $csv_array[0];
                $insert_csv['email'] = $csv_array[1];
                $query = mysql_query("select name from test where name='" . $insert_csv['name'] . "'");
                $count = mysql_num_rows($query);
                if ($count == 0) {
                    $query = "INSERT INTO test(name,email)VALUES('" . $insert_csv['name'] . "','" . $insert_csv['email'] ."')";
                        $n = mysql_query($query, $connect);
                } else {
                    $sql = "update test set email='".$insert_csv['email']."'";
                            $qu  = mysql_query($sql);       
                }
     $i++;
                }

     fclose($csvfile);

            echo "File data successfully imported to database!!";
            mysql_close($connect);
            ?>

Upvotes: 1

Views: 1245

Answers (3)

Axel Amthor
Axel Amthor

Reputation: 11096

There are possible flaws in this code:

        $csvfile  = fopen($csv_file, 'r');           // opens the file
        $theData  = fgetcsv($csvfile);               // reads first line
        $i = 0;
        while (!feof($csvfile)) {                    // checks for eof
            $csv_data[] = fgets($csvfile, 1024);     // reads next line
  1. When there's no header, the first row is skipped.
  2. feof is false after the last line has been read. fgets doesn't check, whether there is data behind the last \n, so feof is false. fgets then reads the "empty" line until eof which is added falsely.

Change:

  // checks for eof and errors
  while ( ($csv_data[] = fgets($csvfile, 1024)) !== false) {  
       ...
  }

Upvotes: 0

Mark Baker
Mark Baker

Reputation: 212402

Use SplFileObject, and you can tell it to ignore empty lines, and to handle the file as a CSV file automatically

$file = new SplFileObject($csv_file);
$file->setFlags(SplFileObject::READ_CSV | SplFileObject::SKIP_EMPTY);
foreach ($file as $csv_array) {
    ...
}

Upvotes: 0

Codeek
Codeek

Reputation: 1624

not very efficient one but add a condition 

if ($insert_csv['name'] !== '' && $insert_csv['email']!=='') //since != '' will return true if you pass is numeric 0 and a few other cases, hence using !==
{
      $query = mysql_query("select name from test where name='" . $insert_csv['name'] . "'");
      $count = mysql_num_rows($query);
     if ($count == 0) {
                $query = "INSERT INTO test(name,email)VALUES('" . $insert_csv['name'] . "','" .  $insert_csv['email'] ."')";
                $n = mysql_query($query, $connect);
     } else {
                $sql = "update test set email='".$insert_csv['email']."'";
                $qu  = mysql_query($sql);       
     }

}

Upvotes: 1

Related Questions