Solved

How do I fix this undefined offset error?

Posted on 2011-09-15
6
2,543 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

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

Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

617 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