Solved

How do I fix this undefined offset error?

Posted on 2011-09-15
6
2,408 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
  • 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 108

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now