Loading really large files

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

User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

This is easily the most frustrating piece of code I've ever worked on. It's probably a combination of my getting almost no sleep, stress unrelated to php and the fact that I never work with large files, but I could probably kill somebody right now. :evil:

Take a look at the lines I marked... where it says "always echos 1" and "increments"... what is causing this behavior?? am I just really sleepy and missing something?

Code: Select all

if ($r = mysql_query($query))
	{
		/**
		 * Loop through each product
		 */
		 $i = 0;
		while ($row = mysql_fetch_assoc($r))
		{
			$this_row_stock = 0;
			$i++;
			
			// Output part number and name
			echo "<td>" . htmlentities($row['pItem']) . "</td>\n";
			echo "<td>" . htmlentities($row['pName']) . "</td>\n";

			/** 
			 * Loop through each file
			 */
			foreach ($csv_files as $key => $file)
			{

				if (!isset($filehandles[$key])) $filehandles[$key] = fopen($file['src'], "r");
				
				/**
				 * Loop through each line
				 */
				while (($data = fgetcsv($filehandles[$key], 1000, $file['delim'], $file['enc'])) !== FALSE)
				{
					if (getAlnum($row['pItem']) == $data[$file['sku_position']])
					{
						echo 'found: ' . $row['pItem'];
					}
				echo $i . " "; // Always echos 1
				}
				echo $i . " "; // increments
			}
			echo '<td>' . $this_row_stock . '</td></tr>';
			$result = mysql_query("
				UPDATE products
				SET pInStock = " . $this_row_stock . "
				WHERE pId = '" . $row['pId'] . "'");
		}
		
		// Close files now that we're done with them
		foreach ($filehandles as &$file)
		{
			fclose($file);
		}
		
	}
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

8O

That is bizarre!

For the database I would consider using a temporary memory (HEAP) table:
http://dev.mysql.com/doc/refman/4.1/en/ ... ngine.html

Maybe you could re-write the loop from a while() to a for() and then just include the line $row=MySQL_fetch_assoc($r) inside the loop... see if that works any better for you.

Good luck NSG!
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

nope, same result... :cry: I am at a loss.

It has something to do with the files opening and closing... it acts like it can only open the files in the first loop of products. :?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

:D :D :D :D

fseek() is my friend.

:oops: should have thought of that long ago. oh well at least I fixed it.
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Post by Luke »

alright, it's working now, but very slowly. Anybody have any suggestions on optimization?

Code: Select all

<?php
set_time_limit(1000);

$csv_files = array(
	'petra' => array('stock_position' => 3, 'sku_position' => 0, 'delim' => ',',  'enc' => '"', 'src' => './petraprodlist.csv'),
	'wynit' => array('stock_position' => 5, 'sku_position' => 0, 'delim' => "\t", 'enc' => '"',  'src' => './407059-832.txt'),
	'cwr'   => array('stock_position' => 2, 'sku_position' => 1, 'delim' => "\t", 'enc' => '"',  'src' => './productlist251234.txt')
);

/***** End configuration - do not edit beyond this line *****/

$filehandles = array();
if (isset($_POST['action']) && $_POST['action'] == 'run')
{
?>
<table border="1" cellspacing="0" cellpadding="5">
 <tr>
  <th>Part #</th>
  <th>Product</th>
<?php foreach ($csv_files as $key => $file): ?>
  <th><?php echo ucwords($key) ?></th>
<?php endforeach; ?>
  <th>Total</th>
 </tr>
<?php

	require_once './vsadmin/db_conn_open.php';
	
	function getAlnum($value)
	{
		return preg_replace('/[^[]]/', '', $value);
	}

	$query = "SELECT pId, pItem, pName, pInStock FROM `products`";
	if ($r = mysql_query($query))
	{
		/**
		 * Loop through each product
		 */
		for ($i=0; $i < mysql_num_rows($r); $i++)
		{
			
 			$row = mysql_fetch_assoc($r);
			$this_row_stock = 0;

			// Output part number and name
			echo "<tr><td>" . htmlentities($row['pItem']) . "</td>\n";
			echo "<td>" . htmlentities($row['pName']) . "</td>\n";

			/** 
			 * Loop through each file
			 */
			foreach ($csv_files as $key => $file)
			{
				
				$found = false;
				if (!isset($filehandles[$key])) $filehandles[$key] = fopen($file['src'], "r");

				/**
				 * Loop through each line
				 */
				 
				if (!is_resource($filehandles[$key])) echo 'NO RESOURCE!';
				
				while (($data = fgetcsv($filehandles[$key], 1000, $file['delim'], $file['enc'])) !== FALSE)
				{
					
					if (getAlnum($row['pItem']) == getAlnum($data[$file['sku_position']]))
					{
						$found = true;
						if ($data[$file['stock_position']] == 0)
						{
							echo '<td style="background-color: #fcc;color: #c00;">0</td>';
						}
						else
						{
							$add_to_stock = ($data[$file['stock_position']] > 0) ? $data[$file['stock_position']] : 0;
							$this_row_stock += $add_to_stock;
							unset($add_to_stock);
							echo '<td>' . $data[$file['stock_position']] . '</td>';
						}
						continue;
					}
				}
				
				unset($data);
				fseek($filehandles[$key], 0);
				
				if (!$found)
				{
					echo '<td style="background-color: #ddd; color: #555;">n/a</td>';
				}
				
			}
			if ($this_row_stock)
			{
				echo '<td style="background-color: #fcc;color: #c00;">' . $this_row_stock . '</td></tr>';
			}
			else
			{
				echo '<td>' . $this_row_stock . '</td></tr>';
			}
			
			$result = mysql_query("
				UPDATE products
				SET pInStock = " . $this_row_stock . "
				WHERE pId = '" . $row['pId'] . "'");
		}
		
		// Close files now that we're done with them
		foreach ($filehandles as &$file)
		{
			fclose($file);
		}
		
	}
	echo '</table>';
}
else
{
	?>
	<form method="post" action="#">
	 <input type="hidden" name="action" value="run">
	 <input type="submit" value="Run Stock Updater">
	</form>
	<?php
}

?></pre>
Post Reply