Hi,

I am having an issue with updating my database with info from an excel spreadsheet. I am using php-excelreader from:

http://sourceforge.net/projects/phpexcelreader/

Here is what I have thus far:

<?php
require_once 'reader.php';
$Host = "...";
$User = "...";
$Password = "...";
$DBname = "...";

$Link = mysql_connect ($Host, $User, $Password) or die ('Database error.');
mysql_select_db($DBname);

$filename="updates.xls";
$prod=parseExcel($filename);

function parseExcel($excel_file_name_with_path)
{

	$data = new Spreadsheet_Excel_Reader();
	// Set output Encoding.
	$data->setOutputEncoding('UTF8');
	$data->read($excel_file_name_with_path);
$count=0;
	for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) {
		for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
			if($j==1){
				$sku=trim($data->sheets[0]['cells'][$i][$j]);
					}
			if ($j==2){
				$desc=addslashes(trim($data->sheets[0]['cells'][$i][$j]));
					}
			if ($j==3){
				$list=addslashes(trim($data->sheets[0]['cells'][$i][$j]));
					}
			if ($j==4){
				$specs=addslashes(trim($data->sheets[0]['cells'][$i][$j]));
					}
		}
$count++;
$Query72 ="UPDATE inventory set description='$desc', specs='$specs', list='$list' where sku like '$sku%'";
mysql_query ($Query72) or die ('Database errorr.'); 

if (mysql_affected_rows() >= 1) {print("ROW $count - update successful!<br>");} else {print("ROW $count - update failed!<br>");}

	}
	
}

The spreadsheet has about 23000 rows, and 25 columns. I just need to update 3 fields in the database with 3 cells in the spreadsheet based on the first cell. For some reason, it will update 2 of the fields in the database, but not the 3rd (and in fact, it erases the data that was already in it). Below is a simplified version of the spreadsheet with only 4 rows, I tried to boil it down when it wasn't working. (cells are delimited by | in the example

AHSD | Waterproof, breathable,... | Performance fit # Waterpr... | Avg Weight: 1...

it is the 4th cell that isn't updating (the 'specs' field in the database).

I am sure I am just overlooking something really dumb. If anyone can lend a hand, I would be greatly appreciative!

Thanks!

Recommended Answers

All 3 Replies

I gues you have to start indexing $j from 0 as the first column is 0, second 1, third 2, etc.


or:

#
if($j==1){
#
$sku=trim($data->sheets[0]['cells'][$i][$j-1]);
#
}
#
if ($j==2){
#
$desc=addslashes(trim($data->sheets[0]['cells'][$i][$j-1]));
#
}
#
if ($j==3){
#
$list=addslashes(trim($data->sheets[0]['cells'][$i][$j-1]));
#
}
#
if ($j==4){
#
$specs=addslashes(trim($data->sheets[0]['cells'][$i][$j-1]));
#
}

Deprecated: Assigning the return value of new by reference is deprecated in /.../Excel/Reader.php On line 261

What is this error .How to overcome this.


Advance Thanks

Take Out the "&" symbol for that line ......... and try it

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.