CSV Upload Script issues

[zzaras]

Hey there everyone.

 

Years ago I used to be active on Pixel2Life. Seems they have died down. I got out of web coding for quite a few years since I have been in the Army.

 

I have had a code on a website that uploaded a Excel CSV file into a database. I know it shouldn't be a complicated task, but with this file different things get uploaded depending on what the values are on the file.

 

Now I am assuming when my Hosting company upgraded to PHP 5 I started getting these errors.

 

 

Notice: Undefined offset: 3 in /home/rzaras/public_html/plaincart/admin/update/processupload.php on line 57

Notice: Undefined offset: 3 in /home/rzaras/public_html/plaincart/admin/update/processupload.php on line 57

Notice: Undefined offset: 3 in /home/rzaras/public_html/plaincart/admin/update/processupload.php on line 57

Notice: Undefined offset: 1 in /home/rzaras/public_html/plaincart/admin/update/processupload.php on line 40

Notice: Undefined offset: 1 in /home/rzaras/public_html/plaincart/admin/update/processupload.php on line 49

Notice: Undefined offset: 2 in /home/rzaras/public_html/plaincart/admin/update/processupload.php on line 51

Notice: Undefined offset: 3 in /home/rzaras/public_html/plaincart/admin/update/processupload.php on line 57

 

 

 

I would like to know how to get rid of those. As well as since the original code I have added a section. I added one more field in the CSV file that is the Original Price. Now in the code I will post I have that section called $pd_spc_price. What that section is suppose to do is if there is a value in the last field in the CSV file it will upload that price or it will upload the previous price that is uploaded earlier. If that make sense. I will post the code and a sample of the CSV file.

mysql_query("DELETE  FROM tbl_product") or die("Could not delete old information from database");

  

  $file = file_get_contents($_FILES["phile"]["tmp_name"]);

  $file_line = explode("\n",$file);

  $j = "0";

  foreach($file_line as $line) {

   $line_data = explode('","', $line);

   if($j>"0") {

    $splititupsomemore = explode(',', $line_data[1]);

//    $pd_id

    $pd_name = addslashes(substr($line_data[0], 1));

    if(substr($pd_name, 0, 1)=="z"||substr($pd_name, 0, 1)=="Z") {

     $cat_id = "1";

    } else {

     $cat_id = "18";

    }

    $pd_description = addslashes($splititupsomemore[0]);

    $pd_price = str_replace("\r", "", addslashes($splititupsomemore[1]));

    

                               $pd_spc = addslashes(substr($splititupsomemore[2], 1));

    if(substr($pd_spc, 0, 1)=="Y"||substr($pd_spc, 0, 1)=="y") {

     $spc_id = "1";

    } else {

     $spc_id = "0";

    }

    $pd_spc_price = addslashes(substr($splititupsomemore[3], 1));

   if($pd_spc_price == TRUE){

     $spc_price = $pd_spc_price;

    } else {

     $spc_price = $pd_price;

    }
//    $pd_qty

//    $pd_image

//    $pd_thumbnail

    $pd_date = date("Y-m-d G:i:s");

//    $pd_last_update
    $query = "INSERT INTO tbl_product VALUES('', '$cat_id', '$pd_name', '$pd_description', '$pd_price', '', '', '', '$pd_date', '', '$spc_id', '$spc_price')";

    mysql_query($query) or die($query);

   }

   $j++;

  }
  echo '<h3><p>The pricelist was updated on '.$pd_date.'. If this is not the current day and time the upload did not work.<br /><a href="index.php">Go Back</a></p></h3>';

 

This is the CSV file I have

 

 

"Item","Description","Price","Special","Original Price"
"AFRBUT","AFRICAN BUTTERFLY",6.25,"y",
"AFREN","ELEPHANT NOSE*",12.95,"y","13.95"
"AFRRO","AFRICAN ROPE FISH",7.95,"y","8.25"
"AMACF","ALBINO CLAWED FROG",3.75,"y",

 

 

 

As you can see the two middle lines have a price on the very end. Not all will have that and I am having an issue figuring that part of the code out.

 

Any help would be great. I haven't been able to get myself fully back into coding. Plus a lot has changed in the last 5 years.

 

Thanks!

[mac_gyver]

the offset errors you are seeing were probably always present, but php's error_reporting/display_errors settings were hiding them.

 

you are attempting to reference array elements that don't exist because the data didn't have some of the optional elements. the proper coding method would be to test if an optional piece of data exists and then take an appropriate action (use a specific default value when it doesn't exist.) you can use isset() to test if a php variable/array element exists. the Ternary Operator (short form if an if/else statement) makes writing the code straightforward -

$pd_spc_price = isset($splititupsomemore[3]) ? addslashes(substr($splititupsomemore[3], 1)) : 0; // use the actual value or a default (zero in this case)

some other points -

 

1) the mysql_ database library is depreciated and all code should be switched over to use the msyqli or pdo database libraries.

 

2) you should escape data being put into a query using the database library's string escape function or even better use prepared queries. addslashes that you are using doesn't escape all the possible characters in all the possible character sets and can allow sql injection.

 

3) if you are regularly inserting a large amount of data, a multi-value insert query, inserting 2-5K rows at a time, will run many times faster than inserting rows one at a time.

[PaulRyan]

Wouldn't it be $splititupsomemore[4] ? Unless I'm missing something.

 

There are 5 elements to each line, and the last one is the element that may or may not be there.

[mac_gyver]

^^^ if that's meant to be about my post, the line i posted was just an example of how to use isset() to avoid the errors.

 

the current errors the op is getting are on these lines -

40 - $splititupsomemore = explode(',', $line_data[1]);
49 - $pd_price = str_replace("\r", "", addslashes($splititupsomemore[1]));
51 - $pd_spc = addslashes(substr($splititupsomemore[2], 1));
57 - $pd_spc_price = addslashes(substr($splititupsomemore[3], 1));

[zzaras]

the offset errors you are seeing were probably always present, but php's error_reporting/display_errors settings were hiding them.

 

you are attempting to reference array elements that don't exist because the data didn't have some of the optional elements. the proper coding method would be to test if an optional piece of data exists and then take an appropriate action (use a specific default value when it doesn't exist.) you can use isset() to test if a php variable/array element exists. the Ternary Operator (short form if an if/else statement) makes writing the code straightforward -

$pd_spc_price = isset($splititupsomemore[3]) ? addslashes(substr($splititupsomemore[3], 1)) : 0; // use the actual value or a default (zero in this case)

some other points -

 

1) the mysql_ database library is depreciated and all code should be switched over to use the msyqli or pdo database libraries.

 

2) you should escape data being put into a query using the database library's string escape function or even better use prepared queries. addslashes that you are using doesn't escape all the possible characters in all the possible character sets and can allow sql injection.

 

3) if you are regularly inserting a large amount of data, a multi-value insert query, inserting 2-5K rows at a time, will run many times faster than inserting rows one at a time.

Thanks for the help with the errors. I did read about the isset() function but wasn't 100% sure how to implement it.

 

Your points make sense and I will go over that in a few. However, I am still trying to get that last value in the CSV line "The original price" coloumn to upload properly.

What in this code is wrong that is not letting it work?

I have the if statement set to true because I want the code to upload what is in that field if it is there and if it isn't it will upload the content from the $pd_price field.

$pd_spc_price = isset($splititupsomemore[3]) ? addslashes(substr($splititupsomemore[3], 1)) : 0; 
if($pd_spc_price == TRUE){      
	$spc_price = $pd_spc_price;     
} else {      
	$spc_price = $pd_price;     
} 

Now onto your points

1. I hadn't heard that changed but I will read up on that and work on it.

 

2. How would I go about doing that? I am assuming the way I built this code 5 years ago was decent but obviously things get better and I haven't updated.

 

3. The most that is imported is 500 max. Although that does make sense I wouldn't know how to go about that. Looks like I need to start studying some more.

 

I did find this sample code online and it showed what seemed like an easier way to import a CSV. My downside on this is trying to make the values of the CSV file upload different things in the database.

<?php
/********************************/
/* Code at http://legend.ws/blog/tips-tricks/csv-php-mysql-import/
/* Edit the entries below to reflect the appropriate values
/********************************/
$databasehost = "localhost";
$databasename = "test";
$databasetable = "sample";
$databaseusername ="test";
$databasepassword = "";
$fieldseparator = ",";
$lineseparator = "\n";
$csvfile = "filename.csv";
/********************************/
/* Would you like to add an ampty field at the beginning of these records?
/* This is useful if you have a table with the first field being an auto_increment integer
/* and the csv file does not have such as empty field before the records.
/* Set 1 for yes and 0 for no. ATTENTION: don't set to 1 if you are not sure.
/* This can dump data in the wrong fields if this extra field does not exist in the table
/********************************/
$addauto = 0;
/********************************/
/* Would you like to save the mysql queries in a file? If yes set $save to 1.
/* Permission on the file should be set to 777. Either upload a sample file through ftp and
/* change the permissions, or execute at the prompt: touch output.sql && chmod 777 output.sql
/********************************/
$save = 1;
$outputfile = "output.sql";
/********************************/

if(!file_exists($csvfile)) {
 echo "File not found. Make sure you specified the correct path.\n";
 exit;
}
$file = fopen($csvfile,"r");
if(!$file) {
 echo "Error opening data file.\n";
 exit;
}
$size = filesize($csvfile);
if(!$size) {
 echo "File is empty.\n";
 exit;
}
$csvcontent = fread($file,$size);
fclose($file);
$con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
@mysql_select_db($databasename) or die(mysql_error());
$lines = 0;
$queries = "";
$linearray = array();
foreach(split($lineseparator,$csvcontent) as $line) {
 $lines++;
 $line = trim($line," \t");
 
 $line = str_replace("\r","",$line);
 
 /************************************
 This line escapes the special character. remove it if entries are already escaped in the csv file
 ************************************/
 $line = str_replace("'","\'",$line);
 /*************************************/
 
 $linearray = explode($fieldseparator,$line);
 
 $linemysql = implode("','",$linearray);
 
 if($addauto)
  $query = "insert into $databasetable values('','$linemysql');";
 else
  $query = "insert into $databasetable values('$linemysql');";
 
 $queries .= $query . "\n";
 @mysql_query($query);
}
@mysql_close($con);
if($save) {
 
 if(!is_writable($outputfile)) {
  echo "File is not writable, check permissions.\n";
 }
 
 else {
  $file2 = fopen($outputfile,"w");
  
  if(!$file2) {
   echo "Error writing to the output file.\n";
  }
  else {
   fwrite($file2,$queries);
   fclose($file2);
  }
 }
 
}
echo "Found a total of $lines records in this csv file.\n";

?>

Edited August 8, 2013 by zzaras

[mac_gyver]

in looking at your code more, the code that is exploding the data is having a problem with the lines that have the y","13.95" on the end because that price will be in $line_data[2], along with the closing double-quote.

 

you need to use either fgetcsv() or str_getcsv() (php5.3 or greater) to parse your csv data. the following is how you can do this using str_getcsv -

$file = $_FILES["phile"]["tmp_name"];

$lines = file($file,FILE_IGNORE_NEW_LINES|FILE_SKIP_EMPTY_LINES); // read the lines into an array
array_shift($lines); // remove header line
$lines = array_map('str_getcsv',$lines); // convert each csv line to an array

// loop over the lines and process them
foreach($lines as $line){
    echo '<pre>'; print_r($line); // examine data to see what it is

    // your processing code...
    
}