Error when uploading CSV file to database using PHP

Question

I'm trying to upload a CSV file into an existing table in my database using PHP.

Here is my full code:

<?php
include("detail.php");


 $connect = mysql_connect("$host", $user, $password) or die("Couldn't connect to SQL  Server on $myServer");
 mysql_select_db("$database") or die("Couldn't open database $myDB");

 if (mysqli_connect_errno())
 {
 echo "Failed to connect to MySQL: " . mysql_connect_error();
 }


 define('CSV_PATH','E:/4th Year/FYP/'); // specify CSV file path

  $csv_file = CSV_PATH . "Cash2011.csv"; // Name of your CSV file
 $csvfile = fopen($csv_file, 'r');
 $theData = fgets($csvfile);
 $i = 0;
 while (!feof($csvfile))
 {
   $csv_data[] = fgets($csvfile, 1024);
   $csv_array = explode(",", $csv_data[$i]);
   $insert_csv = array();
   $insert_csv['cashAmount'] = $csv_array[0];
   $insert_csv['cashReceivedDate'] = $csv_array[1];
   $insert_csv['customerID'] = $csv_array[2];
   $insert_csv['invoiceNo'] = $csv_array[3];
   $insert_csv['monthNum'] = $csv_array[4];
   $insert_csv['yearNum'] = $csv_array[5];
   $query = "INSERT INTO cash2011(cashAmount,cashReceivedDate,invoiceNo,monthNum,yearNum) VALUES ('','".$insert_csv['cashAmount']."','".$insert_csv['cashReceivedDate']."','".$insert_csv['customerID']."','".$insert_csv['invoiceNo']."','".$insert_csv['monthNum']."','".$insert_csv['yearNum']."')";
   $n=mysql_query($query, $connect);
   $i++;
   }
   fclose($csvfile);
   echo "File data successfully imported to database!!";
   ?>

These are the errors that I keep receiving: Undefined offset: 1 in C:\Apache2.2\htdocs\clearTables.php on line 47 (which is $insert_csv['cashAmount'] = $csv_array[0];). Undefined offset: 2 in C:\Apache2.2\htdocs\clearTables.php on line 48.

Does anyone know what the problem is? Thanks

to make it a little cleaner, why not make your vars something like $cashAmount = $csv_array[0]; this way you can just insert $cashAmount instead of $insert_csv['cashAmount']; nothing wrong with what are doing there, just a little cleaner IMO — bart2puck
– bart2puck, Commented Feb 24, 2014 at 20:03
are you sure all the lines in your CSV file contain 6 values like cash, something, something else, etc. seems like some lines have only 1 value in them. — CodeBird
– CodeBird, Commented Feb 24, 2014 at 20:07
It doesn't look like you're doing any SQL escaping at all here and probably have some severe [SQL injection bugs]. You're also using the deprecated mysql_query which should not be used in new applications because it's being removed from future versions of PHP. PDO is not hard to learn and provides a simple, reliable way of adding data to queries. A guide like PHP The Right Way outlines best practices. — tadman
– tadman, Commented Feb 24, 2014 at 20:14

Community · Accepted Answer · 2017-05-23 11:51:15Z

0

i tested your code, and work very nice... and, when i echo the query, return:

    INSERT INTO cash2011(cashAmount,cashReceivedDate,invoiceNo,monthNum,yearNum) 
VALUES ('','ad4213','ad4214','ad4215','ad4216','adware','ad7777 ')
    //'','ad4213','ad4214','ad4215','ad4216','adware','ad7777 ' -> data in my example csv file

a few things.. i believe that your file doesn't exist, that is why you have those errors, try to check with this line before the while (!feof($file))

 if(is_file($csv_file)){ //your code}

also, in your query, you have 5 values, and only 4 in the rows definition. and, why you have a , there?

if your problem continue, check my answer here , if you look, is a similar code of yours.

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered Feb 25, 2014 at 1:25

kraysak

1,7561 gold badge13 silver badges14 bronze badges

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

Comments

Flexicoder · Accepted Answer · 2015-06-18 07:35:28Z

0

you have syntax error in following code. So replace

$query = "INSERT INTO cash2011(cashAmount,cashReceivedDate,invoiceNo,monthNum,yearNum) VALUES ('','".$insert_csv['cashAmount']."','".$insert_csv['cashReceivedDate']."','".$insert_csv['customerID']."','".$insert_csv['invoiceNo']."','".$insert_csv['monthNum']."','".$insert_csv['yearNum']."')";

By

$query = "INSERT INTO cash2011(cashAmount,cashReceivedDate,invoiceNo,monthNum,yearNum) VALUES ('".$insert_csv['cashAmount']."','".$insert_csv['cashReceivedDate']."','".$insert_csv['customerID']."','".$insert_csv['invoiceNo']."','".$insert_csv['monthNum']."','".$insert_csv['yearNum']."')";

edited Jun 18, 2015 at 7:35

Flexicoder

8,5615 gold badges45 silver badges58 bronze badges

answered Jun 18, 2015 at 7:07

Aashim

1

Collectives™ on Stack Overflow

Error when uploading CSV file to database using PHP

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related