Fatal error: Allowed memory size of 134217728 bytes exhausted

[54uydf]

Hi, I'm getting this error:
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 576 bytes) in 'path' on line 207
what I'm doing is getting an excel file with about 8000+ rows and trying to move them into a table in my DB.
a FOR loop is putting data from each row of the excel file into a class vars c,f,g..,m. and then calls this insert() func (for every row). here's a part of the code and line 207 is highlighted. how can I fix this error?

function insert() {
	$sql = "INSERT INTO grades VALUES ( '".$this->c."','".
		$this->f."','".$this->g."','".
		$this->h."','".$this->i."','".
		$this->j."','".$this->k."','".
		$this->l."','".$this->m."' )";
	$dblink = null;
	try	{
		[B]$dblink = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD);[/B]		
		mysql_select_db(DB_NAME,$dblink);
		mysql_set_charset('utf8', $dblink) or die ('Error changing charset to hebrew');
	} catch(Exception $ex) {
		echo "Could not connect to " . DB_HOST . ":" . DB_NAME . "\n";
		echo "Error: " . $ex->message;
		exit;
	}
		
	$retid = mysql_query($sql,$dblink) or die(mysql_error());
	if (!$retid) { 
		echo( mysql_error()); 
	}
	
	if(is_resource($dblink)) {		
		mysql_close($dblink);
	}		
	return $retid;	
}

I can PM the whole class code if that is necessary.

thank you

[pritaeas]

You keep opening and closing the mysql connection. Try opening it before your loop and closing it when you are done.

[54uydf]

alright, I did that but now I'm getting the same error only from different script.
it's a PHPExcel script, and it's too long to post here..I'll post the function with the line that gives error..but I don't know what I can change here, have no idea where is this function is called from even.. is there a way to just allow more memory? don't know if it's a good idea, but I gotta make this thing work somehow

/**
	 * Get cell at a specific coordinate by using numeric cell coordinates
	 *
	 * @param	string $pColumn		Numeric column coordinate of the cell
	 * @param	string $pRow		Numeric row coordinate of the cell
	 * @return	PHPExcel_Cell		Cell that was found
	 */
	public function getCellByColumnAndRow($pColumn = 0, $pRow = 1)
	{
		$columnLetter = PHPExcel_Cell::stringFromColumnIndex($pColumn);
		$coordinate = $columnLetter . $pRow;

		if (!$this->_cellCollection->isDataSet($coordinate)) {
			[B]$cell = $this->_cellCollection->addCacheData($coordinate, new PHPExcel_Cell($columnLetter, $pRow, null, PHPExcel_Cell_DataType::TYPE_NULL, $this));[/B]			$this->_cellCollectionIsSorted = false;

			if (PHPExcel_Cell::columnIndexFromString($this->_cachedHighestColumn) < $pColumn)
				$this->_cachedHighestColumn = $columnLetter;

			$this->_cachedHighestRow = max($this->_cachedHighestRow,$pRow);

			return $cell;
		}

		return $this->_cellCollection->getCacheData($coordinate);
	}

[cwarn23]

No wonder why. You php configuration is limited to 13M of data. Your excel file is probably 5 or 10M. I would link you to some recourses on how to fix it but again happygeek has told me not to do so. So I can only say what the problem is and not give paragraphs of detailed descriptions on things that cause the problem and how to solve it along with side effects on some servers as well as warnings as per usage.

[54uydf]

my file is only 601 KB, has 4 sheets, and about 2000 rows on each sheet..

[mschroeder]

Your memory limit is set to 128MB not 13MB. In my experience with PHPExcel, you can expect between 1 - 10KB of memory usage on average per cell. Formulas and formatting causing even larger memory footprints.

While it will require converting your workbook into 4 csv files, you will then be able to iterate over the individual files by line and your memory footprint will be tiny.

• Create one database connection.
• Open the file by creating an instance of an SplFileObject
• Set the READ_CSV flag (http://www.php.net/manual/en/splfileobject.setflags.php)
• Set the CSV Controls to match your file's delimiter and enclosure (http://www.php.net/manual/en/splfileobject.setcsvcontrol.php)
• Iterate over the files, line by line (see the example on the setCsvControl manual page).
• Execute an insert for each line OR build a large insert statement and execute only 1 query.
  • Executing an insert for each line, will allow you to use prepared statements and will use less memory, but will take longer to execute
  • Executing one large query will make using prepared statements more difficult, will use more memory, but will execute faster.

If this is a big project and you really must work with Excel files, than I would suggest budgeting the money for the excellent LibXL, http://www.libxl.com/home.html This library also has a native php extension https://github.com/iliaal/php_excel

[54uydf]

thank you, I haven't heard of this library before, eitherway I've been working for few months with phpexcel and it's sort of too late to change the code or the library..but good to know for next time..which I hope never comes lol
no more working with excel for me please!

[digital-ether]

Do you have more memory you that you can increase your PHP memory limit to? Edit PHP.ini.