• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2768
  • Last Modified:

How do I fix this undefined offset error?

Experts,

I have a script that works perfectly until the last section.  Everything works fine until the CSV to MySQL import script.   It appears that the for each loop is not working.  All of the results appear on data[0]  instead of data[0], data[1], data[2]. data[3] etc

I am getting the following errors on the section below:  (Line 152 is the insert statement)

Notice: Undefined offset: 1 in /home/web/public_html/get-trinity.php on line 152

Notice: Undefined offset: 2 in /home/web/public_html/get-trinity.php on line 152

Notice: Undefined offset: 3 in /home/web/public_html/get-trinity.php on line 152

Notice: Undefined offset: 4 in /home/web/public_html/get-trinity.php on line 152

Notice: Undefined offset: 5 in /home/web/public_html/get-trinity.php on line 152

//  UPLOAD PRODUCT INFO  *******************************************************************

mysql_query("TRUNCATE TABLE `1trinity_excel_download`");

// CSV into MySQL import script by Mark Randall (mark@hostcobalt.com)
//
// Include the mysql connect page
//include("connect.php");
// Set the filename that you want to import
$filename="trinity.csv";
//open the file
$handle = fopen($filename, "r");
//this is what causes it to skip
fgetcsv($handle,",");


//begin looping through the lines
while (($data = fgetcsv($handle, ",")) !== FALSE)
{

// loop all column values and escape special characters  // [b]what is wrong here??[/b]
foreach ($data as $key => $value){
  $data[$key] = mysql_real_escape_string($value);
}
  [b]// Line 152[/b]
$import="INSERT into `1trinity_excel_download` (item_id,lineItemDescription,description,Brand,notes,price1) values ('".mysql_real_escape_string($data[0])."','".mysql_real_escape_string($data[1])."','".mysql_real_escape_string($data[2])."','".mysql_real_escape_string($data[3])."','".htmlspecialchars($data[4], ENT_QUOTES, 'utf-8')."','".mysql_real_escape_string($data[5])."')"; 


//execute the mysql query
mysql_query($import) or die(mysql_error());
}
//close the file
fclose($handle);
//output a message saying its done.

$msgprod="Product Import done";
echo $msgprod;
echo "<BR>";
	

Open in new window



Full Code:

<?php
	ini_set("display_errors","1");
	ERROR_REPORTING(E_ALL);
	
	
	
	mysql_connect("localhost","username","password") or die("Unable to connect to SQL server");
     mysql_select_db('database') or die("Unable to SELECT DB");

     echo "Connected to DB";
     echo "<br /><br />";
	
	
	
	$url = 'http://distributor.com/downloads/index.cfm';
		
	
	//$orig = file_get_contents($url);

	
	$fields = array(
	  
	    'username'=>urlencode('mmfeed'),
	    'password'=>urlencode('inventory2'),
	);
	
	//url-ify the data for the POST
	$fields_string = '';
	foreach($fields as $key=>$value) { $fields_string .= $key.'='.$value.'&'; }
	rtrim($fields_string,'&');
				
	$ch = curl_init(); // initialize curl handle
	curl_setopt($ch, CURLOPT_HEADER, 0);
	curl_setopt($ch, CURLOPT_VERBOSE, 1);
	curl_setopt($ch, CURLOPT_USERAGENT, "Mozilla/4.0 (compatible;)");
	curl_setopt($ch, CURLOPT_AUTOREFERER, false);
	curl_setopt($ch, CURLOPT_CONNECTTIMEOUT,7);
	curl_setopt($ch, CURLOPT_REFERER, $url);
	curl_setopt($ch, CURLOPT_URL,$url); // set url to post to
	curl_setopt($ch, CURLOPT_FAILONERROR, 0);
	curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);// allow redirects
	
	curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
	curl_setopt($ch, CURLOPT_COOKIEFILE, 'cookie/cookie.txt');
	curl_setopt($ch, CURLOPT_COOKIEJAR, 'cookie/cookie.txt');

	curl_setopt($ch, CURLOPT_RETURNTRANSFER,1); // return into a variable
	curl_setopt($ch, CURLOPT_TIMEOUT, 50); // times out after 50s
	curl_setopt($ch,CURLOPT_POST,count($fields));
	curl_setopt($ch,CURLOPT_POSTFIELDS,$fields_string);
	
	
	$buffer = curl_exec($ch); // run the whole process
	if(curl_exec($ch) === false)
	{
	    echo 'Curl error: ' . curl_error($ch);
	    exit;
	}
	
	
	echo $buffer;
	
	


	
	preg_match_all('/<a href="(\/admin\/downloads\/Inventory_(.*).xls)">Right Click to Download<\/a>/smU', $buffer, $download_links);
	
	$download_base = 'http://distributor.com';

	
	$count = count($download_links[1]);



	$newest_download_url = $download_base.$download_links[1][($count-1)];

	$fp = fopen (dirname(__FILE__) . '/trinity.xls', 'w+');//This is the file where we save the information
		
	$url_2 = $newest_download_url;
	curl_setopt($ch, CURLOPT_URL,$url_2); // set url to go fetch
	curl_setopt($ch, CURLOPT_TIMEOUT, 60);               
	curl_setopt($ch, CURLOPT_FILE, $fp);
	
	$buffer = curl_exec($ch); // run the process
	//echo $buffer;
	fclose($fp);
	
	
	


require_once 'reader.php';
$excel = new Spreadsheet_Excel_Reader();
$excel->setOutputEncoding('CP1251');
$excel->read('trinity.xls');
$x=1;
$sep = "|";
ob_start();
while($x<=$excel->sheets[0]['numRows']) {
$y=1;
$row="";
while($y<=$excel->sheets[0]['numCols']) {
$cell = isset($excel->sheets[0]['cells'][$x][$y]) ? $excel->sheets[0]['cells'][$x][$y] : '';
$row.=($row=="")?"".$cell."":"".$sep."".$cell."";
$y++;
}
echo $row."\n"; 
$x++;
}
$fp = fopen("trinity.csv",'w');
fwrite($fp,ob_get_contents());
fclose($fp);
ob_end_clean();



//This is where the problem is:


//  UPLOAD PRODUCT INFO  *******************************************************************

mysql_query("TRUNCATE TABLE `1trinity_excel_download`");

// CSV into MySQL import script by Mark Randall (mark@hostcobalt.com)
//
// Include the mysql connect page
//include("connect.php");
// Set the filename that you want to import
$filename="trinity.csv";
//open the file
$handle = fopen($filename, "r");
//this is what causes it to skip
fgetcsv($handle,"|");


//begin looping through the lines
while (($data = fgetcsv($handle, "|")) !== FALSE)
{

// loop all column values and escape special characters
foreach ($data as $key => $value){
  $data[$key] = mysql_real_escape_string($value);
}
$import="INSERT into `1trinity_excel_download` (item_id,lineItemDescription,description,Brand,notes,price1) values ('".mysql_real_escape_string($data[0])."','".mysql_real_escape_string($data[1])."','".mysql_real_escape_string($data[2])."','".mysql_real_escape_string($data[3])."','".htmlspecialchars($data[4], ENT_QUOTES, 'utf-8')."','".mysql_real_escape_string($data[5])."')"; 


//execute the mysql query
mysql_query($import) or die(mysql_error());
}
//close the file
fclose($handle);
//output a message saying its done.

$msgprod="Product Import done";
echo $msgprod;
echo "<BR>";
	
					
?>					

Open in new window



0
rlb1
Asked:
rlb1
  • 3
  • 2
2 Solutions
 
Beverley PortlockCommented:
You are seeing the NOTICEs because you have this in your code

      ERROR_REPORTING(E_ALL);

Change it to

      ERROR_REPORTING(E_ALL & ~E_NOTICE);

and the NOTICE messages will go away. They are usually unimportant and worth checking ocassionally but most times you can simply ignore them.
0
 
Beverley PortlockCommented:
Actually, re-reading the question, is your problem the NOTICE messages or something else? This bit All of the results appear on data[0]  instead of data[0], data[1], data[2]. data[3] etc has me confused.
0
 
rlb1Author Commented:
BPortlock,

Thanks for your help!

The problem is not the NOTICE messages.  The problem appears to be in the foreach statement (I think) or the csv script itself.   The foreach statement is not seperating each value like it normally does.   All of the results are populating the first field of the database which is data[0].  

data[1] through data[14] have no results.  

data[0] results are:  field1 | field2 | field3 | etc  (all combined)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Beverley PortlockCommented:
Are you saying that a pipe | is the delimiter rather than a comma? If so then you need to alter this

fgetcsv($handle,",");

to this

fgetcsv($handle, 1024, "|");

(see http://uk.php.net/fgetcsv for more info). You will also notice that I have stuck a line length in as the SECOND parameter because the data delimiter is the THIRD parameter. I have assumed that your line length is shorter than 1024, if not then bung it up a bit and try again.

0
 
Ray PaseurCommented:
The code snippets do not match.  One uses a comma delimiter and one uses a pipe delimiter.

If it's not the pipe delimiter, it may be a blank line in the file.

But is this script reading this URL on line 15 of the "Full code" posted above?
http://distributor.com/downloads/index.cfm

Maybe if you post the CREATE TABLE statement for 1trinity_excel_download and the trinity.csv file, we can show you how this kind of import is done.

See the code snippet here.  From the "Full code" above, this appears to be more like what you might want from line 129 onward.
// Set the filename that you want to import
$filename="trinity.csv";

//open the file
$handle = fopen($filename, "r");

// TEST TO SEE IF THE FILE OPENED CORRECTLY
if (!$handle) die("UNABLE TO OPEN $filename");

// READ AND DISCARD THE FIRST LINE OF THE CSV FILE -- WHY?
fgetcsv($handle,"|");


//begin looping through the lines
while (($data = fgetcsv($handle, "|")) !== FALSE)
{
    // REMOVE THIS LOOP - YOU ARE ESCAPING THESE FIELDS TWICE
    // foreach ($data as $key => $value)
    // {
    //     $data[$key] = mysql_real_escape_string($value);
    // }
    
    // VISUALIZE THE DATA
    echo "<br/>" . PHP_EOL;
    print_r($data);
    
    // CONSTRUCT THE QUERY STRING
    $import="INSERT into `1trinity_excel_download` (item_id,lineItemDescription,description,Brand,notes,price1) values ('".mysql_real_escape_string($data[0])."','".mysql_real_escape_string($data[1])."','".mysql_real_escape_string($data[2])."','".mysql_real_escape_string($data[3])."','".htmlspecialchars($data[4], ENT_QUOTES, 'utf-8')."','".mysql_real_escape_string($data[5])."')"; 

    // DISPLAY THE QUERY STRING SO WE CAN SEE IF IT MAKES SENSE
    echo "<br/>$import";

    //execute the mysql query
    mysql_query($import) or die(mysql_error());
}

Open in new window

0
 
rlb1Author Commented:
Thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now