Solved

How do I fix this undefined offset error?

Posted on 2011-09-15
6
2,510 Views
Last Modified: 2012-05-12
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
Comment
Question by:rlb1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 36546660
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
 
LVL 34

Expert Comment

by:Beverley Portlock
ID: 36546661
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
 

Author Comment

by:rlb1
ID: 36546678
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 34

Assisted Solution

by:Beverley Portlock
Beverley Portlock earned 200 total points
ID: 36546695
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
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 300 total points
ID: 36549358
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
 

Author Closing Comment

by:rlb1
ID: 36557791
Thanks
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with mod_substitute 18 80
splitting a sting into elements 6 76
[form-control] Retain values after a POST action 21 76
php time 12 30
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
This article discusses how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question