Data Feed TXT Script Takes A Long Time To Be Created - Coding or Server??

olimits7 · January 30, 2009

Hi,

I have a PHP script that runs and creates a *.txt data feed of all the products on my webste. I currently have around 130,000 products; which I don't think is that high of a number compared to the large ecommerce website.

My issue is it takes more than 5 minutes to create a simple *.txt data feed of only 20,000 records; not even the whole 130,000 records!!

1. So my question is can I increase the speed of this *.txt data feed process??

2. Could this be an issue with the code/queries not being optimized to the fullest??

3. Or is this a server issue??

Thank you,

olimits7

printf · January 30, 2009

No way to tell without seeing your code...

premiso · January 30, 2009

How are you gathering the data? Are you fetching it from your website and parsing?

Can you show us the code, so we can see how it is currently being done?

olimits7 · January 30, 2009

Hi,

I think the data is being pulled straight from mySQL database.

This is the code for the script that runs. I outsourced this project and I want to make sure the code is written clean, logical, and optimized; so it creates the data feed as fast as possible.

Can you tell me if the code below is written the best way it can be??

  
<?php
  require('includes/application_top.php');
  set_time_limit('60');
  ob_start();

  $action = (isset($_GET['action']) ? $_GET['action'] : '');

  if (tep_not_null($action)) {
    switch ($action) {
    	case 'set':
    		set_time_limit(0);
    		$active = (int)$_GET['active'];

    		//Get all products
    		if ($active == 2)
    			$status = 0;
    		else
    			$status = (int)!$active;
    		$products_query = tep_db_query("SELECT products_id FROM ".TABLE_PRODUCTS." WHERE products_status = ".$status);
    		$products_array = Array();

    		if ($active < 2)
    		{
    			$query = "UPDATE ".TABLE_PRODUCTS." p LEFT JOIN (".TABLE_VENDORS_TO_PRODUCTS." v2p JOIN ".TABLE_VENDORS." v ON (v.vendors_id = v2p.vendors_id)) ON (v2p.products_id = p.products_id)
    				SET p.products_status = ".$active."
    			WHERE p.override = '0' and p.products_status = ".(int)!$active." AND ".(($active) ? 'v.vendors_status = 1 AND v2p.wholesale_price > 0' : '(v2p.products_id IS NULL OR v2p.wholesale_price = 0 OR v.vendors_status = 0)')."
    						";
			}
			else //Set non-vendors active
			{
    			$query = "UPDATE ".TABLE_PRODUCTS." p LEFT JOIN (".TABLE_VENDORS_TO_PRODUCTS." v2p JOIN ".TABLE_VENDORS." v ON (v.vendors_id = v2p.vendors_id)) ON (v2p.products_id = p.products_id)
    				SET p.products_status = 1
    			WHERE p.override = '0' and p.products_status = 0 AND (v2p.products_id IS NULL OR v2p.wholesale_price = 0 OR v.vendors_status = 0)";
			}

			ob_end_flush();

			$count = 0;
			$num_of_rows = tep_db_num_rows($products_query);
			$limit = ($num_of_rows < 100) ? $num_of_rows : 100;
			while ($product = tep_db_fetch_array($products_query))
			{
				$products_array[] = $product['products_id'];
				if (sizeof($products_array) == $limit)
				{
					tep_db_query($query." AND p.products_id IN (".join(',', $products_array).")");
					$count += mysql_affected_rows();
					$products_array = Array();
					echo ".";
					flush();
					if (++$row%100 == 0)
					{
						echo '<br />';
						flush();
					}
					//Recalculate limi
					$num_of_rows -= 100;
					$limit = ($num_of_rows < 100) ? $num_of_rows : 100;
				}
			}
			$messageStack->add($count.' prodcuts were updated', 'success');

    		//tep_redirect(tep_href_link(FILENAME_VENDORS, (isset($_GET['page']) ? 'page=' . $_GET['page'] . '&' : '') . 'vID=' . (int)$_GET['vID']));
    	break;
    	case 'upload':
    		set_time_limit(0);
    		if ($_POST['v2p_file_local'])
    		{
    			if (!file_exists(DIR_FS_ADMIN.'vendors/'.$_POST['v2p_file_local']))
    			{
    				$messageStack->add('Ëîêàëüíûé ôàéë âûáðàí íåâåðíî');
    				break;
				}
				$filename = DIR_FS_ADMIN.'vendors/'.$_POST['v2p_file_local'];
			}
			elseif ($_FILES['v2p_file']['name'])
			{
				if ($_FILES['v2p_file']['error'])
				{
					switch ($_FILES['v2p_file']['error'])
					{
						case UPLOAD_ERR_INI_SIZE:
							$messageStack->add(sprintf(ERROR_FILESIZE, round(ini_get('upload_max_filesize')/1024)));
						break;
						case UPLOAD_ERR_PARTIAL:
						case WARNING_NO_FILE_UPLOADED:
							$messageStack->add(ERROR_FILE_NOT_SAVED);
					}
					break;
				}
				$filename = $_FILES['v2p_file']['tmp_name'];
			}
			else
			{
				$messageStack->add('Please, select file for upload');
				break;
			}

			//Get array of all vendors
			$vendors_query = tep_db_query("SELECT vendors_name, vendors_id FROM ".TABLE_VENDORS);
			$v_names2ids_array = Array();
			$v_ids2names_array = Array();
			while ($vendor = tep_db_fetch_array($vendors_query))
				$v_names2ids_array[$vendor['vendors_name']] = $vendor['vendors_id'];
			$v_ids2names_array = array_flip($v_names2ids_array);

			//Parse file
			$count = 0;
			$row = 0;
//				$file = fopen($filename, 'r');
			$last = 0;
			ob_end_flush();

			$readed = file($filename);
			$theheaders_array = explode("\t", $readed[0]);
			unset($readed[0]);
			$lll = 0;
			$filelayout = array();
			foreach( $theheaders_array as $header ){
				$cleanheader = trim(str_replace( '"', '', $header));
				$filelayout[ $cleanheader ] = $lll++;
			}

			if(sizeof($readed) > 0){
				foreach($readed as $value){
					$items = explode("\t", $value);
					foreach( $filelayout as $fieldname => $fieldnumber ){
						$$fieldname = $items[ $fieldnumber ];
					}

					if (++$row%100 == 0){
						echo ".";
						flush();
					}
					if ($row%10000 == 0){
						echo '<br />';
						flush();
					}

					$VendorPrice = preg_replace("/[^\d\.,]/", '', $VendorPrice);
//						if (!is_numeric($VendorPrice)) continue;

					$vendor_id = (is_numeric($VendorID)) ? $VendorID : $v_names2ids_array[$VendorID];
					if (!$v_ids2names_array[$vendor_id]) continue;

					//Check if product exists
					$UPCNumber = str_replace("'", "", $UPCNumber);
					$products_query = tep_db_query("SELECT products_id, products_price, products_model FROM ".TABLE_PRODUCTS." WHERE products_model = '".tep_db_input($UPCNumber)."'");
					$vendors_query = tep_db_query("SELECT wholesale_price, inventory_levels FROM ".TABLE_VENDORS_TO_PRODUCTS." WHERE products_model = '".tep_db_input($UPCNumber)."' AND vendors_id = '".tep_db_input($VendorID)."'");
					$vend = tep_db_fetch_array($vendors_query);

					if(tep_not_null($InventoryLevels)){
						switch(trim($InventoryLevels)){
							case 'Available Now':
							case 'Short Wait':
								$InventoryLevels = '1';
								break;
							case 'Not Available':
							case 'Special Order':
								$InventoryLevels = '0';
								break;
						}

					}    

			//       if(VENDORS_UPLOAD_SKIP_UPC == 'true') { 
					if (tep_db_num_rows($products_query) == 1) {
						$product = tep_db_fetch_array($products_query);
						//If all ok, replace data into vendors_to_products table


						 $sql_data_array = array('vendors_id' => tep_db_input($vendor_id),
												'products_id' => $product['products_id'],
												'products_model' => tep_db_input($product['products_model'])); 




						if(isset($VendorPrice) && tep_not_null($VendorPrice)){
							$sql_data_array = array_merge($sql_data_array, array('wholesale_price' => $VendorPrice));
						}
						///add by anuj
						if(!tep_not_null($VendorPrice)){
							$sql_data_array = array_merge($sql_data_array, array('wholesale_price' => $vend['wholesale_price']));
						}
						///
						///add by anuj
						if(!tep_not_null($InventoryLevels)){
							$sql_data_array = array_merge($sql_data_array, array('inventory_levels' => $vend['inventory_levels']));

						}


						if(isset($InventoryLevels) && tep_not_null($InventoryLevels)){
							$sql_data_array = array_merge($sql_data_array, array('inventory_levels' => (int)$InventoryLevels));

						}

						tep_db_perform(TABLE_VENDORS_TO_PRODUCTS, $sql_data_array, 'replace');

                          }						  	
					//} //for VENDORS_UPLOAD_SKIP_UPC == 'true' 
					elseif (VENDORS_UPLOAD_SKIP_UPC == 'false') { //If skip UPC is false, add model to v2p

					  
					  $sql_data_array = array('vendors_id' => tep_db_input($vendor_id),
												'products_id' => 'NULL',
												'products_model' => tep_db_input($UPCNumber));
					  
					  
						if(isset($VendorPrice) && tep_not_null($VendorPrice)){
							$sql_data_array = array_merge($sql_data_array, array('wholesale_price' => $VendorPrice));
						}
						////add by anuj
						/*if(!tep_not_null($VendorPrice)){
							$sql_data_array = array_merge($sql_data_array, array('wholesale_price' => $product['products_price']));
						}*/
						///////////
						if(isset($InventoryLevels) && tep_not_null($InventoryLevels)){
							$sql_data_array = array_merge($sql_data_array, array('inventory_levels' => (int)$InventoryLevels));
						}
						/////////add by anuj
						/*if(!tep_not_null($InventoryLevels)){
							$sql_data_array = array_merge($sql_data_array, array('inventory_levels' => (int)$InventoryLevels1));
						}*/
						///////////
						tep_db_perform(TABLE_VENDORS_TO_PRODUCTS, $sql_data_array, 'replace');

					} else //If Skip not exists UPC then continue
						continue;
					$count++;
				}
			}

                /////////if VENDORS_UPLOAD_SKIP_UPC=false

			//Delete excess values
			/*if(VENDORS_UPLOAD_SKIP_UPC == 'false')
			{
			tep_db_query("DELETE FROM vendors_to_products WHERE products_id NOT IN (SELECT products_id FROM products)");
			$affected = mysql_affected_rows();
			}*/
			////////////
			//Delete excess values
			if(VENDORS_UPLOAD_SKIP_UPC == 'true')
			{
			tep_db_query("DELETE FROM vendors_to_products WHERE products_id NOT IN (SELECT products_id FROM products)");
			$affected = mysql_affected_rows();
			}

			$messageStack->add(sprintf(TEXT_V2P_SUCCESS, $count), 'success');
			if ($affected > 0) $messageStack->add($affected.' excess rows were deleted', 'success');
    	break;

      case 'setflag':
        if ($_GET['vID']) {
          tep_db_query("UPDATE ".TABLE_VENDORS." SET vendors_status = ".(int)$_GET['flag']." WHERE vendors_id = ".(int)$_GET['vID']);
        }

        tep_redirect(tep_href_link(FILENAME_VENDORS, (isset($_GET['page']) ? 'page=' . $_GET['page'] . '&' : '') . 'vID=' . (int)$_GET['vID']));
        break;

      case 'insert':
      case 'save':
        if (isset($_GET['vID'])) $vendor_id = tep_db_prepare_input($_GET['vID']);
        $vendor_name = tep_db_prepare_input($_POST['vendors_name']);

        $sql_data_array = Array();
        foreach ($_POST as $key=>$value)
        {
        	if (strstr($key, 'vendors_'))
        		$sql_data_array[$key] = tep_db_prepare_input($value);
			}

        if ($action == 'insert') {
          tep_db_perform(TABLE_VENDORS, $sql_data_array, 'replace');
          $vendor_id = tep_db_insert_id();
        } elseif ($action == 'save') {
          tep_db_perform(TABLE_VENDORS, $sql_data_array, 'update', "vendors_id = '" . (int)$vendor_id . "'");
        }

        tep_redirect(tep_href_link(FILENAME_VENDORS, (isset($_GET['page']) ? 'page=' . $_GET['page'] . '&' : '') . 'vID=' . $vendor_id));
        break;
      case 'deleteconfirm':
        $vendor_id = tep_db_prepare_input($_GET['vID']);

        tep_db_query("DELETE v, v2p FROM ".TABLE_VENDORS." v LEFT JOIN ".TABLE_VENDORS_TO_PRODUCTS." v2p USING (vendors_id) where v.vendors_id = '" . (int)$vendor_id . "'");

        tep_redirect(tep_href_link(FILENAME_VENDORS, 'page=' . $_GET['page']));
        break;
    }
  }
?>
<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">
<html <?php echo HTML_PARAMS; ?>>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">
<title><?php echo TITLE; ?></title>
<link rel="stylesheet" type="text/css" href="includes/stylesheet.css">
<link rel="stylesheet" type="text/css" href="includes/javascript/thickbox.css">
<script language="javascript" src="includes/general.js"></script>
<script language="javascript" src="includes/javascript/jquery.pack.js"></script>
<script language="javascript" src="includes/javascript/thickbox.js"></script>
</head>
<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0" bgcolor="#FFFFFF">


<?
if ($_GET['v_action'] == 'details') {
ob_clean();
Header('Content-type: text/html; charset=UTF-8');

?><table cellpadding="0" cellspacing="0" border="0">
<tr><td><b>Inventory Statistics</b></td></tr>
<?
     //Get total products for this inventory sections

$instock_query = tep_db_query(
	"SELECT s.sections_id, s.sections_name,
			(SELECT COUNT(*) FROM ".TABLE_PRODUCTS." p JOIN ".TABLE_PRODUCTS_TYPES." pt USING (product_types_id) WHERE p.products_status = 1 AND pt.sections_id = s.sections_id AND p.products_id IN
				(SELECT products_id FROM ".TABLE_VENDORS_TO_PRODUCTS." v2p JOIN ".TABLE_VENDORS." v USING (vendors_id) WHERE v.vendors_status = 1 AND v2p.inventory_levels > 0 AND v.vendors_id = ".(int)$_GET['vID'].")) AS total
	FROM ".TABLE_SECTIONS." s
	ORDER BY sections_name");
	$preorder_query = tep_db_query(
	"SELECT s.sections_id, s.sections_name,
			(SELECT COUNT(*) FROM ".TABLE_PRODUCTS." p JOIN ".TABLE_PRODUCTS_TYPES." pt USING (product_types_id) WHERE p.products_status = 1 AND p.products_tba = 1 AND pt.sections_id = s.sections_id AND p.products_id IN
				(SELECT products_id FROM ".TABLE_VENDORS_TO_PRODUCTS." v2p JOIN ".TABLE_VENDORS." v USING (vendors_id) WHERE v.vendors_status = 1 AND v2p.inventory_levels = 0 AND v.vendors_id = ".(int)$_GET['vID'].")) AS total
	FROM ".TABLE_SECTIONS." s
	ORDER BY sections_name");
	$backorder_query = tep_db_query(
	"SELECT s.sections_id, s.sections_name,
			(SELECT COUNT(*) FROM ".TABLE_PRODUCTS." p JOIN ".TABLE_PRODUCTS_TYPES." pt USING (product_types_id) WHERE p.products_status = 1 AND p.products_tba != 1 AND pt.sections_id = s.sections_id AND p.products_id IN
				(SELECT products_id FROM ".TABLE_VENDORS_TO_PRODUCTS." v2p JOIN ".TABLE_VENDORS." v USING (vendors_id) WHERE v.vendors_status = 1 AND v2p.inventory_levels = 0 AND v.vendors_id = ".(int)$_GET['vID'].")) AS total
	FROM ".TABLE_SECTIONS." s
	ORDER BY sections_name");
while ($instock = tep_db_fetch_array($instock_query))
{
	//Get total products in section
	$total_query = tep_db_query("SELECT COUNT(*) AS total FROM ".TABLE_PRODUCTS." p JOIN ".TABLE_PRODUCTS_TYPES." pt USING (product_types_id)
		WHERE pt.sections_id = ".$instock['sections_id']);
	$total = tep_db_fetch_array($total_query);
	$preorder = tep_db_fetch_array($preorder_query);
	$backorder = tep_db_fetch_array($backorder_query);

					?><tr><td colspan="7" class="dataTableContent"><b><?=number_format($instock['total'], 0, '.', ',')?></b> - In-Stock; <b><?=number_format($backorder['total'], 0, '.', ',')?></b> - Backorder; <b><?=number_format($preorder['total'], 0, '.', ',')?></b> - Pre-Order in <b><?=$instock['sections_name']?></b> out of <b><?=number_format($total['total'], 0, '.', ',')?></b> total</td></tr><?
}



//Get total products for this vendor

$count_query = tep_db_query(
	"SELECT COUNT(products_id) AS count FROM ".TABLE_PRODUCTS." p WHERE p.products_status = 1 AND p.products_id IN
    		(SELECT products_id FROM ".TABLE_VENDORS_TO_PRODUCTS." v2p JOIN ".TABLE_VENDORS." v USING (vendors_id) WHERE v.vendors_status = 1 AND v2p.wholesale_price > 0 AND v.vendors_id = ".(int)$_GET['vID'].")");
  $count = tep_db_fetch_array($count_query);
  //Get total number of products
  $total_query = tep_db_query("SELECT COUNT(*) AS total FROM ".TABLE_PRODUCTS." p");
  $total = tep_db_fetch_array($total_query);

?><tr><td><b>Per Vendor Statistics</b></td></tr>
<tr><td class="dataTableContent"><b><?=number_format($count['count'], 0, '.', ',')?></b> out of <b><?=number_format($total['total'], 0, '.', ',')?></b> total <b>Products</b></td></tr><?

//Get total products for this vendor by sections
$count_query = tep_db_query(
	"SELECT s.sections_id, s.sections_name,
			(SELECT COUNT(*) FROM ".TABLE_PRODUCTS." p JOIN ".TABLE_PRODUCTS_TYPES." pt USING (product_types_id) WHERE p.products_status = 1 AND pt.sections_id = s.sections_id AND p.products_id IN
				(SELECT products_id FROM ".TABLE_VENDORS_TO_PRODUCTS." v2p JOIN ".TABLE_VENDORS." v USING (vendors_id) WHERE v.vendors_status = 1 AND v2p.wholesale_price > 0 AND v.vendors_id = ".(int)$_GET['vID'].")) AS count
	FROM ".TABLE_SECTIONS." s
	ORDER BY sections_name");
while ($count = tep_db_fetch_array($count_query))
{
	//Get total products in section
	$total_query = tep_db_query("SELECT COUNT(*) AS total FROM ".TABLE_PRODUCTS." p JOIN ".TABLE_PRODUCTS_TYPES." pt USING (product_types_id)
		WHERE pt.sections_id = ".$count['sections_id']);
	$total = tep_db_fetch_array($total_query);

	?><tr><td class="dataTableContent"><b><?=number_format($count['count'], 0, '.', ',')?></b> out of <b><?=number_format($total['total'], 0, '.', ',')?></b> total <b><?=$count['sections_name']?></b></td></tr><?
}
?></table><?
}
?>
<?php if($_GET['v_action'] != 'details') { ?>
<!-- header //-->
<?php require(DIR_WS_INCLUDES . 'header.php'); ?>
<!-- header_eof //-->

<!-- body //-->
<table border="0" width="100%" cellspacing="2" cellpadding="2">
  <tr>
    <td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="1" cellpadding="1" class="columnLeft">
<!-- left_navigation //-->
<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>
<!-- left_navigation_eof //-->
    </table></td>
<!-- body_text //-->
    <td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
      <tr>
        <td width="100%"><table border="0" width="100%" cellspacing="0" cellpadding="0">
          <tr>
            <td class="pageHeading"><?php echo HEADING_TITLE; ?></td>
            <td><a href="#TB_inline?width=300&height=120&inlineId=upload" class="thickbox" title="<?=TEXT_UPLOAD_INTRO?>"><?=HEADING_UPLOAD?></a></td>
            <td align="right"><a href="<?=tep_href_link(FILENAME_VENDORS, tep_get_all_get_params(Array('active', 'action')).'action=set&active=1')?>">Set vendors active</a></td>
            <td align="right"><a href="<?=tep_href_link(FILENAME_VENDORS, tep_get_all_get_params(Array('active', 'action')).'action=set&active=2')?>">Set non-vendors active</a></td>
            <td align="right"><a href="<?=tep_href_link(FILENAME_VENDORS, tep_get_all_get_params(Array('active', 'action')).'action=set&active=0')?>">Set non-vendors inactive</a></td>
            <td class="pageHeading" align="right" width="25%"><?php echo tep_draw_separator('pixel_trans.gif', HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>
          </tr>
        </table></td>
      </tr>
      <tr>
        <td><table border="0" width="100%" cellspacing="0" cellpadding="0">
          <tr>
            <td valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="2">
              <tr class="dataTableHeadingRow">
              	<td class="dataTableHeadingContent" width="5%"><?php echo TABLE_HEADING_VENDOR_ID; ?></td>
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_VENDOR; ?></td>
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_EMAIL; ?></td>
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_PHONE; ?></td>
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_FAX ?></td>
                <td class="dataTableHeadingContent"><?php echo TABLE_HEADING_STATUS; ?></td>
                <td class="dataTableHeadingContent" align="right"><?php echo TABLE_HEADING_ACTION; ?> </td>
              </tr>
<?php
//Get all vendors
$vendors_sql = "select *, date_format(date_added, '%m/%d/%Y') AS date_added from ".TABLE_VENDORS." ORDER BY vendors_name";
  $vendors_split = new splitPageResults($_GET['page'], MAX_DISPLAY_SEARCH_RESULTS, $vendors_sql, $vendors_query_numrows);
  $vendors_query = tep_db_query($vendors_sql);
  while ($vendor = tep_db_fetch_array($vendors_query)) {
    if ((!$_GET['vID'] || ($_GET['vID'] && $_GET['vID'] == $vendor['vendors_id'])) && !$vInfo && substr($action, 0, 3) != 'new') {
      $vInfo = new objectInfo($vendor);
    }

    if (is_object($vInfo) && ($vendor['vendors_id'] == $vInfo->vendors_id)) {
      echo '              <tr id="defaultSelected" class="dataTableRowSelected" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_VENDORS, 'page=' . $_GET['page'] . '&vID=' . $vendor['vendors_id'] . '&action=edit') . '\'">' . "\n";
    } else {
      echo '              <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_VENDORS, 'page=' . $_GET['page'] . '&vID=' . $vendor['vendors_id']) . '\'">' . "\n";
    }
?>
							<td class="dataTableContent"><?php echo $vendor['vendors_id']; ?></td>
                <td class="dataTableContent"><?php echo $vendor['vendors_name']; ?></td>
                <td class="dataTableContent"><?php echo $vendor['vendors_email']; ?></td>
                <td class="dataTableContent"><?php echo $vendor['vendors_phone']; ?></td>
                <td class="dataTableContent"><?php echo $vendor['vendors_fax']; ?></td>
                <td class="dataTableContent">
                <?
      if ($vendor['vendors_status'] == '1') {
        echo tep_image(DIR_WS_IMAGES . 'icon_status_green.gif', IMAGE_ICON_STATUS_GREEN, 10, 10) . '  <a href="' . tep_href_link(FILENAME_VENDORS, 'page=' . $_GET['page'] . '&vID=' . $vendor['vendors_id'] . '&action=setflag&flag=0') . '">' . tep_image(DIR_WS_IMAGES . 'icon_status_red_light.gif', IMAGE_ICON_STATUS_RED_LIGHT, 10, 10) . '</a>';
      } else {
        echo '<a href="' . tep_href_link(FILENAME_VENDORS, 'page=' . $_GET['page'] . '&vID=' . $vendor['vendors_id'] . '&action=setflag&flag=1') . '">' . tep_image(DIR_WS_IMAGES . 'icon_status_green_light.gif', IMAGE_ICON_STATUS_GREEN_LIGHT, 10, 10) . '</a>  ' . tep_image(DIR_WS_IMAGES . 'icon_status_red.gif', IMAGE_ICON_STATUS_RED, 10, 10);
      }
      ?>
                </td>
                <td class="dataTableContent" align="right"><?php if (isset($vInfo) && is_object($vInfo) && ($vendor['vendors_id'] == $vInfo->vendors_id)) { echo tep_image(DIR_WS_IMAGES . 'icon_arrow_right.gif'); } else { echo '<a href="' . tep_href_link(FILENAME_VENDORS, 'page=' . $_GET['page'] . '&vID=' . $vendor['vendors_id']) . '">' . tep_image(DIR_WS_IMAGES . 'icon_info.gif', IMAGE_ICON_INFO) . '</a>'; } ?> </td>
              </tr>
<?php
  }
?>
              <tr>
                <td colspan="7"><table border="0" width="100%" cellspacing="0" cellpadding="2">
                  <tr>
                    <td class="smallText" valign="top"><?php echo $vendors_split->display_count($vendors_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, $_GET['page'], TEXT_DISPLAY_NUMBER_OF_VENDORS); ?></td>
                    <td class="smallText" align="right"><?php echo $vendors_split->display_links($vendors_query_numrows, MAX_DISPLAY_SEARCH_RESULTS, MAX_DISPLAY_PAGE_LINKS, $_GET['page']); ?></td>
                  </tr>
                </table></td>
              </tr>
<?php
  if (empty($action)) {
?>
              <tr>
                <td align="right" colspan="7" class="smallText"><?php echo '<a href="' . tep_href_link(FILENAME_VENDORS, 'page=' . $_GET['page'] . '&vID=' . $vInfo->vendors_id . '&action=new') . '">' . tep_image_button('button_insert.gif', IMAGE_INSERT) . '</a>'; ?></td>
              </tr>
<?php
  }

  						?><tr><td class="dataTableContent" colspan="7" style="padding-bottom: 10px; padding-top: 10px;"><b>Total Inventory Statistics:</b></td></tr>
					<?php

					?>
					<!--<tr><td colspan="7" class="dataTableContent"><b><?=number_format($countinv['count'], 0, '.', ',')?></b> out of <b><?=number_format($total_inv['total'], 0, '.', ',')?></b> total <b>Products</b></td></tr>-->
					<?php
					//Get total products for this vendor by sections
$instock_query = tep_db_query(
	"SELECT s.sections_id, s.sections_name,
			(SELECT COUNT(*) FROM ".TABLE_PRODUCTS." p JOIN ".TABLE_PRODUCTS_TYPES." pt USING (product_types_id) WHERE p.products_status = 1 AND pt.sections_id = s.sections_id AND p.products_id IN
				(SELECT products_id FROM ".TABLE_VENDORS_TO_PRODUCTS." v2p JOIN ".TABLE_VENDORS." v USING (vendors_id) WHERE v.vendors_status = 1 AND v2p.inventory_levels > 0)) AS total
	FROM ".TABLE_SECTIONS." s
	ORDER BY sections_name");
	$preorder_query = tep_db_query(
	"SELECT s.sections_id, s.sections_name,
			(SELECT COUNT(*) FROM ".TABLE_PRODUCTS." p JOIN ".TABLE_PRODUCTS_TYPES." pt USING (product_types_id) WHERE p.products_status = 1 AND p.products_tba = 1 AND pt.sections_id = s.sections_id AND p.products_id IN
				(SELECT products_id FROM ".TABLE_VENDORS_TO_PRODUCTS." v2p JOIN ".TABLE_VENDORS." v USING (vendors_id) WHERE v.vendors_status = 1 AND v2p.inventory_levels = 0)) AS total
	FROM ".TABLE_SECTIONS." s
	ORDER BY sections_name");
	$backorder_query = tep_db_query(
	"SELECT s.sections_id, s.sections_name,
			(SELECT COUNT(*) FROM ".TABLE_PRODUCTS." p JOIN ".TABLE_PRODUCTS_TYPES." pt USING (product_types_id) WHERE p.products_status = 1 AND p.products_tba != 1 AND pt.sections_id = s.sections_id AND p.products_id IN
				(SELECT products_id FROM ".TABLE_VENDORS_TO_PRODUCTS." v2p JOIN ".TABLE_VENDORS." v USING (vendors_id) WHERE v.vendors_status = 1 AND v2p.inventory_levels = 0)) AS total
	FROM ".TABLE_SECTIONS." s
	ORDER BY sections_name");
while ($instock = tep_db_fetch_array($instock_query))
{
	//Get total products in section
	$total_query = tep_db_query("SELECT COUNT(*) AS total FROM ".TABLE_PRODUCTS." p JOIN ".TABLE_PRODUCTS_TYPES." pt USING (product_types_id)
		WHERE pt.sections_id = ".$instock['sections_id']);
	$total = tep_db_fetch_array($total_query);
	$preorder = tep_db_fetch_array($preorder_query);
	$backorder = tep_db_fetch_array($backorder_query);

					?><tr><td colspan="7" class="dataTableContent"><b><?=number_format($instock['total'], 0, '.', ',')?></b> - In-Stock; <b><?=number_format($backorder['total'], 0, '.', ',')?></b> - Backorder; <b><?=number_format($preorder['total'], 0, '.', ',')?></b> - Pre-Order in <b><?=$instock['sections_name']?></b> out of <b><?=number_format($total['total'], 0, '.', ',')?></b> total</td></tr><?
}

?>

					<tr><td class="dataTableContent" colspan="7" style="padding-bottom: 10px; padding-top: 10px;"><b>Total Vendor Statistics:</b></td></tr><?

//Get total products for vendors
$count_query = tep_db_query(
	"SELECT COUNT(products_id) AS count FROM ".TABLE_PRODUCTS." p WHERE p.products_status = 1 AND p.products_id IN
    		(SELECT products_id FROM ".TABLE_VENDORS_TO_PRODUCTS." v2p JOIN ".TABLE_VENDORS." v USING (vendors_id) WHERE v.vendors_status = 1 AND v2p.wholesale_price > 0)");
  $count = tep_db_fetch_array($count_query);
  //Get total number of products
  $total_query = tep_db_query("SELECT COUNT(*) AS total FROM ".TABLE_PRODUCTS." p");
  $total = tep_db_fetch_array($total_query);

					?><tr><td colspan="7" class="dataTableContent"><b><?=number_format($count['count'], 0, '.', ',')?></b> out of <b><?=number_format($total['total'], 0, '.', ',')?></b> total <b>Products</b></td></tr><?

//Get total products for this vendor by sections
$count_query = tep_db_query(
	"SELECT s.sections_id, s.sections_name,
			(SELECT COUNT(*) FROM ".TABLE_PRODUCTS." p JOIN ".TABLE_PRODUCTS_TYPES." pt USING (product_types_id) WHERE p.products_status = 1 AND pt.sections_id = s.sections_id AND p.products_id IN
				(SELECT products_id FROM ".TABLE_VENDORS_TO_PRODUCTS." v2p JOIN ".TABLE_VENDORS." v USING (vendors_id) WHERE v.vendors_status = 1 AND v2p.wholesale_price > 0)) AS total
	FROM ".TABLE_SECTIONS." s
	ORDER BY sections_name");
while ($count = tep_db_fetch_array($count_query))
{
	//Get total products in section
	$total_query = tep_db_query("SELECT COUNT(*) AS total FROM ".TABLE_PRODUCTS." p JOIN ".TABLE_PRODUCTS_TYPES." pt USING (product_types_id)
		WHERE pt.sections_id = ".$count['sections_id']);
	$total = tep_db_fetch_array($total_query);

					?><tr><td colspan="7" class="dataTableContent"><b><?=number_format($count['total'], 0, '.', ',')?></b> out of <b><?php echo $count['stock_status'];?><?=number_format($total['total'], 0, '.', ',')?></b> total <b><?=$count['sections_name']?></b></td></tr><?
}

					?><tr><td class="dataTableContent" colspan="7" style="padding-bottom: 10px; padding-top: 10px;"><b>Total Active/Inactive Product Statistics:</b></td></tr><?

//Get active and inactive products
$count_query = tep_db_query("SELECT COUNT(*) AS total FROM ".TABLE_PRODUCTS." WHERE products_status = 1 UNION ALL SELECT COUNT(*) AS total FROM ".TABLE_PRODUCTS." WHERE products_status = 0 UNION ALL SELECT COUNT(*) AS total FROM ".TABLE_PRODUCTS);
$active = tep_db_fetch_array($count_query);
$inactive = tep_db_fetch_array($count_query);
$total = tep_db_fetch_array($count_query)
?>
						<tr><td colspan="7" class="dataTableContent"><b><?=number_format($active['total'], 0, '.', ',')?></b> active and <b><?=number_format($inactive['total'], 0, '.', ',')?></b> inactive products<b><?=$count['sections_name']?></b> out of <b><?=number_format($total['total'], 0, '.', ',')?></b></td></tr><?
//By sections
$active_query = tep_db_query("SELECT s.sections_id, s.sections_name,
		(SELECT COUNT(*) FROM ".TABLE_PRODUCTS." p JOIN ".TABLE_PRODUCTS_TYPES." pt USING (product_types_id) WHERE pt.sections_id = s.sections_id AND p.products_status = 1) AS total
	FROM ".TABLE_SECTIONS." s
	ORDER BY sections_name");
$inactive_query = tep_db_query("SELECT s.sections_id, s.sections_name,
		(SELECT COUNT(*) FROM ".TABLE_PRODUCTS." p JOIN ".TABLE_PRODUCTS_TYPES." pt USING (product_types_id) WHERE pt.sections_id = s.sections_id AND p.products_status = 0) AS total
	FROM ".TABLE_SECTIONS." s
	ORDER BY sections_name");
while ($active = tep_db_fetch_array($active_query))
{
	//Get total products
	$total_query = tep_db_query("SELECT COUNT(*) AS total FROM ".TABLE_PRODUCTS." p JOIN ".TABLE_PRODUCTS_TYPES." pt USING (product_types_id) JOIN ".TABLE_SECTIONS." s ON (pt.sections_id = s.sections_id) WHERE s.sections_id = ".$active['sections_id']);
	$total = tep_db_fetch_array($total_query);
	$inactive = tep_db_fetch_array($inactive_query);
						?><tr><td colspan="7" class="dataTableContent"><b><?=number_format($active['total'], 0, '.', ',')?></b> active and <b><?=number_format($inactive['total'], 0, '.', ',')?></b> inactive products in <b><?=$active['sections_name']?></b> out of <b><?=number_format($total['total'], 0, '.', ',')?></b></td></tr><?
}
?>
            </table></td>
<?php
  $heading = array();
  $contents = array();

  switch ($action) {
    case 'new':
      $heading[] = array('text' => '<b>' . TEXT_HEADING_NEW_VENDOR . '</b>');

      $contents = array('form' => tep_draw_form('vendors', FILENAME_VENDORS, 'action=insert', 'post'));
      $contents[] = array('text' => TEXT_NEW_INTRO);
      $contents[] = array('text' => '<br>' . TEXT_VENDORS_NAME . '<br>' . tep_draw_input_field('vendors_name'));
      $contents[] = array('text' => '<br>' . TEXT_VENDORS_EMAIL . '<br>' . tep_draw_input_field('vendors_email'));
      $contents[] = array('text' => '<br>' . TEXT_VENDORS_PHONE . '<br>' . tep_draw_input_field('vendors_phone'));
      $contents[] = array('text' => '<br>' . TEXT_VENDORS_FAX . '<br>' . tep_draw_input_field('vendors_fax'));
      $contents[] = array('text' => '<br>' . TEXT_VENDORS_ADDRESS . '<br>' . tep_draw_textarea_field('vendors_address', 'soft', 40, 7));

      $contents[] = array('align' => 'center', 'text' => '<br>' . tep_image_submit('button_save.gif', IMAGE_SAVE) . ' <a href="' . tep_href_link(FILENAME_VENDORS, 'page=' . $_GET['page'] . '&vID=' . $_GET['vID']) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>');
      break;
    case 'edit':
      $heading[] = array('text' => '<b>' . TEXT_HEADING_EDIT_VENDOR . '</b>');

      $contents = array('form' => tep_draw_form('vendors', FILENAME_VENDORS, 'page=' . $_GET['page'] . '&vID=' . $vInfo->vendors_id . '&action=save', 'post'));
      $contents[] = array('text' => TEXT_EDIT_INTRO);

      $contents[] = array('text' => '<br>' . TEXT_VENDORS_NAME . '<br>' . tep_draw_input_field('vendors_name', $vInfo->vendors_name));
      $contents[] = array('text' => '<br>' . TEXT_VENDORS_EMAIL . '<br>' . tep_draw_input_field('vendors_email', $vInfo->vendors_email));
      $contents[] = array('text' => '<br>' . TEXT_VENDORS_PHONE . '<br>' . tep_draw_input_field('vendors_phone', $vInfo->vendors_phone));
      $contents[] = array('text' => '<br>' . TEXT_VENDORS_FAX . '<br>' . tep_draw_input_field('vendors_fax', $vInfo->vendors_fax));
      $contents[] = array('text' => '<br>' . TEXT_VENDORS_ADDRESS . '<br>' . tep_draw_textarea_field('vendors_address', 'soft', 40, 7, $vInfo->vendors_address));

      $contents[] = array('align' => 'center', 'text' => '<br>' . tep_image_submit('button_save.gif', IMAGE_SAVE) . ' <a href="' . tep_href_link(FILENAME_VENDORS, 'page=' . $_GET['page'] . '&vID=' . $vInfo->vendors_id) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>');
      break;
    case 'delete':
      $heading[] = array('text' => '<b>' . TEXT_HEADING_DELETE_VENDOR . '</b>');

      $contents = array('form' => tep_draw_form('manufacturers', FILENAME_VENDORS, 'page=' . $_GET['page'] . '&vID=' . $vInfo->vendors_id . '&action=deleteconfirm'));
      $contents[] = array('text' => TEXT_DELETE_INTRO);
      $contents[] = array('text' => '<br><b>' . $vInfo->vendors_name . '</b>');

      $contents[] = array('align' => 'center', 'text' => '<br>' . tep_image_submit('button_delete.gif', IMAGE_DELETE) . ' <a href="' . tep_href_link(FILENAME_VENDORS, 'page=' . $_GET['page'] . '&vID=' . $vInfo->vendors_id) . '">' . tep_image_button('button_cancel.gif', IMAGE_CANCEL) . '</a>');
      break;
    default:
      if (isset($vInfo) && is_object($vInfo)) {
        $heading[] = array('text' => '<b>' . $vInfo->vendors_name . '</b>');

        $contents[] = array('align' => 'center', 'text' =>
        	'<a href="'.tep_href_link(FILENAME_VENDORS, 'v_action=details&vID='.$vInfo->vendors_id).'&width=500&height=200" title="'.$vInfo->vendors_name.'" class="thickbox" style="padding-bottom: 5px;">'.tep_image_button('button_details.gif', IMAGE_DETAILS).'</a><br />'.
        	'<a href="' . tep_href_link(FILENAME_VENDORS, 'page=' . $_GET['page'] . '&vID=' . $vInfo->vendors_id . '&action=edit') . '">' . tep_image_button('button_edit.gif', IMAGE_EDIT) . '</a> '.
        	'<a href="' . tep_href_link(FILENAME_VENDORS, 'page=' . $_GET['page'] . '&vID=' . $vInfo->vendors_id . '&action=delete') . '">' . tep_image_button('button_delete.gif', IMAGE_DELETE) . '</a>');
        $contents[] = array('text' => '<br>' . TEXT_DATE_ADDED . ': ' . $vInfo->date_added);
        $contents[] = array('text' => '<br>' . TEXT_VENDORS_NAME.': '.$vInfo->vendors_name);
        $contents[] = array('text' => '<br>' . TEXT_VENDORS_EMAIL.': '.$vInfo->vendors_email);
        $contents[] = array('text' => '<br>' . TEXT_VENDORS_PHONE.': '.$vInfo->vendors_phone);
        $contents[] = array('text' => '<br>' . TEXT_VENDORS_FAX.': '.$vInfo->vendors_fax);
        $contents[] = array('text' => '<br>' . TEXT_VENDORS_ADDRESS.': '.$vInfo->vendors_address);
      }
      break;
  }

  if ( (tep_not_null($heading)) && (tep_not_null($contents)) ) {
    echo '            <td width="25%" valign="top">' . "\n";

    $box = new box;
    echo $box->infoBox($heading, $contents);

    echo '            </td>' . "\n";
  }
?>
          </tr>
        </table></td>
      </tr>
    </table></td>
<!-- body_text_eof //-->
  </tr>
</table>
<!-- body_eof //-->

<!-- footer //-->
<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>
<!-- footer_eof //-->
<br>
<?php require(DIR_WS_INCLUDES . 'application_bottom.php');
ob_end_flush();?>
<? } ?>
<div style="display: none;" id="upload">
<?
//Get local files from vendors directory
$dir = dir(DIR_FS_ADMIN.'vendors');
$files_list = Array(Array('id' => 0, 'text' => 'None'));
while ($file = $dir->read())
{
if ($file != '.' && $file != '..')
	$files_list[] = Array('id' => $file,  'text' => $file);
}
echo tep_draw_form('upload', FILENAME_VENDORS, 'action=upload', 'post', 'enctype="multipart/form-data"').
'Select file locally:<br />'.tep_draw_pull_down_menu('v2p_file_local', $files_list).'<br />Or upload one:<br />'.
tep_draw_file_field('v2p_file').'<br />'.tep_image_submit('button_upload.gif', IMAGE_UPLOAD)?>
</form>
</div>

</body>
</html>

Thank you,

olimits7

printf · January 30, 2009

the queries are not optimized, you can also eliminate many of them because they do the exact opposite of the previous query. So you can merge in some instances (3) queries into (1). Also the usage conditionals in result loops is very bad. If you want to limit 100 rows per flush then you should be selecting using LIMIT, not running the same if(condition) in your result loop thousands of times. There are to many other problems to go through each one. If it works the way it is then use it, but I would rewrite it because there is way to many repetitive things going on that don't need to go on! Whoever wrote this needs a lesson on managing resources!

olimits7 · January 30, 2009

Hi,

Thank you for your reply...

The script does work fine on small data feeds, but on large one's it crashes the server. I'm sure this has to do with the reasons you listed above...

Can you also take a quick look at this other PHP file and let me know if you think this code/queries is optimized best it can be??

I have a suspicion that this code isn't written to the best of it's ability too...because on large files it crashes the website. :-(

<?php
  } elseif($action == 'process') {
     set_time_limit("7200");
     ?>
      <tr><td><?php
//get detail of affiliates
$affiliate_query = tep_db_query("select * from " . TABLE_AFFILIATES . " where affiliates_id = " . $HTTP_GET_VARS['aID'] . "");
$aff = tep_db_fetch_array($affiliate_query);
$dir = 'affiliate_data/';
/////////
////////////////get section name 
   $sections_query = tep_db_query("select * from sections where sections_id =".$aff['section_id']."");
   $sections = tep_db_fetch_array($sections_query);
   if($aff['section_id']=='0')
   $sectionname='All';
   else
   $sectionname=$sections['sections_name'];
   ////////////////
    ////////////////get currency name name 
   $cur_query = tep_db_query("select * from currencies where currencies_id =".$aff['currency_id']."");
   $cur = tep_db_fetch_array($cur_query);
   ////////////////
/////////

$sit_query = tep_db_query("select * from " . TABLE_AFFILIATES_FTP_INFO . " where ftp_id = '" . $aff['affiliate_company'] . "'");

$sit = tep_db_fetch_array($sit_query);
/////
$filename = $dir.$sit['filename']."_".$sectionname."_".$cur['code'].$sit['filetype'];
if($sit['ftp_id']=='3')
{
$fp = fopen($filename,'w');
fwrite($fp, "UPC\tProductName\tBrand\tCategory\tProductURL\tImageURL\tOfferPrice\tAvailabilty\tDeliveryCost\n");

}
elseif($sit['ftp_id']=='2')
{
$fp = fopen($filename,'w');
}
elseif($sit['ftp_id']=='1')
{
$fp = fopen($filename,'w');
fwrite($fp, "product_type\ttitle\tdescription\tid\tlink\timage_link\tprice\tupc\tcondition\n");

}
else
{
$fp = fopen($filename,'w');
fwrite($fp, "UPC\tProductName\tBrand\tCategory\tProductURL\tImageURL\tOfferPrice\tAvailabilty\tDeliveryCost\n");
}
///
if($aff['section_id']!='0')
{
$query="select p.*, pd.*, pid.* from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TYPE . " pid where p.products_status = '1' and p.products_id = pd.products_id and p.product_types_id = pid.product_types_id and p.products_model REGEXP '^[0-9]+' and pid.sections_id=".$aff['section_id']."";
}
else
{
$query="select p.*, pd.*, pid.* from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TYPE . " pid where p.products_status = '1' and p.products_id = pd.products_id and p.product_types_id = pid.product_types_id and p.products_model REGEXP '^[0-9]+'";
}
////////////////get currency name
$cur_query = tep_db_query("select * from currencies where currencies_id =".$aff['currency_id']."");
$cur = tep_db_fetch_array($cur_query);
//echo $cur['code'];
////////////////
$result2=mysql_query($query);
$k=0;
while($row=mysql_fetch_array($result2)){

flush();
//add for price

$row['products_price'] = $price_value = tep_get_products_price($row['products_id']);
if ($new_price = tep_get_products_special_price($row['products_id'])) {
  $price_value = $new_price;
      $products_price = $currencies1->display_price1($new_price, tep_get_tax_rate($row['products_tax_class_id']), 1, $cur['code']);
    }
else {
      $products_price = $currencies1->display_price1($row['products_price'], tep_get_tax_rate($row['products_tax_class_id']), 1, $cur['code']);
    }
////
$manufacturer_query = tep_db_query("select manufacturers_name from " . TABLE_MANUFACTURERS . " where manufacturers_id = '" . $row['manufacturers_id'] . "'");
$manufacturer = tep_db_fetch_array($manufacturer_query);

$string = $manufacturer['manufacturers_name'];
$pattern = '/©(\d+)(\w+)/';
$replacement = '';
$man=preg_replace($pattern, $replacement, $string);



////////////////
$imgurl = HTTPP_SERVER.DIR_WS_HTTP_CATALOG."images/".$row['products_image'];
$prourl = tep_hrefp_link(FILENAME_PRODUCT_INFO, 'products_id='.$row['products_id']);
//check availability
$vendors_query = tep_db_query("SELECT inventory_levels FROM ".TABLE_VENDORS_TO_PRODUCTS." WHERE products_model = '".$row['products_model']."' AND products_id = '".$row['products_id']."' AND wholesale_price > 0 ORDER BY wholesale_price DESC LIMIT 1");
$vend = tep_db_fetch_array($vendors_query);
if($vend['inventory_levels']>0)
$avb='In-Stock';
elseif($vend['inventory_levels']==0 && $row['products_tba'] == '1')
$avb='Pre-Order';
elseif($inventory_levels == 0 && date('Y-m-d', strtotime('now')) < date('Y-m-d', strtotime($row['products_date_added'])))
$avb='Pre-Order';
else
$avb=$sit['availability'];
///

///30january create df for froogle
if($sit['ftp_id']=='1')
{
   $output = '';
   switch($row['sections_id']){
			case '1':
				$output .= 'ABC';
				break;
			case '2':
				$output .= 'DEF';
				break;
			case '3':
				$output .= 'GHI';
				break;
			case '4':
				$output .= 'JKL';
				break;
		}
		$output .= "\t" . $row['products_name'] . "\t" . $row['products_description'] . "\t" . $row['products_id'] . "\t" . $prourl . "\t" . $imgurl . "\t" . $products_price . "\t" . $row['products_model'] . "\t" . $sit['p_condition'] . "\n";
		fwrite($fp, $output);
		$k++;
}
/////// end of froole df created
//create df for price grabber
elseif($sit['ftp_id']=='2')
{

            $output=$row['products_name'] . "\t" . $row['products_model'] . "\t" . $man . "\t" . $row['products_model'] . "\t" . tep_get_category($row['sections_id']) . "\t" . $row['product_types_name'] . "\t" . $products_price . "\t" . $avb . "\t" . $prourl . "\t" . $row['shipping_rate'] . "\t" . $sit['p_condition'] . "\n";
		fwrite($fp, $output);
		$k++;
}
//end of price grabber df created
//create df for Affiliate future
elseif($sit['ftp_id']=='3')
{
        
        $output=$row['products_model'] . "\t" . $row['products_name'] . "\t" . $row['product_types_name'] . "\t" . tep_get_category($row['sections_id']) . "\t" . $prourl . "\t" . $imgurl . "\t" . $products_price . "\t" . $avb . "\t" . $sit['deliverycost'] . "\n";
		fwrite($fp, $output);
		$k++;
}
///end of Affiliate future created
else
{
       $output=$row['products_model'] . "\t" . $row['products_name'] . "\t" . $row['product_types_name'] . "\t" . tep_get_category($row['sections_id']) . "\t" . $prourl . "\t" . $imgurl . "\t" . $products_price . "\t" . $avb . "\t" . $sit['deliverycost'] . "\n";
		fwrite($fp, $output);
		$k++;
}
}//end of while


//manage date modified in affiliates table
mysql_query("update " . TABLE_AFFILIATES . " SET `date_modified` = now() where affiliates_id=" .$HTTP_GET_VARS['aID']. ";");
//
//echo "The tab-delimited *.txt file has been created successfully .";
if($k==mysql_num_rows($result2)){//finally create d DF
echo "Successfully wrote <a href=\"$filename\">$filename</a>\n";
echo '<tr><td><a href="' . tep_href_link(FILENAME_AFFILIATE_MANAGER_NEW, 'page=' . $HTTP_GET_VARS['page'] . '&aID=' . $HTTP_GET_VARS['aID']) . '">' . tep_image_button('button_back.gif', IMAGE_ICON_BACK, 10, 10) . '</a></td></tr>';
}
else
    {
   echo 'File Couldnot Be Created!!';

    }
    fclose($fp);
?>

Thank you,

olimits7

olimits7 · February 1, 2009

Hi,

Can somebody please take a look at the code I posted right above this message and let me know how well it's written??

Thank you,

olimits7

corbin · February 1, 2009

The first four queries could be combined into 1 with a couple JOINs.

If I had to guess, I would say:

if($aff['section_id']!='0')
{
$query="select p.*, pd.*, pid.* from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TYPE . " pid where p.products_status = '1' and p.products_id = pd.products_id and p.product_types_id = pid.product_types_id and p.products_model REGEXP '^[0-9]+' and pid.sections_id=".$aff['section_id']."";
}
else
{
$query="select p.*, pd.*, pid.* from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TYPE . " pid where p.products_status = '1' and p.products_id = pd.products_id and p.product_types_id = pid.product_types_id and p.products_model REGEXP '^[0-9]+'";
}

is horribly slow, depending on your tables' layouts.

You could always try timing different parts of the script. For example:

$START = microtime(true);
//code
$END = microtime(true);

echo "Part1 took " . ($END - $START) . "seconds.";

$START = microtime(true);
//code 2
$END = microtime(true);

echo "Part2 took " . ($END - $START) . "seconds.";

Sign In

Data Feed TXT Script Takes A Long Time To Be Created - Coding or Server??

Recommended Posts

olimits7

Link to comment

Share on other sites

printf

Link to comment

Share on other sites

premiso

Link to comment

Share on other sites

olimits7

Link to comment

Share on other sites

printf

Link to comment

Share on other sites

olimits7

Link to comment

Share on other sites

olimits7

Link to comment

Share on other sites

corbin

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information