PHP mysql loop goes out of memory

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mickael42
Forum Newbie
Posts: 4
Joined: Thu Oct 09, 2014 8:21 am

PHP mysql loop goes out of memory

Post by mickael42 »

Hi everyone,

My php script stop running because it goes out of memory.
Here is my code:

Code: Select all


$files = array_diff(scandir($dir_name), array('..', '.'));
	if (!empty($files))
	{
		$all_lines = array();
		foreach ($files as $file)
		{
			if (pathinfo($file, PATHINFO_EXTENSION) == "csv")
			{
				$fp = fopen($dir_name.$file, 'r');
				while (($line = fgetcsv($fp, 0, $delimiter)) !== FALSE)
					$all_lines[] = array_map('addslashes', $line);
				fclose($fp);
				//unlink($file);
			}
		}
	}
/*the 1st part is done properly*/
foreach ($all_lines as $line)
	{
		$my_request = "INSERT INTO $table_name VALUES ('$line[0]'";
		$i = 1;
		while ($i < $maxcol)
		{
			if (isset($line[$i]))
				$my_request = $my_request.", '$line[$i]'";
			else
				$my_request = $my_request.", ''";
			$i++;
		}
		$my_request = $my_request.")";
		if (mysqli_query($connect_id, $my_request) == FALSE)
		{
			echo "<p>$my_request</p>";
			exit("Error while updating the table: $table_name.");
		}
	}
How the 2nd part can stop running... its only a foreach loop, if its work once, it should work unlimited times, right ? I used all the same variables, i never keep new informations, i just use what i already have... how the memory use can increase ? :banghead:

i run the script like 5 times on a csv with more than 600k rows.
And with exactly the same script it stop at different moments.
Number of request done before it stop : 205 286 // 200 514 // 192 429 // 211 164 // ...

Thank you,

Mickael.
Last edited by mickael42 on Thu Oct 09, 2014 8:45 am, edited 1 time in total.
mickael42
Forum Newbie
Posts: 4
Joined: Thu Oct 09, 2014 8:21 am

Re: PHP mysql loop goes out of memory

Post by mickael42 »

Here is the 2nd way i tried, i read lot of topic that say it better to do loop with function to avoid leaks but i doesnt solve the problem.

Code: Select all

$files = array_diff(scandir($dir_name), array('..', '.'));
	if (!empty($files))
	{	
		if (($connect_id = mysqli_connect($db_server, $user_name, $user_pw, $db_name)) == FALSE)
			exit("Connection to the database: $db_name into the server: $db_server as $user_name failed.");
		
		foreach ($files as $file)
		{
			if (pathinfo($file, PATHINFO_EXTENSION) == "csv")
			{
                        $fp = fopen($dir_name.$file, 'r');
				while (($line = fgetcsv($fp, 0, $delimiter)) !== FALSE)
				 	insert_update(array_map('addslashes', $line), $connect_id);
				fclose($fp);
			}
		}
		mysqli_close($connect_id);
	}
The function

Code: Select all

function insert_update($row, $connect_id)
	{
		$table_name = "name";
		$maxcol = 42;
				
		$my_request = "INSERT INTO $table_name VALUES ('$row[0]'";
		$i = 1;
		while ($i < $maxcol)
		{
			if (isset($row[$i]))
				$my_request = $my_request.", '$row[$i]'";
			else
				$my_request = $my_request.", ''";
			$i++;
		}
		$my_request = $my_request.")";
		if (mysqli_query($connect_id, $my_request) == FALSE)
		{
			echo "<p>$my_request</p>";
			exit("Error while updating the table: $table_name.");
		}
	}
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: PHP mysql loop goes out of memory

Post by Celauran »

Before I even start digging through the code, have you checked PHP's memory limit? Tried increasing it? Also, have you considered that PHP might not be the best tool for this job?
mickael42
Forum Newbie
Posts: 4
Joined: Thu Oct 09, 2014 8:21 am

Re: PHP mysql loop goes out of memory

Post by mickael42 »

Yes,
ini_set('memory_limit','-1');

I have to do it in PHP.
mickael42
Forum Newbie
Posts: 4
Joined: Thu Oct 09, 2014 8:21 am

Re: PHP mysql loop goes out of memory

Post by mickael42 »

Ok got it, the loop was 'too fast' so my script stop because the server wasnt supported too many request in the same time.
I add usleep(50) and it work perfectly fine.

Thank you all.
Post Reply