Seperate Pricing Per Customer v3.5

TracyS · December 18, 2006

Hi JanZ,

Thanks again for the new instructions - but they unfortunately bring me right back to the 1054 error:

1054 - Unknown column 'pd.products_id' in 'where clause'

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id) where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%cohosh%' or p.products_model like '%cohosh%' or m.manufacturers_name like '%cohosh%') )

I'm guessing that means the contrib I mentioned in my last post is needed in my case since after installing that contrib my searches work without MySQL errors.

Now I just have to figure out why it's listing the same product 15 times! :huh:

Ok - I'm back to the Product Listing page having an error - and this is also the page that lists the price incorrectly - it seems as though it isn't checking for which customer group and just automatically giving the retail price. SOooo - at least I have my searches working and know what page I need to play around with now :D :blink:

If this happens to sound familiar - the search results are displaying in a table that has 5 columns - and the heading of each column is Product Name - so it is simply repeating itself 5 columns wide :huh:

Jan Zonjee · December 18, 2006

Thanks again for the new instructions - but they unfortunately bring me right back to the 1054 error:
1054 - Unknown column 'pd.products_id' in 'where clause'

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id) where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%cohosh%' or p.products_model like '%cohosh%' or m.manufacturers_name like '%cohosh%') )

I'm guessing that means the contrib I mentioned in my last post is needed in my case since after installing that contrib my searches work without MySQL errors.

No, you are missing part(s) of the regular osC code:

	  if ($status_tmp_product_prices_table == true) {
 $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . $product_prices_table . " as tmp_pp using(products_id)";
  } elseif ($status_tmp_special_prices_table == true) {
 $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id ";
  } else {
$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) ";
  }
 // EOF Separate Pricing Per Customer

 if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {
if (!tep_session_is_registered('customer_country_id')) {
  $customer_country_id = STORE_COUNTRY;
  $customer_zone_id = STORE_ZONE;
}
$from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id left join " . TABLE_ZONES_TO_GEO_ZONES . " gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "')";
 }

 $from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

 $where_str = " where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id ";

In particular you are missing the:

  $from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

This is another 1054 error code: missing pd.products_id because " . TABLE_PRODUCTS_DESCRIPTION . " pd, is missing.

You need to add those changes to the latest osC updated version of that page !

TracyS · December 18, 2006

No, you are missing part(s) of the regular osC code:
In particular you are missing the:
  $from_str .= ", " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";
This is another 1054 error code: missing pd.products_id because " . TABLE_PRODUCTS_DESCRIPTION . " pd, is missing.

You need to add those changes to the latest osC updated version of that page !

YAY!! You are correct (as usual!) :D Ok - half the battle solved! That saved me quite a bit of work with other errors that the other contrib. fix seemed to be causing :)

Would you happen to know - does the product_listing.php page govern the look of the content on the search results, the product table on a products page and the listing of subcategories on a categories page?

I'm having some issues, and having even more fun figuring out where / how to correct them :blush:

If you look at: http://mmherbs.dreamhosters.com

My 3 current issues with (I think) the products_listing.php page

1) If you go to a category (such as Women's Health) the subcategories in FireFox and Netscape are getting listed twice. After viewing the source code and only finding them in there once I've determined that it's the ALT tag of the image field that is causing the text for the subcategory to show up twice. I don't have images for the subcategories (and I'm not sure if we will) so I'd like to know how to comment out the section that calls those particular images.

2) If you then go to a products page, you will see in the table of products available for purchase there is a column called "Options" - I don't need that column so I have to find out how to turn that off. I would also like to change the field where you select the quantity of each item you would like to purchase and set it so that it starts at 0. That way the customer only has to change the number for the product they want (I just have a feeling people are going to end up ordering more than they want, not realizing they have to change the other product(s) to 0 )

3) If you do a search for "cohosh" for example, the resulting page has a table with columns of "Model", "Price", "Quantity" and "Buy Now" - I don't need any of those columns as the products listed from a search are the Master Products and aren't purchasable in and of themselves. (If the purchasable products also showed up in a search it would be confusing to most people as they would have a minimum of 3 links - one for the master and one for a 2oz size and another for the 4oz size).

I had tried to make these edits on my own and ended up with the table for the products being fine, but the table for the search was listing 5 columns of "Product Name" :huh:

The last issue I am having with (I think) the product_listing.php page is - if I'm signed in as a retail customer, or if I'm just a Guest - I get correct pricing information in the table on a products page. However, if I sign in as a Wholesale customer I am still getting the retail price on that table. It is the correct price in the shopping cart - but there must be something I've missed on the product_listing.php page.

Since I'm also using Master Products, and I'm not very well versed in PHP/MySQL yet, this has definitely been a big learning curve for me and I truly do appreciate all of the wonderful assistance I have found on these forums! THANK YOU! :D

If it helps - here is my current product_listing.php code

<?php
/*
 $Id: product_listing.php,v 1.44 2003/06/09 22:49:59 hpdl Exp $
 adapted for Separate Pricing Per Customer v4 2005/02/26

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Master Products  - JOHNSON - 05/07/2003 [email protected]

 Copyright (c) 2003 Suomedia - Dynamic Content Management  

 Released under the GNU General Public License
*/

 $listing_split = new splitPageResults($listing_sql, MAX_DISPLAY_SEARCH_RESULTS, 'p.products_id');

 if ( ($listing_split->number_of_rows > 0) && ( (PREV_NEXT_BAR_LOCATION == '1') || (PREV_NEXT_BAR_LOCATION == '3') ) ) {
?>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
 <tr>
<td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
<td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
<?php
 }

 $list_box_contents = array();

 for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
switch ($column_list[$col]) {
case 'PRODUCT_LIST_MODEL':
	$lc_text = TABLE_HEADING_MODEL;
	$lc_align = '';
	break;
  case 'PRODUCT_LIST_NAME':
	$lc_text = TABLE_HEADING_PRODUCTS;
	$lc_align = '';
	break;
  case 'PRODUCT_LIST_MANUFACTURER':
	$lc_text = TABLE_HEADING_MANUFACTURER;
	$lc_align = '';
	break;
  case 'PRODUCT_LIST_PRICE':
	$lc_text = TABLE_HEADING_PRICE;
	$lc_align = 'right';
	break;
  case 'PRODUCT_LIST_QUANTITY':
	$lc_text = TABLE_HEADING_QUANTITY;
	$lc_align = 'right';
	break;
  case 'PRODUCT_LIST_WEIGHT':
	$lc_text = TABLE_HEADING_WEIGHT;
	$lc_align = 'right';
	break;
  case 'PRODUCT_LIST_IMAGE':
	$lc_text = TABLE_HEADING_IMAGE;
	$lc_align = 'center';
	break;
  case 'PRODUCT_LIST_BUY_NOW':
	$lc_text = TABLE_HEADING_BUY_NOW;
	$lc_align = 'center';
	break;

}

if ( ($column_list[$col] != 'PRODUCT_LIST_BUY_NOW') && ($column_list[$col] != 'PRODUCT_LIST_IMAGE') ) {
  $lc_text = tep_create_sort_heading($HTTP_GET_VARS['sort'], $col+1, $lc_text);
}

$list_box_contents[0][] = array('align' => $lc_align,
								'params' => 'class="productListing-heading"',
								'text' => ' ' . $lc_text . ' ');
 }

 if ($listing_split->number_of_rows > 0) {

$rows = 0;
$listing_query = tep_db_query($listing_split->sql_query);
// BOF Separate Pricing per Customer
$no_of_listings = tep_db_num_rows($listing_query);
// global variable (session) $sppc_customer_group_id -> local variable customer_group_id

 if(!tep_session_is_registered('sppc_customer_group_id')) {
 $customer_group_id = '0';
 } else {
  $customer_group_id = $sppc_customer_group_id;
 }


while ($_listing = tep_db_fetch_array($listing_query)) {
  $listing[] = $_listing;
$list_of_prdct_ids[] = $_listing['products_id'];
} 
// next part is a debug feature, when uncommented it will print the info that this module receives
/*
  echo '<pre>';
  print_r($listing);
  echo '</pre>';
*/  
 $select_list_of_prdct_ids = "products_id = '".$list_of_prdct_ids[0]."' ";
 if ($no_of_listings > 1) {
  for ($n = 1; $n < count($list_of_prdct_ids); $n++) {
  $select_list_of_prdct_ids .= "or products_id = '".$list_of_prdct_ids[$n]."' "; 
  }
}

// get all product prices for products with the particular customer_group_id
// however not necessary for customer_group_id = 0
if ($customer_group_id != '0') {
 $pg_query = tep_db_query("select pg.products_id, customers_group_price as price from " . TABLE_PRODUCTS_GROUPS . " pg where (".$select_list_of_prdct_ids.") and pg.customers_group_id = '".$customer_group_id."' ");
//   $no_of_pg_products = tep_db_num_rows($pg_query);
while ($pg_array = tep_db_fetch_array($pg_query)) {
$new_prices[] = array ('products_id' => $pg_array['products_id'], 'products_price' => $pg_array['price'], 'specials_new_products_price' => '', 'final_price' => $pg_array['price']);
}
  for ($x = 0; $x < $no_of_listings; $x++) {
// replace products prices with those from customers_group table
  if(!empty($new_prices)) {
	 for ($i = 0; $i < count($new_prices); $i++) {
	if( $listing[$x]['products_id'] == $new_prices[$i]['products_id'] ) {
	$listing[$x]['products_price'] = $new_prices[$i]['products_price'];
	$listing[$x]['final_price'] = $new_prices[$i]['final_price'];
	}
	}
} // end if(!empty($new_prices)
$listing[$x]['specials_new_products_price'] = ''; // makes sure that a retail specials price doesn't carry over to another customer group
$listing[$x]['final_price'] = $listing[$x]['products_price']; // final price should not be the retail special price
  } // end for ($x = 0; $x < $no_of_listings; $x++)
} // end if ($customer_group_id != '0')

// an extra query is needed for all the specials

$specials_query = tep_db_query("select products_id, specials_new_products_price from " . TABLE_SPECIALS . " where (".$select_list_of_prdct_ids.") and status = '1' and customers_group_id = '" .$customer_group_id. "'");
while ($specials_array = tep_db_fetch_array($specials_query)) {
$new_s_prices[] = array ('products_id' => $specials_array['products_id'], 'products_price' => '', 'specials_new_products_price' => $specials_array['specials_new_products_price'] , 'final_price' => $specials_array['specials_new_products_price']);
}

// add the correct specials_new_products_price and replace final_price
for ($x = 0; $x < $no_of_listings; $x++) {

	if(!empty($new_s_prices)) {
	for ($i = 0; $i < count($new_s_prices); $i++) {
	 if( $listing[$x]['products_id'] == $new_s_prices[$i]['products_id'] ) {
	   $listing[$x]['specials_new_products_price'] = $new_s_prices[$i]['specials_new_products_price'];
	   $listing[$x]['final_price'] = $new_s_prices[$i]['final_price'];
	 }
	   }
   } // end if(!empty($new_s_prices)
} // end for ($x = 0; $x < $no_of_listings; $x++)

//	while ($listing = tep_db_fetch_array($listing_query)) { (was original code)
for ($x = 0; $x < $no_of_listings; $x++) {

  $rows++;

  if (($rows/2) == floor($rows/2)) {
	$list_box_contents[] = array('params' => 'class="productListing-even"');
  } else {
	$list_box_contents[] = array('params' => 'class="productListing-odd"');
  }

  $cur_row = sizeof($list_box_contents) - 1;

  for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
	$lc_align = '';

	switch ($column_list[$col]) {
	  case 'PRODUCT_LIST_MODEL':
		$lc_align = '';
		$lc_text = ' ' . $listing[$x]['products_model'] . ' ';
		break;
	  case 'PRODUCT_LIST_NAME':
		$lc_align = '';
		if (isset($HTTP_GET_VARS['manufacturers_id'])) {
		  $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing[$x]['products_id']) . '">' . $listing[$x]['products_name'] . '</a>';
		} else {
		  $lc_text = ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing[$x]['products_id']) . '">' . $listing[$x]['products_name'] . '</a> ';
		}
		break;
	  case 'PRODUCT_LIST_MANUFACTURER':
		$lc_align = '';
		$lc_text = ' <a href="' . tep_href_link(FILENAME_DEFAULT, 'manufacturers_id=' . $listing[$x]['manufacturers_id']) . '">' . $listing[$x]['manufacturers_name'] . '</a> ';
		break;
	  case 'PRODUCT_LIST_PRICE':
		$lc_align = 'right';

		if (tep_not_null($listing[$x]['specials_new_products_price'])) {
		  $lc_text = ' <s>' .  $currencies->display_price($listing[$x]['products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . '</s>  <span class="productSpecialPrice">' . $currencies->display_price($listing[$x]['specials_new_products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . '</span> ';
		} else {
		  $lc_text = ' ' . $currencies->display_price($listing[$x]['products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . ' ';
		}
		break;
	  case 'PRODUCT_LIST_QUANTITY':
		$lc_align = 'right';
		$lc_text = ' ' . $listing[$x]['products_quantity'] . ' ';
		break;
	  case 'PRODUCT_LIST_WEIGHT':
		$lc_align = 'right';
		$lc_text = ' ' . $listing[$x]['products_weight'] . ' ';
		break;
	  case 'PRODUCT_LIST_IMAGE':
		$lc_align = 'center';
		if (isset($HTTP_GET_VARS['manufacturers_id'])) {
		  $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing[$x]['products_image'], $listing[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a>';
		} else {
		  $lc_text = ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing[$x]['products_image'], $listing[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a> ';
		}
		break; // EOF Separate Pricing per Customer
	  case 'PRODUCT_LIST_BUY_NOW':
		$lc_align = 'center';
//Master Products			
		if ($listing[$x]['products_master_status'] != '1' && $listing[$x]['products_master'] == '0') {
				$lc_text = '<a href="' . tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action')) . 'action=buy_now&products_id=' . $listing[$x]['products_id']) . '">' . tep_image_button('button_buy_now.gif', IMAGE_BUTTON_BUY_NOW) . '</a> ';
			} elseif ($listing[$x]['products_master'] != '0') {
	   		 $lc_text = ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing[$x]['products_master']) . '">' . tep_image_button('button_buy_now.gif', IMAGE_BUTTON_BUY_NOW) . '</a> ';
			} else {
				$lc_text = ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing[$x]['products_id']) . '">' . tep_image_button('button_buy_now.gif', IMAGE_BUTTON_BUY_NOW) . '</a> ';
			}
//Master Products EOF			
		break;
	}

	$list_box_contents[$cur_row][] = array('align' => $lc_align,
										   'params' => 'class="productListing-data"',
										   'text'  => $lc_text);
  }
}

new productListingBox($list_box_contents);
 } else {
$list_box_contents = array();

$list_box_contents[0] = array('params' => 'class="productListing-odd"');
$list_box_contents[0][] = array('params' => 'class="productListing-data"',
							   'text' => TEXT_NO_PRODUCTS);

new productListingBox($list_box_contents);
 }

 if ( ($listing_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '2') || (PREV_NEXT_BAR_LOCATION == '3')) ) {
?>
<table border="0" width="100%" cellspacing="0" cellpadding="2">
 <tr>
<td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
<td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
<?php
 }
?>

Jan Zonjee · December 18, 2006

1) If you go to a category (such as Women's Health) the subcategories in FireFox and Netscape are getting listed twice. After viewing the source code and only finding them in there once I've determined that it's the ALT tag of the image field that is causing the text for the subcategory to show up twice. I don't have images for the subcategories (and I'm not sure if we will) so I'd like to know how to comment out the section that calls those particular images.

I can't see anything like that with Safari and Firefox 1.0.5.8 on a Mac.

2) If you then go to a products page, you will see in the table of products available for purchase there is a column called "Options" - I don't need that column so I have to find out how to turn that off. I would also like to change the field where you select the quantity of each item you would like to purchase and set it so that it starts at 0. That way the customer only has to change the number for the product they want (I just have a feeling people are going to end up ordering more than they want, not realizing they have to change the other product(s) to 0 )

That is a master_listing.php if you ask me and I think you can change the columns on that in admin->configuration->slave products.

3) If you do a search for "cohosh" for example, the resulting page has a table with columns of "Model", "Price", "Quantity" and "Buy Now" - I don't need any of those columns as the products listed from a search are the Master Products and aren't purchasable in and of themselves. (If the purchasable products also showed up in a search it would be confusing to most people as they would have a minimum of 3 links - one for the master and one for a 2oz size and another for the 4oz size).

Since advanced_search_results.php uses product_listing.php for displaying the results (as you correctly assumed) you are stuck here, unless you save that page under another name, "require" the new page instead of product_listing.php and are able to change that page to what you need....

The last issue I am having with (I think) the product_listing.php page is - if I'm signed in as a retail customer, or if I'm just a Guest - I get correct pricing information in the table on a products page. However, if I sign in as a Wholesale customer I am still getting the retail price on that table. It is the correct price in the shopping cart - but there must be something I've missed on the product_listing.php page.

I think it is caused by not adding global $sppc_customer_group_id; but that could have been my fault. You could also use:

		if (isset($_SESSION['sppc_customer_group_id']) && $_SESSION['sppc_customer_group_id'] != '0') {
	  $customer_group_id = (int)$_SESSION['sppc_customer_group_id'];
	} else {
	  $customer_group_id = 0;
	}

TracyS · December 18, 2006

I can't see anything like that with Safari and Firefox 1.0.5.8 on a Mac.

That is a master_listing.php if you ask me and I think you can change the columns on that in admin->configuration->slave products.

Since advanced_search_results.php uses product_listing.php for displaying the results (as you correctly assumed) you are stuck here, unless you save that page under another name, "require" the new page instead of product_listing.php and are able to change that page to what you need....

I think it is caused by not adding global $sppc_customer_group_id; but that could have been my fault. You could also use:
		if (isset($_SESSION['sppc_customer_group_id']) && $_SESSION['sppc_customer_group_id'] != '0') {
	  $customer_group_id = (int)$_SESSION['sppc_customer_group_id'];
	} else {
	  $customer_group_id = 0;
	}

Hi JanZ,

Even though you can't see it - is it the product_listing.php that I would remove the image information from in order to stop it from happening in FireFox and Netscape Navigator on Windows? (It didn't happen in FireFox until I installed Netscape - so I'm pretty sure it's a Netscape issue - but since it's now owned by AOL I'm guessing we'll have a few customers using it so I'm trying to make things compatible >_< ).

You were right on the Admin for the Slave Products config. :D I didn't realize that option was in there :blush: Now I have to post on that board to try to figure out why my options in that area are all doubled :blink: :blush:

I will try creating a different page to include for the search results and we'll see what happens :)

I will try changing the code you listed above to see if that solves the price problem :)

Thanks again! :D

TracyS · December 18, 2006

Hi JanZ,

I will try changing the code you listed above to see if that solves the price problem :)

Thanks again! :D

Ok - I changed to this

if (isset($_SESSION['sppc_customer_group_id']) && $_SESSION['sppc_customer_group_id'] != '0') {
	  $customer_group_id = (int)$_SESSION['sppc_customer_group_id'];
	} else {
	  $customer_group_id = 0;
	}

and it didn't make a bit of difference :( I still get retail prices when signed in as a wholesale customer. (except in the shopping cart).

I did find, however; that I was also getting the duplicate text for the shopping cart - and by uploading a transparent 1x1 gif I resolved that issue - so I will upload the transparent gif for all of the subcategories and slave products that I don't need to show an image for at the moment :) What's even more weird is I turned off the show slave product image in the admin - but it was still trying to show it in the shopping cart - oh well - at least I have a workaround for that one now :D

What did you mean by:

I think it is caused by not adding global $sppc_customer_group_id;

If you look at the code I put up earlier, where in the code should that be? Is it there? I am in no way going to say that you may be at fault for it not being somewhere it should be as I have done so much modifiying to this store that I don't even remember what all pages I've modified anymore and I have to go back and look at the original contrib's I've installed to see what they modified for a starting point half the time :blush:

I did run a comparison though with WinMerge - and if it's missing somewhere I'm just not spotting it. Any idea why the code change above didn't do anything?

TracyS · December 19, 2006

Ok - I'm down to just having the problem of the prices in the product_listing table not being correct for wholesale customers. I'm beginning to wonder if it isn't being generated by a Master Products file rather than the actual product_listing.php file - so I will be checking into that soon :)

I was able to use the allprods.php from the All Products contrib. to get my search results looking better - now I just have to find out if there's a way to change it to more of a traditional search where it will search through the product descriptions for the keywords instead of just the products name.

Using the transparent gif seems to have fixed the issue of "seeing doubles" in NN and FF on the subcategories and products in the shopping cart. :D

Thanks again for all of your help - if I find a master products page that is the culprit of my incorrect pricing issues I will definitely post here and let everyone know :thumbsup:

TracyS · December 20, 2006

Ok - I'm down to just having the problem of the prices in the product_listing table not being correct for wholesale customers. I'm beginning to wonder if it isn't being generated by a Master Products file rather than the actual product_listing.php file - so I will be checking into that soon :)

I was able to use the allprods.php from the All Products contrib. to get my search results looking better - now I just have to find out if there's a way to change it to more of a traditional search where it will search through the product descriptions for the keywords instead of just the products name.

Using the transparent gif seems to have fixed the issue of "seeing doubles" in NN and FF on the subcategories and products in the shopping cart. :D

Thanks again for all of your help - if I find a master products page that is the culprit of my incorrect pricing issues I will definitely post here and let everyone know :thumbsup:

Hi JanZ - I think I've found the culprit (possibly) ;)

In includes/modules there are some new files from Master Products that aren't in stock osC and therefor SPPC wouldn't have had updated pages for them. One of these pages is master_listing.php

I tried to add the SPPC code from product_listing.php into master_listing.php but that didn't work out so well. What I did find is that it caused my product_listing table to have no model, description or name - but to say "out of stock" and have a $0.00 dollar amount. Which tells me that this master_listing.php file does affect the information that goes into the product listing of available slave products :D It also tells me I didn't tweak the code properly for this page :( I'm also not sure if this is in fact where my pricing error is coming from - but I guess it's as good a starting point as any.

master_listing.php is an include file that is called from master_products.php - neither of these files have any changes to them for SPPC (and I'm pretty positive that one or both definitely need it).

Here is the code for the two pages as it is (without SPPC code) if anybody here feels they may know how to tackle this one. I will also be attempting to hack it myself (but with my limited PHP knowledge this could be interesting :blush: ) and if I find the trick to getting it all working before someone else here on the forum does I will definitely post it for future Master Products and SPPC users :thumbsup:

master_products.php (which calls master_listing.php)

<?php
/*
$ID master_products.php - JOHNSON - 05/07/2003 [email protected]

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Master Products  - JOHNSON - 05/07/2003 [email protected]

 Copyright (c) 2003 Suomedia - Dynamic Content Management

 Released under the GNU General Public License
*/

 require(DIR_WS_LANGUAGES . $language . '/' . FILENAME_MASTER_PRODUCTS);

$master_query = tep_db_query("select products_master from " . TABLE_PRODUCTS . " where products_master LIKE '%" . $HTTP_GET_VARS['products_id'] . "%'");
$thisquery = tep_db_fetch_array($master_query);
if ($thisquery['products_master'] != '0') {

	  $slave_list = array('MASTER_LIST_MODEL' => MASTER_LIST_MODEL,
							  'MASTER_LIST_NAME' => MASTER_LIST_NAME,
							  'MASTER_LIST_MANUFACTURER' => MASTER_LIST_MANUFACTURER,
							  'MASTER_LIST_PRICE' => MASTER_LIST_PRICE,
							  'MASTER_LIST_QUANTITY' => MASTER_LIST_QUANTITY,
							  'MASTER_LIST_WEIGHT' => MASTER_LIST_WEIGHT,
							  'MASTER_LIST_IMAGE' => MASTER_LIST_IMAGE,
							  'MASTER_LIST_BUY_NOW' => MASTER_LIST_BUY_NOW,
													'MASTER_LIST_OPTIONS' => MASTER_LIST_OPTIONS,
							  'MASTER_LIST_DESCRIPTION' => MASTER_LIST_DESCRIPTION);

asort($slave_list);

$column_list = array();
reset($slave_list);
while (list($key, $value) = each($slave_list)) {
  if ($value > 0) $column_list[] = $key;
}

$select_column_list = '';

for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
  switch ($column_list[$i]) {
	case 'MASTER_LIST_MODEL':
	  $select_column_list .= 'p.products_model,';
	  break;
	case 'MASTER_LIST_NAME':
	  $select_column_list .= 'pd.products_name, ';
	  break;
	case 'MASTER_LIST_DESCRIPTION':
  $select_column_list .= 'pd.products_description, ';
	  break;		  
	case 'MASTER_LIST_MANUFACTURER':
	  $select_column_list .= 'm.manufacturers_name, ';
	  break;
	case 'MASTER_LIST_QUANTITY':
	  $select_column_list .= 'p.products_quantity, ';
	  break;
	case 'MASTER_LIST_IMAGE':
	  $select_column_list .= 'p.products_image, ';
	  break;
	case 'MASTER_LIST_WEIGHT':
	  $select_column_list .= 'p.products_weight, ';
	  break;
  }
}

			$master_sql = "select  " . $select_column_list . " p.products_id,  p.manufacturers_id, p.products_tax_class_id, s.specials_new_products_price, s.status, p.products_price from ". TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_id = pd.products_id and p.products_master LIKE '%" . $HTTP_GET_VARS['products_id'] . "%' and p.products_status = '1' and pd.language_id = '" . (int)$languages_id . "'";


}

if ( (!isset($HTTP_GET_VARS['sort'])) || (!ereg('[1-8][ad]', $HTTP_GET_VARS['sort'])) || (substr($HTTP_GET_VARS['sort'], 0, 1) > sizeof($column_list)) ) {
  for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
	if ($column_list[$i] == 'MASTER_LIST_NAME') {
	  $HTTP_GET_VARS['sort'] = $i+1 . 'a';
	  $master_sql .= " order by pd.products_name";
	  break;
	}
  }
} else {
  $sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1);
  $sort_order = substr($HTTP_GET_VARS['sort'], 1);
  $master_sql .= ' order by ';
  switch ($column_list[$sort_col-1]) {
	case 'MASTER_LIST_MODEL':
	  $master_sql .= "p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
	case 'MASTER_LIST_NAME':
	  $master_sql .= "pd.products_name " . ($sort_order == 'd' ? 'desc' : '');
	  break;
	case 'MASTER_LIST_MANUFACTURER':
	  $master_sql .= "m.manufacturers_name " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
	case 'MASTER_LIST_QUANTITY':
	  $master_sql .= "p.products_quantity " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
	case 'MASTER_LIST_IMAGE':
	  $master_sql .= "pd.products_name";
	  break;
	case 'MASTER_LIST_WEIGHT':
	  $master_sql .= "p.products_weight " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
	case 'MASTER_LIST_PRICE':
	  $master_sql .= "p.products_price " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
  }
}

 include(DIR_WS_MODULES . FILENAME_MASTER_LISTING); 

?>

master_listing.php

<?php
/*
 $Id: master_listing.php

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Master Products MS2  - JOHNSON - 05/07/2003 [email protected]

 Copyright (c) 2003 Suomedia - Dynamic Content Management

 Released under the GNU General Public License

*/

 $listing_split = new splitPageResults($master_sql, MAX_DISPLAY_SEARCH_RESULTS, 'products_master');



 if ( ($listing_split->number_of_rows > 0) && ( (MASTER_PREV_NEXT_BAR_LOCATION == '1') || (MASTER_PREV_NEXT_BAR_LOCATION == '3') ) ) {


?>


<table border="0" width="100%" cellspacing="0" cellpadding="2">
 <tr>
<td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
<td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
<?php
 }



 $list_box_contents = array();

 for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
switch ($column_list[$col]) {
  case 'MASTER_LIST_MODEL':
	$lc_text = TABLE_HEADING_MODEL;
	$lc_align = '';
	break;
  case 'MASTER_LIST_NAME':
	$lc_text = TABLE_HEADING_PRODUCTS;
	$lc_align = '';
	break;
  case 'MASTER_LIST_DESCRIPTION':
	$lc_text = TABLE_HEADING_DESCRIPTION;
	$lc_align = 'center';
	break;
  case 'MASTER_LIST_ATTRIBUTES':
	$lc_text = TABLE_HEADING_ATTRIBUTES;
	$lc_align = 'center';
	break;				
  case 'MASTER_LIST_MANUFACTURER':
	$lc_text = TABLE_HEADING_MANUFACTURER;
	$lc_align = '';
	break;
  case 'MASTER_LIST_PRICE':
	$lc_text = TABLE_HEADING_PRICE;
	$lc_align = 'right';
	break;
  case 'MASTER_LIST_QUANTITY':
	$lc_text = TABLE_HEADING_QUANTITY;
	$lc_align = 'right';
	break;
  case 'MASTER_LIST_WEIGHT':
	$lc_text = TABLE_HEADING_WEIGHT;
	$lc_align = 'right';
	break;
  case 'MASTER_LIST_IMAGE':
	$lc_text = TABLE_HEADING_IMAGE;
	$lc_align = 'center';
	break;
  case 'MASTER_LIST_BUY_NOW':
	$lc_text = TABLE_HEADING_BUY_NOW;
	$lc_align = 'center';
	break;
	case 'MASTER_LIST_OPTIONS':
	  $lc_text = TABLE_HEADING_OPTIONS;
	  $lc_align = 'align="center"';
	  break;
}

if ( ($column_list[$col] != 'MASTER_LIST_BUY_NOW') && ($column_list[$col] != 'MASTER_LIST_IMAGE') && ($column_list[$col] != 'MASTER_LIST_MULTIPLE') && ($column_list[$col] != 'MASTER_LIST_DESCRIPTION') && ($column_list[$col] != 'MASTER_LIST_OPTIONS') ) {
  $lc_text = tep_create_sort_heading($HTTP_GET_VARS['sort'], $col+1, $lc_text);
}

$list_box_contents[0][] = array('align' => $lc_align,
								'params' => 'class="productListing-heading"',
								'text' => ' ' . $lc_text . ' ');
 }

 if ($listing_split->number_of_rows > 0) {
$rows = 0;
$master_query = tep_db_query($listing_split->sql_query);
while ($listing = tep_db_fetch_array($master_query)) {
  $rows++;

  if (($rows/2) == floor($rows/2)) {
	$list_box_contents[] = array('params' => 'class="productListing-even"');
  } else {
	$list_box_contents[] = array('params' => 'class="productListing-odd"');
  }

  $cur_row = sizeof($list_box_contents) - 1;

  for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
	$lc_align = '';
	$lc_params = '';

	switch ($column_list[$col]) {
	  case 'MASTER_LIST_MODEL':
		$lc_align = '';
		$lc_text = ' ' . $listing['products_model'] . ' ';
		break;
	  case 'MASTER_LIST_NAME':
		$lc_align = '';
		if (isset($HTTP_GET_VARS['manufacturers_id'])) {
		  $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing['products_id']) . '">' . $listing['products_name'] . '</a>';
		} else {
		  $lc_text = ' ' . $listing['products_name'] . '</a> ';
		}
		break;
	  case 'MASTER_LIST_DESCRIPTION':
		$lc_align = '';
		$lc_text = ' ' . osc_trunc_string(strip_tags($listing['products_description'], '<a><b><em><font><i><s><span><strong><sub><sup><u>'), MASTER_LIST_DESCRIPTION_LENGTH) . ' ';

		break;			
	  case 'MASTER_LIST_MANUFACTURER':
		$lc_align = '';
		$lc_text = ' <a href="' . tep_href_link(FILENAME_DEFAULT, 'manufacturers_id=' . $listing['manufacturers_id']) . '">' . $listing['manufacturers_name'] . '</a> ';
		break;
	  case 'MASTER_LIST_PRICE':
		$lc_align = 'right';
		if (tep_not_null($listing['specials_new_products_price'])) {
		  $lc_text = ' <s>' .  $currencies->display_price($listing['products_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . '</s>  <span class="productSpecialPrice">' . $currencies->display_price($listing['specials_new_products_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . '</span> ';
		} else {
		  $lc_text = ' ' . $currencies->display_price($listing['products_price'], tep_get_tax_rate($listing['products_tax_class_id'])) . ' ';
		}
		break; 
	  case 'MASTER_LIST_QUANTITY':
		$lc_align = 'center';
		$lc_text = ' ' . $listing['products_quantity'] . ' ';
		break;
	  case 'MASTER_LIST_WEIGHT':
		$lc_align = 'right';
		$lc_text = ' ' . $listing['products_weight'] . ' ';
		break;
	  case 'MASTER_LIST_IMAGE':
		$lc_align = 'center';
		if (isset($HTTP_GET_VARS['manufacturers_id'])) {
		  $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing['products_image'], $listing['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a>';
		} else {
		  $lc_text = ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing['products_image'], $listing['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a> ';
		}
		break;

	  case 'MASTER_LIST_BUY_NOW': 

		$lc_align = 'center'; 
		$lc_valign = 'top';
		if ((STOCK_CHECK == 'true')&&(tep_get_products_stock($listing['products_id']) < 1)) { 
		$lc_text = TEXT_STOCK;
	  } elseif ((STOCK_CHECK == 'false')&&(tep_get_products_stock($listing['products_id']) < 1)) {
		$qty_array = array();
		for ($i=0; $ns = 20, $i <= $ns; $i++) {
				$qty_array[] = array('id' => $i, 'text' => $i);

							$lc_text = tep_draw_input_field('Qty_ProdId_' . $listing['products_id'], '1', 'size="4"');

		}
	  } else {
		$quantity = tep_get_products_stock($listing['products_id']);  
		$qty_array = array();
		for ($i=0; $ns = (($quantity < 20) ? $quantity : 20), $i <= $ns; $i++) {
				$qty_array[] = array('id' => $i, 'text' => $i);

							$lc_text = tep_draw_input_field('Qty_ProdId_' . $listing['products_id'], '1', 'size="4"');

		} 
   		 }
	   break;
		   case 'MASTER_LIST_OPTIONS':

			  $lc_align = 'align="center"';
	 					$lc_text = '';
			  ///////////////////////////////////////////////////////////////////////////
			  // BOF: attribute options
			  $opt_count=0;
						$products_options_name = tep_db_query("select distinct popt.products_options_id, popt.products_options_name from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_ATTRIBUTES . " patrib where patrib.products_id='" . $listing['products_id'] . "' and patrib.options_id = popt.products_options_id and popt.language_id = '" . $languages_id . "' ORDER by products_options_id");
			  while ($products_options_name_values = tep_db_fetch_array($products_options_name)) {
				$opt_count++;
				$products_options_array = array();
				$lc_text .= '<b>' . $products_options_name_values['products_options_name'] . '</b><br />' . "\n";
				   $products_options = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$listing['products_id'] . "' and pa.options_id = '" . (int)$products_options_name_values['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'");

				 while ($products_options_values = tep_db_fetch_array($products_options)) {
				   $products_options_array[] = array('id' => $products_options_values['products_options_values_id'], 'text' => $products_options_values['products_options_values_name'], 'style' => '');
				   if ($products_options_values['options_values_price'] != '0') {
					 $products_options_array[sizeof($products_options_array)-1]['text'] .= ' (' . $products_options_values['price_prefix'] . $currencies->display_price($products_options_values['options_values_price'], tep_get_tax_rate($product_info_values['products_tax_class_id'])) .') ';
				   }
				 }
				$lc_text .= tep_draw_pull_down_menu('id_'.$listing['products_id'].'[' . $products_options_name_values['products_options_id'] . ']', $products_options_array);
				$lc_text .= '<br />';
			  }
			  if($opt_count==0) {
				$lc_text = MASTER_TEXT_NONE;
			  }
			  // EOF: attribute options
			  ///////////////////////////////////////////////////////////////////////////

			 break;
	}
	$list_box_contents[$cur_row][] = array('align' => $lc_align,
										   'valign' => $lc_valign,
										   'params' => 'class="productListing-data"',
										   'text'  => $lc_text);
  }
}

new productListingBox($list_box_contents);
 } else {
$list_box_contents = array();

$list_box_contents[0] = array('params' => 'class="productListing-odd"');
$list_box_contents[0][] = array('params' => 'class="productListing-data"',
							   'text' => TEXT_NO_PRODUCTS);

new productListingBox($list_box_contents);
 }
?>
 <table border="0" width="100%" cellspacing="0" cellpadding="2">
  <tr>
	<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
  </tr>
 </table>
<?php
 if ( ($listing_split->number_of_rows > 0) && ((MASTER_PREV_NEXT_BAR_LOCATION == '2') || (MASTER_PREV_NEXT_BAR_LOCATION == '3')) ) {
?>	 
 <table border="0" width="100%" cellspacing="0" cellpadding="2">		  
 <tr>
	<td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
	<td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
  <tr>
	<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
  </tr>

<?php
 }
?>

TracyS · December 20, 2006

master_listing.php is an include file that is called from master_products.php - neither of these files have any changes to them for SPPC (and I'm pretty positive that one or both definitely need it).

Here is the code for the two pages as it is (without SPPC code) if anybody here feels they may know how to tackle this one. I will also be attempting to hack it myself (but with my limited PHP knowledge this could be interesting :blush: ) and if I find the trick to getting it all working before someone else here on the forum does I will definitely post it for future Master Products and SPPC users :thumbsup:

>_< I dunno if I'm any closer or not - LOL

I tried to add in the SPPC changes from product_listing.php to master_products.php - and I'm back to at least getting my product page viewable again in a browser - but it didn't (as of yet) adjust the prices in the table of slave products available for purchase and it is giving me two errors:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/.lew/mmherbs/mountainmeadowherbs.com/includes/functions/database.php on line 104

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/.lew/mmherbs/mountainmeadowherbs.com/includes/functions/database.php on line 100

Now there is a bit of code at the very end of the SPPC code in product_listing.php that I do not know how to (or even if I should) incorporate into master_products.php

Here is what I have so far for master_products.php - I have kept the code comments and I commented the section out that I don't know what to do with (as well as saying so in a comment) - any thoughts?

<?php
/*
$ID master_products.php - JOHNSON - 05/07/2003 [email protected]

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Master Products  - JOHNSON - 05/07/2003 [email protected]

 Copyright (c) 2003 Suomedia - Dynamic Content Management

 Released under the GNU General Public License
*/

 require(DIR_WS_LANGUAGES . $language . '/' . FILENAME_MASTER_PRODUCTS);

$master_query = tep_db_query("select products_master from " . TABLE_PRODUCTS . " where products_master LIKE '%" . $HTTP_GET_VARS['products_id'] . "%'");
$thisquery = tep_db_fetch_array($master_query);
if ($thisquery['products_master'] != '0') {

	  $slave_list = array('MASTER_LIST_MODEL' => MASTER_LIST_MODEL,
							  'MASTER_LIST_NAME' => MASTER_LIST_NAME,
							  'MASTER_LIST_MANUFACTURER' => MASTER_LIST_MANUFACTURER,
							  'MASTER_LIST_PRICE' => MASTER_LIST_PRICE,
							  'MASTER_LIST_QUANTITY' => MASTER_LIST_QUANTITY,
							  'MASTER_LIST_WEIGHT' => MASTER_LIST_WEIGHT,
							  'MASTER_LIST_IMAGE' => MASTER_LIST_IMAGE,
							  'MASTER_LIST_BUY_NOW' => MASTER_LIST_BUY_NOW,
													'MASTER_LIST_OPTIONS' => MASTER_LIST_OPTIONS,
							  'MASTER_LIST_DESCRIPTION' => MASTER_LIST_DESCRIPTION);

asort($slave_list);

$column_list = array();
reset($slave_list);
while (list($key, $value) = each($slave_list)) {
  if ($value > 0) $column_list[] = $key;
}

$select_column_list = '';

for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
  switch ($column_list[$i]) {
	case 'MASTER_LIST_MODEL':
	  $select_column_list .= 'p.products_model,';
	  break;
	case 'MASTER_LIST_NAME':
	  $select_column_list .= 'pd.products_name, ';
	  break;
	case 'MASTER_LIST_DESCRIPTION':
  $select_column_list .= 'pd.products_description, ';
	  break;		  
	case 'MASTER_LIST_MANUFACTURER':
	  $select_column_list .= 'm.manufacturers_name, ';
	  break;
	case 'MASTER_LIST_QUANTITY':
	  $select_column_list .= 'p.products_quantity, ';
	  break;
	case 'MASTER_LIST_IMAGE':
	  $select_column_list .= 'p.products_image, ';
	  break;
	case 'MASTER_LIST_WEIGHT':
	  $select_column_list .= 'p.products_weight, ';
	  break;
  }
}

			$master_sql = "select  " . $select_column_list . " p.products_id,  p.manufacturers_id, p.products_tax_class_id, s.specials_new_products_price, s.status, p.products_price from ". TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_id = pd.products_id and p.products_master LIKE '%" . $HTTP_GET_VARS['products_id'] . "%' and p.products_status = '1' and pd.language_id = '" . (int)$languages_id . "'";


}
//BOF Separate Pricing per Customer 
$no_of_listings = tep_db_num_rows($master_sql);
//global variable (session) $sppc_customer_group_id -> local variable customer_group_id
if ( !isset($_SESSION['sppc_customer_group_id']) ){
   $customer_group_id = '0';
   } else {
   $customer_group_id = $_SESSION['sppc_customer_group_id'];
   }

   while ($_listing = tep_db_fetch_array($master_sql)) {
	 $listing[] = $_listing;
	$list_of_prdct_ids[] = $_listing['products_id'];
}
// next part is a debug feature, when uncommented it will print the info that this module receives
/*
  echo '<pre>';
  print_r($listing);
  echo '</pre>';
*/
$select_list_of_prdct_ids = "products_id = '".$list_of_prdct_ids[0]."' ";
 if ($no_of_listings > 1) {
for ($n = 1; $n < count($list_of_prdct_ids); $n++) {
$select_list_of_prdct_ids .= "or products_id = '".$list_of_prdct_ids[$n]."' ";
}
}

 // get all product prices for products with the particular customer_group_id
 // however not necessary for customer_group_id = 0
if ($customer_group_id != '0') {
   $pg_query = tep_db_query("select pg.products_id, customers_group_price as price from " . TABLE_PRODUCTS_GROUPS . " pg where (".$select_list_of_prdct_ids.") and pg.customers_group_id = '".$customer_group_id."' ");
  // $no_of_pg_products = tep_db_num_rows($pg_query);
	 while ($pg_array = tep_db_fetch_array($pg_query)) {
	 $new_prices[] = array ('products_id' => $pg_array['products_id'], 'products_price' => $pg_array['price'], 'specials_new_products_price' => '', 'final_price' => $pg_array['price']);
	 }
for ($x = 0; $x < $no_of_listings; $x++) {
// replace products prices with those from customers group table
  if(!empty($new_prices)) {
 for ($i = 0; $i < count($new_prices); $i++) {
if( $listing[$x]['products_id'] == $new_prices[$i]['products_id'] ) {
$listing[$x]['products_price'] = $new_prices[$i]['products_price'];
$listing[$x]['final_price'] = $new_prices[$i]['final_price'];
}
}
} // end if(!empty($new_prices)
  $listing[$x]['specials_new_products_price'] = ''; //make sure that a retail specials price doesn't carry over to another customer group
  $listing[$x]['final_price'] = $listing[$x]['products_price']; // final price should not be the retail special price
  } // end for ($x = 0; $x < $no_of_listings; $x++)
 } // end if ($customer_group_id != '0')

// an extra query is needed for all the specials
  $specials_query = tep_db_query("select products_id, specials_new_products_price from " . TABLE_SPECIALS . " where (".$select_list_of_prdct_ids.") and status = '1' and customers_group_id = '" .$customer_group_id. "'");
  while ($specials_array = tep_db_fetch_array($specials_query)) {
  $new_s_prices[] = array ('products_id' => $specials_array['products_id'], 'products_price' => '', 'specials_new_products_price' => $specials_array['specials_new_products_price'] , 'final_price' => $specials_array['specials_new_products_price']);
  }

// add the correct specials_as_new_products_price and replace final_price
for ($x = 0; $x < $no_of_listings; $x++) {

if(!empty($new_s_prices)) {
for ($i = 0; $i < count($new_s_prices); $i++) {
  if( $listing[$x]['products_id'] == $new_s_prices[$i]['products_id'] ) {
	$listing[$x]['specials_new_products_price'] = $new_s_prices[$i]['specials_new_products_price'];
	$listing[$x]['final_price'] = $new_s_prices[$i]['final_price'];
}
  }
} // end if(!empty($new_s_prices)
 }// end for ($x = 0; $x < $no_of_listings; $x++)

// how to incorporate the following
 /* for ($x = 0; $x < $no_of_listings; $x++) {

  $rows++;

  if (($rows/2) == floor($rows/2)) {
	$list_box_contents[] = array('params' => 'class="productListing-even"');
  } else {
	$list_box_contents[] = array('params' => 'class="productListing-odd"');
  }

  $cur_row = sizeof($list_box_contents) - 1;

  for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
	$lc_align = '';

	switch ($column_list[$col]) {
	  case 'PRODUCT_LIST_IMAGE':
		$lc_align = 'center';
		if (isset($HTTP_GET_VARS['manufacturers_id'])) {
		  $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing[$x]['products_image'], $listing[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a>';
		} else {
		  $lc_text = ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing[$x]['products_image'], $listing[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a> ';
		}
		break;
	  case 'PRODUCT_LIST_NAME':
		$lc_align = '';
		if (isset($HTTP_GET_VARS['manufacturers_id'])) {
		  $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing[$x]['products_id']) . '">' . $listing[$x]['products_name'] . '</a>';
		} else {
		  $lc_text = ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing[$x]['products_id']) . '">' . $listing[$x]['products_name'] . '</a> ';
		}
		break;
 */
//EOF Separate Price per Customer

if ( (!isset($HTTP_GET_VARS['sort'])) || (!ereg('[1-8][ad]', $HTTP_GET_VARS['sort'])) || (substr($HTTP_GET_VARS['sort'], 0, 1) > sizeof($column_list)) ) {
  for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
	if ($column_list[$i] == 'MASTER_LIST_NAME') {
	  $HTTP_GET_VARS['sort'] = $i+1 . 'a';
	  $master_sql .= " order by pd.products_name";
	  break;
	}
  }
} else {
  $sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1);
  $sort_order = substr($HTTP_GET_VARS['sort'], 1);
  $master_sql .= ' order by ';
  switch ($column_list[$sort_col-1]) {
	case 'MASTER_LIST_MODEL':
	  $master_sql .= "p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
	case 'MASTER_LIST_NAME':
	  $master_sql .= "pd.products_name " . ($sort_order == 'd' ? 'desc' : '');
	  break;
	case 'MASTER_LIST_MANUFACTURER':
	  $master_sql .= "m.manufacturers_name " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
	case 'MASTER_LIST_QUANTITY':
	  $master_sql .= "p.products_quantity " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
	case 'MASTER_LIST_IMAGE':
	  $master_sql .= "pd.products_name";
	  break;
	case 'MASTER_LIST_WEIGHT':
	  $master_sql .= "p.products_weight " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
	case 'MASTER_LIST_PRICE':
	  $master_sql .= "p.products_price " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
	  break;
  }
}

 include(DIR_WS_MODULES . FILENAME_MASTER_LISTING); 

?>

Jan Zonjee · December 20, 2006

I tried to add the SPPC code from product_listing.php into master_listing.php but that didn't work out so well.

I got the files from someone, but I can't find back at this moment who it was. It looks like there were no changes to master_products.php but I would suggest to change the $master_sql to:

	  $master_sql = "select  " . $select_column_list . " p.products_id,  p.manufacturers_id, p.products_tax_class_id, NULL as specials_new_products_price, p.products_price from ". TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id where p.products_id = pd.products_id and p.products_master = '" . $thisquery['products_master'] . "' and p.products_status = '1' and pd.language_id = '" . (int)$languages_id . "'";

The master listing for SPPC is:

<?php
/*
 $Id: master_listing.php

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Master Products MS2  - JOHNSON - 05/07/2003 [email protected]

 Copyright (c) 2003 Suomedia - Dynamic Content Management

 Released under the GNU General Public License

*/

 $listing_split = new splitPageResults($master_sql, MAX_DISPLAY_SEARCH_RESULTS, 'products_master');



 if ( ($listing_split->number_of_rows > 0) && ( (MASTER_PREV_NEXT_BAR_LOCATION == '1') || (MASTER_PREV_NEXT_BAR_LOCATION == '3') ) ) {


?>


<table border="0" width="100%" cellspacing="0" cellpadding="2">
 <tr>
<td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
<td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
<?php
 }



 $list_box_contents = array();

 for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
switch ($column_list[$col]) {
  case 'MASTER_LIST_MODEL':
	$lc_text = TABLE_HEADING_MODEL;
	$lc_align = '';
	break;
  case 'MASTER_LIST_NAME':
	$lc_text = TABLE_HEADING_PRODUCTS;
	$lc_align = '';
	break;
  case 'MASTER_LIST_DESCRIPTION':
	$lc_text = TABLE_HEADING_DESCRIPTION;
	$lc_align = 'center';
	break;
  case 'MASTER_LIST_ATTRIBUTES':
	$lc_text = TABLE_HEADING_ATTRIBUTES;
	$lc_align = 'center';
	break;				
  case 'MASTER_LIST_MANUFACTURER':
	$lc_text = TABLE_HEADING_MANUFACTURER;
	$lc_align = '';
	break;
  case 'MASTER_LIST_PRICE':
	$lc_text = TABLE_HEADING_PRICE;
	$lc_align = 'right';
	break;
  case 'MASTER_LIST_QUANTITY':
	$lc_text = TABLE_HEADING_QUANTITY;
	$lc_align = 'right';
	break;
  case 'MASTER_LIST_WEIGHT':
	$lc_text = TABLE_HEADING_WEIGHT;
	$lc_align = 'right';
	break;
  case 'MASTER_LIST_IMAGE':
	$lc_text = TABLE_HEADING_IMAGE;
	$lc_align = 'center';
	break;
  case 'MASTER_LIST_BUY_NOW':
	$lc_text = TABLE_HEADING_BUY_NOW;
	$lc_align = 'center';
	break;
  case 'MASTER_LIST_OPTIONS':
	$lc_text = TABLE_HEADING_OPTIONS;
	$lc_align = 'align="center"';
	break;
}

if (($column_list[$col] != 'MASTER_LIST_BUY_NOW') && ($column_list[$col] != 'MASTER_LIST_IMAGE') && ($column_list[$col] != 'MASTER_LIST_MULTIPLE') && ($column_list[$col] != 'MASTER_LIST_DESCRIPTION') && ($column_list[$col] != 'MASTER_LIST_OPTIONS')) {
  $lc_text = tep_create_sort_heading($HTTP_GET_VARS['sort'], $col+1, $lc_text);
}

$list_box_contents[0][] = array('align' => $lc_align,
								'params' => 'class="productListing-heading"',
								'text' => ' ' . $lc_text . ' ');
 }

 if ($listing_split->number_of_rows > 0) {
$rows = 0;
$master_query = tep_db_query($listing_split->sql_query);
// BOF Separate Pricing per Customer
 $no_of_listings = tep_db_num_rows($master_query);
global $sppc_customer_group_id;
// global variable (session) $sppc_customer_group_id -> local variable customer_group_id

 if(!tep_session_is_registered('sppc_customer_group_id')) {
$customer_group_id = '0';
  } else {
$customer_group_id = $sppc_customer_group_id;
 }

 while ($_listing = tep_db_fetch_array($master_query)) {
$listing[] = $_listing;
$list_of_prdct_ids[] = $_listing['products_id'];
 }
// next part is a debug feature, when uncommented it will print the info that this module receives
/*
 echo '<pre>';
 print_r($listing);
 echo '</pre>';
*/  
 $select_list_of_prdct_ids = "products_id = '".$list_of_prdct_ids[0]."' ";
 if ($no_of_listings > 1) {
for ($n = 1; $n < count($list_of_prdct_ids); $n++) {
$select_list_of_prdct_ids .= "or products_id = '".$list_of_prdct_ids[$n]."' ";
 }
}

// get all product prices for products with the particular customer_group_id
// however not necessary for customer_group_id = 0
 if ($customer_group_id != '0') {
$pg_query = tep_db_query("select pg.products_id, customers_group_price as price from " . TABLE_PRODUCTS_GROUPS . " pg where (".$select_list_of_prdct_ids.") and pg.customers_group_id = '".$customer_group_id."' ");
//   $no_of_pg_products = tep_db_num_rows($pg_query);
while ($pg_array = tep_db_fetch_array($pg_query)) {
  $new_prices[] = array ('products_id' => $pg_array['products_id'], 'products_price' => $pg_array['price'], 'specials_new_products_price' => '', 'final_price' =>	 $pg_array['price']);
}
for ($x = 0; $x < $no_of_listings; $x++) {
// replace products prices with those from customers_group table
  if(!empty($new_prices)) {
	  for ($i = 0; $i < count($new_prices); $i++) {
		if( $listing[$x]['products_id'] == $new_prices[$i]['products_id'] ) {
		  $listing[$x]['products_price'] = $new_prices[$i]['products_price'];
		  $listing[$x]['final_price'] = $new_prices[$i]['final_price'];
		}
	  }
  } // end if(!empty($new_prices)
  $listing[$x]['specials_new_products_price'] = ''; // makes sure that a retail specials price doesn't carry over to another customer group
  $listing[$x]['final_price'] = $listing[$x]['products_price']; // final price should not be the retail special price
} // end for ($x = 0; $x < $no_of_listings; $x++)
 } // end if ($customer_group_id != '0')

// an extra query is needed for all the specials

 $specials_query = tep_db_query("select products_id, specials_new_products_price from " . TABLE_SPECIALS . " where (".$select_list_of_prdct_ids.") and status = '1' and customers_group_id = '" .$customer_group_id. "'");
 while ($specials_array = tep_db_fetch_array($specials_query)) {
$new_s_prices[] = array ('products_id' => $specials_array['products_id'], 'products_price' => '', 'specials_new_products_price' =>	 $specials_array['specials_new_products_price'] , 'final_price' => $specials_array['specials_new_products_price']);
 }

// add the correct specials_new_products_price and replace final_price
 for ($x = 0; $x < $no_of_listings; $x++) {

if(!empty($new_s_prices)) {
  for ($i = 0; $i < count($new_s_prices); $i++) {
	if( $listing[$x]['products_id'] == $new_s_prices[$i]['products_id'] ) {
	  $listing[$x]['specials_new_products_price'] = $new_s_prices[$i]['specials_new_products_price'];
	  $listing[$x]['final_price'] = $new_s_prices[$i]['final_price'];
	}
  }
} // end if(!empty($new_s_prices)
 } // end for ($x = 0; $x < $no_of_listings; $x++)

//	while ($listing = tep_db_fetch_array($listing_query)) { (was original code)
 for ($x = 0; $x < $no_of_listings; $x++) {
  $rows++;

  if (($rows/2) == floor($rows/2)) {
	$list_box_contents[] = array('params' => 'class="productListing-even"');
  } else {
	$list_box_contents[] = array('params' => 'class="productListing-odd"');
  }

  $cur_row = sizeof($list_box_contents) - 1;

  for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
	$lc_align = '';
	$lc_params = '';

	switch ($column_list[$col]) {
	  case 'MASTER_LIST_MODEL':
		$lc_align = '';
		$lc_text = ' ' . $listing[$x]['products_model'] . ' ';
		break;
	  case 'MASTER_LIST_NAME':
		$lc_align = '';
		if (isset($HTTP_GET_VARS['manufacturers_id'])) {
		  $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing[$x]['products_id']) . '">' . $listing[$x]['products_name'] . '</a>';
		} else {
		  $lc_text = ' ' . $listing[$x]['products_name'] . '</a> ';
		}
		break;
	  case 'MASTER_LIST_DESCRIPTION':
		$lc_align = '';
		$lc_text = ' ' . osc_trunc_string(strip_tags($listing[$x]['products_description'], '<a><b><em><font><i><s><span><strong><sub><sup><u>'), MASTER_LIST_DESCRIPTION_LENGTH) . ' ';

		break;			
	  case 'MASTER_LIST_MANUFACTURER':
		$lc_align = '';
		$lc_text = ' <a href="' . tep_href_link(FILENAME_DEFAULT, 'manufacturers_id=' . $listing[$x]['manufacturers_id']) . '">' . $listing[$x]['manufacturers_name'] . '</a> ';
		break;
	  case 'MASTER_LIST_PRICE':
		$lc_align = 'right';
		if (tep_not_null($listing[$x]['specials_new_products_price'])) {
		  $lc_text = ' <s>' .  $currencies->display_price($listing[$x]['products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . '</s>  <span class="productSpecialPrice">' . $currencies->display_price($listing[$x]['specials_new_products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . '</span> ';
		} else {
		  $lc_text = ' ' . $currencies->display_price($listing[$x]['products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . ' ';
		}
		break; 
	  case 'MASTER_LIST_QUANTITY':
		$lc_align = 'center';
		$lc_text = ' ' . $listing[$x]['products_quantity'] . ' ';
		break;
	  case 'MASTER_LIST_WEIGHT':
		$lc_align = 'right';
		$lc_text = ' ' . $listing[$x]['products_weight'] . ' ';
		break;
	  case 'MASTER_LIST_IMAGE':
		$lc_align = 'center';
		if (isset($HTTP_GET_VARS['manufacturers_id'])) {
		  $lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing[$x]['products_image'], $listing[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a>';
		} else {
		  $lc_text = ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing[$x]['products_image'], $listing[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a> ';
		}
		break;

	  case 'MASTER_LIST_BUY_NOW': 

		$lc_align = 'center'; 
		$lc_valign = 'top';
		if ((STOCK_CHECK == 'true')&&(tep_get_products_stock($listing[$x]['products_id']) < 1)) { 
		  $lc_text = TEXT_STOCK;
		} elseif ((STOCK_CHECK == 'false')&&(tep_get_products_stock($listing[$x]['products_id']) < 1)) {
		  $qty_array = array();
		  for ($i=0; $ns = 20, $i <= $ns; $i++) {
			$qty_array[] = array('id' => $i, 'text' => $i); 

			$lc_text = tep_draw_pull_down_menu('Qty_ProdId_' . $listing[$x]['products_id'], $qty_array);

		  }
		} else {
		  $quantity = tep_get_products_stock($listing[$x]['products_id']);  
		  $qty_array = array();
		  for ($i=0; $ns = (($quantity < 20) ? $quantity : 20), $i <= $ns; $i++) {
			$qty_array[] = array('id' => $i, 'text' => $i); 

			$lc_text = tep_draw_pull_down_menu('Qty_ProdId_' . $listing[$x]['products_id'], $qty_array);

		  } 
		}
		  break;

	  case 'MASTER_LIST_OPTIONS':

		$lc_align = 'align="center"';
		$lc_text = '';
		// BOF: attribute options
		$opt_count=0;
		$products_options_name = tep_db_query("select distinct popt.products_options_id, popt.products_options_name from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_ATTRIBUTES . " patrib where patrib.products_id='" . $listing[$x]['products_id'] . "' and patrib.options_id = popt.products_options_id and popt.language_id = '" . $languages_id . "'");
		while ($products_options_name_values = tep_db_fetch_array($products_options_name)) {
		  $opt_count++;
		  $products_options_array = array();
		  $lc_text .= '<b>' . $products_options_name_values['products_options_name'] . '</b><br />' . "\n";
		  $products_options = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$listing[$x]['products_id'] . "' and pa.options_id = '" . (int)$products_options_name_values['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'");

		   while ($products_options_values = tep_db_fetch_array($products_options)) {
			 $products_options_array[] = array('id' => $products_options_values['products_options_values_id'], 'text' => $products_options_values['products_options_values_name'], 'style' => '');
			 if ($products_options_values['options_values_price'] != '0') {
			   $products_options_array[sizeof($products_options_array)-1]['text'] .= ' (' . $products_options_values['price_prefix'] . $currencies->display_price($products_options_values['options_values_price'], tep_get_tax_rate($product_info_values['products_tax_class_id'])) .') ';
			 }
		   }
		 $lc_text .= tep_draw_pull_down_menu('id_'.$listing[$x]['products_id'].'[' . $products_options_name_values['products_options_id'] . ']', $products_options_array);
		 $lc_text .= '<br />';
		}
	  if($opt_count==0) {
		$lc_text = MASTER_TEXT_NONE;
	  }
	  // EOF: attribute options
	  break;
	  }
	  $list_box_contents[$cur_row][] = array('align' => $lc_align,
										   'valign' => $lc_valign,
										   'params' => 'class="productListing-data"',
										   'text'  => $lc_text);
  }
}

new productListingBox($list_box_contents);
 } else {
$list_box_contents = array();

$list_box_contents[0] = array('params' => 'class="productListing-odd"');
$list_box_contents[0][] = array('params' => 'class="productListing-data"',
							   'text' => TEXT_NO_PRODUCTS);

new productListingBox($list_box_contents);
 }
?>
 <table border="0" width="100%" cellspacing="0" cellpadding="2">
  <tr>
	<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
  </tr>
 </table>
<?php
 if ( ($listing_split->number_of_rows > 0) && ((MASTER_PREV_NEXT_BAR_LOCATION == '2') || (MASTER_PREV_NEXT_BAR_LOCATION == '3')) ) {
?>	 
 <table border="0" width="100%" cellspacing="0" cellpadding="2">		  
 <tr>
	<td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
	<td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
  <tr>
	<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
  </tr>

<?php
 }
?>

TracyS · December 20, 2006

>_< I dunno if I'm any closer or not - LOL

I tried to add in the SPPC changes from product_listing.php to master_products.php - and I'm back to at least getting my product page viewable again in a browser - but it didn't (as of yet) adjust the prices in the table of slave products available for purchase and it is giving me two errors:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/.lew/mmherbs/mountainmeadowherbs.com/includes/functions/database.php on line 104

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/.lew/mmherbs/mountainmeadowherbs.com/includes/functions/database.php on line 100

Now there is a bit of code at the very end of the SPPC code in product_listing.php that I do not know how to (or even if I should) incorporate into master_products.php

Ok - I'm thinking it's the master_listing.php file that needs changing - but I'm having problems with how to restructure this query:

$pg_query = tep_db_query("select pg.products_id, customers_group_price as price from " . TABLE_PRODUCTS_GROUPS . " pg where (".$select_list_of_prdct_ids.") and pg.customers_group_id = '".$customer_group_id."' ");

The reason being is that this page is calling the products_master from the database rather than the products_id - so the $select_list_of_prdct_ids is actually a list of product_master

The original query to get the product_master is:

$master_query = tep_db_query("select products_master from " . TABLE_PRODUCTS . " where products_master LIKE '%" . $HTTP_GET_VARS['products_id'] . "%'");

I've been figuring out how to adjust the SPPC code up to this point to account for $master_query rather than $listing_query and 'products_master' rather than 'products_id' - but I'm not that well versed in MySQL (or PHP for that matter)

By looking at what it's doing - I'm assuming that somehow in the $pg_query we need to reference something like

where pg.product_id = '%" . $HTTP_GET_VARS['products_id']. "%'"

or basically somehow we need to reference the product_id's that go with the product_master that is found in the original query in order for $pg_query to know how to correlate the product and the group.

If you want to see the master_listing.php file - scroll up or go back a page (I pasted it in minus any of my attempts to add SPPC code to it)

Here's the edits I've made thus far (I've included the first and last few lines of code above and below the edits so you can see placement on the page):

  if ($listing_split->number_of_rows > 0) {
$rows = 0;
$master_query = tep_db_query($listing_split->sql_query);
//BOF Separate Pricing per Customer
$no_of_listings = tep_db_num_rows($master_query);
// global variable (session) $sppc_customer_group_id -> local variable customer_group_id

 if ( !isset($_SESSION['sppc_customer_group_id']) ) {
 $customer_group_id = '0';
 } else {
  $customer_group_id = $_SESSION['sppc_customer_group_id'];
 }

 while ($_listing = tep_db_fetch_array($master_query)) {
   $listing[] = $_listing;
  $list_of_prdct_ids[] = $_listing['products_master'];
  }
// next part is a debug feature, when uncommented it will print the info that this module receives
 /*
 echo '<pre>';
 print_r($listing);
 echo '</pre>';
 */
$select_list_of_prdct_ids = "products_master = '".$list_of_prdct_ids[0]."' ";
if ($no_of_listings > 1) {
  for ($n = 1; $n < count($list_of_prdct_ids); $n++) {
  $select_list_of_prdct_ids .= "or products_master = '".$list_of_prdct_ids[$n]."' ";
  }
}
// get all product prices for products with the particular customer_group_id
// however not necessary for customer_group_id = 0
if ($customer_group_id != '0') {
 $pg_query = tep_db_query("select pg.products_id, customers_group_price as price from " . TABLE_PRODUCTS_GROUPS . " pg where (".$select_list_of_prdct_ids.") and pg.customers_group_id = '".$customer_group_id."' ");

//EOF Separate Price per Customer	
while ($listing = tep_db_fetch_array($master_query)) {
  $rows++;

Does it at least look like I'm headed in the right direction? Is my assumption that we need to change the pg_query correct?

TracyS · December 20, 2006

Ok - I'm thinking it's the master_listing.php file that needs changing - but I'm having problems with how to restructure this query:
$pg_query = tep_db_query("select pg.products_id, customers_group_price as price from " . TABLE_PRODUCTS_GROUPS . " pg where (".$select_list_of_prdct_ids.") and pg.customers_group_id = '".$customer_group_id."' ");
........

Does it at least look like I'm headed in the right direction? Is my assumption that we need to change the pg_query correct?

Ok - I tried to copy/paste the code found towards the bottom of the page here: http://www.oscommerce.com/forums/lofiversion/i...53436-1350.html

But I end up with these error messages and an empty slaves table:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/.lew/mmherbs/mountainmeadowherbs.com/includes/functions/database.php on line 104

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/.lew/mmherbs/mountainmeadowherbs.com/includes/functions/database.php on line 100

So, of course, I start searching the forums for those error messages and I found an older post from JanZ that basically the errors mean that the $listing_query hasn't been done so the num_rows and fetch_array have nothing to work with.

Which brings me back to where I was in my previous post - I think we have to somehow change the listing_query to master_query and then change the pg_query to account for the difference in master_query not getting the product_id :blink:

All I know is this is waaaay above my PHP / MySQL ability - so I am very hopeful that somebody here can offer some insight as to the best way to adjust this. In the meantime I'm going to do some more forum searches and see if my PHP and MySQL book covers anything even remotely close to this :huh:

pingpongrob · December 20, 2006

TracyS · December 20, 2006

Ok - I tried to copy/paste the code found towards the bottom of the page here: http://www.oscommerce.com/forums/lofiversion/i...53436-1350.html

All I know is this is waaaay above my PHP / MySQL ability - so I am very hopeful that somebody here can offer some insight as to the best way to adjust this. In the meantime I'm going to do some more forum searches and see if my PHP and MySQL book covers anything even remotely close to this :huh:

:D WOOHOO!!! :D

It took several different posts to get everything I needed altogether and into my master_listing.php file - but it's finally done!! :D

For others who are looking I am going to make it simple by posting my working master_listing.php file - you can always copy and paste it into notepad - save it as whatever you'd like, and then run a comparison on it with WinMerge (or similar tool) to find where the differences are :thumbsup:

<?php
/*
 $Id: master_listing.php
 adapted for Separate Pricing Per Customer v4 2005/02/26

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Master Products MS2  - JOHNSON - 05/07/2003 [email protected]

 Copyright (c) 2003 Suomedia - Dynamic Content Management

 Released under the GNU General Public License

*/

 $listing_split = new splitPageResults($master_sql, MAX_DISPLAY_SEARCH_RESULTS, 'products_master');



 if ( ($listing_split->number_of_rows > 0) && ( (MASTER_PREV_NEXT_BAR_LOCATION == '1') || (MASTER_PREV_NEXT_BAR_LOCATION == '3') ) ) {


?>


<table border="0" width="100%" cellspacing="0" cellpadding="2">
 <tr>
<td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
<td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
<?php
 }



 $list_box_contents = array();

 for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
switch ($column_list[$col]) {
  case 'MASTER_LIST_MODEL':
	$lc_text = TABLE_HEADING_MODEL;
	$lc_align = '';
	break;
  case 'MASTER_LIST_NAME':
	$lc_text = TABLE_HEADING_PRODUCTS;
	$lc_align = '';
	break;
  case 'MASTER_LIST_DESCRIPTION':
	$lc_text = TABLE_HEADING_DESCRIPTION;
	$lc_align = 'center';
	break;
  case 'MASTER_LIST_ATTRIBUTES':
	$lc_text = TABLE_HEADING_ATTRIBUTES;
	$lc_align = 'center';
	break;				
  case 'MASTER_LIST_MANUFACTURER':
	$lc_text = TABLE_HEADING_MANUFACTURER;
	$lc_align = '';
	break;
  case 'MASTER_LIST_PRICE':
	$lc_text = TABLE_HEADING_PRICE;
	$lc_align = 'right';
	break;
  case 'MASTER_LIST_QUANTITY':
	$lc_text = TABLE_HEADING_QUANTITY;
	$lc_align = 'right';
	break;
  case 'MASTER_LIST_WEIGHT':
	$lc_text = TABLE_HEADING_WEIGHT;
	$lc_align = 'right';
	break;
  case 'MASTER_LIST_IMAGE':
	$lc_text = TABLE_HEADING_IMAGE;
	$lc_align = 'center';
	break;
  case 'MASTER_LIST_BUY_NOW':
	$lc_text = TABLE_HEADING_BUY_NOW;
	$lc_align = 'center';
	break;
	case 'MASTER_LIST_OPTIONS':
	  $lc_text = TABLE_HEADING_OPTIONS;
	  $lc_align = 'align="center"';
	  break;
}

if ( ($column_list[$col] != 'MASTER_LIST_BUY_NOW') && ($column_list[$col] != 'MASTER_LIST_IMAGE') && ($column_list[$col] != 'MASTER_LIST_MULTIPLE') && ($column_list[$col] != 'MASTER_LIST_DESCRIPTION') && ($column_list[$col] != 'MASTER_LIST_OPTIONS') ) {
  $lc_text = tep_create_sort_heading($HTTP_GET_VARS['sort'], $col+1, $lc_text);
}

$list_box_contents[0][] = array('align' => $lc_align,
								'params' => 'class="productListing-heading"',
								'text' => ' ' . $lc_text . ' ');
 }

 if ($listing_split->number_of_rows > 0) {
$rows = 0;
$master_query = tep_db_query($listing_split->sql_query);
// BOF Separate Pricing per Customer
 $no_of_listings = tep_db_num_rows($master_query);
// global variable (session) $sppc_customer_group_id -> local variable customer_group_id

if(!tep_session_is_registered('sppc_customer_group_id')) {
$customer_group_id = '0';
} else {
$customer_group_id = $sppc_customer_group_id;
}

while ($_listing = tep_db_fetch_array($master_query)) {
// let's start with default settings, you never know
$_new_listing = array_merge($_listing , $default_settings);
$listing[] = $_new_listing;
$list_of_prdct_ids[] = $_listing['products_id'];
}
// next part is a debug feature, when uncommented it will print the info that this module receives

/*   echo '<pre>';
print_r($listing);
echo '</pre>'; */

$select_list_of_prdct_ids = "products_id = '".$list_of_prdct_ids[0]."' ";
if ($no_of_listings > 1) {
for ($n = 1; $n < count($list_of_prdct_ids); $n++) {
$select_list_of_prdct_ids .= "or products_id = '".$list_of_prdct_ids[$n]."' ";
}
}

// get all product prices for products with the particular customer_group_id
// however not necessary for customer_group_id = 0
if ($customer_group_id != '0') {
$pg_query = tep_db_query("select pg.products_id, customers_group_price as price from " . TABLE_PRODUCTS_GROUPS . " pg where (".$select_list_of_prdct_ids.") and pg.customers_group_id = '".$customer_group_id."' ");
//   $no_of_pg_products = tep_db_num_rows($pg_query);
 while ($pg_array = tep_db_fetch_array($pg_query)) {
 $new_prices[] = array ('products_id' => $pg_array['products_id'], 'products_price' => $pg_array['price'], 'specials_new_products_price' => '', 'final_price' => $pg_array['price']);
 }
for ($x = 0; $x < $no_of_listings; $x++) {
// replace products prices with those from customers_group table
if(!empty($new_prices)) {
   for ($i = 0; $i < count($new_prices); $i++) {
  if( $listing[$x]['products_id'] == $new_prices[$i]['products_id'] ) {
$listing[$x]['products_price'] = $new_prices[$i]['products_price'];
$listing[$x]['final_price'] = $new_prices[$i]['final_price'];
  }
 }
} // end if(!empty($new_prices)
$listing[$x]['specials_new_products_price'] = ''; // makes sure that a retail specials price doesn't carry over to another customer group
$listing[$x]['final_price'] = $listing[$x]['products_price']; // final price should not be the retail special price
} // end for ($x = 0; $x < $no_of_listings; $x++)
} // end if ($customer_group_id != '0')

// an extra query is needed for all the specials

$specials_query = tep_db_query("select products_id, specials_new_products_price from " . TABLE_SPECIALS . " where (".$select_list_of_prdct_ids.") and status = '1' and customers_group_id = '" .$customer_group_id. "'");
while ($specials_array = tep_db_fetch_array($specials_query)) {
$new_s_prices[] = array ('products_id' => $specials_array['products_id'], 'products_price' => '', 'specials_new_products_price' => $specials_array['specials_new_products_price'] , 'final_price' => $specials_array['specials_new_products_price']);
}

// add the correct specials_new_products_price and replace final_price
for ($x = 0; $x < $no_of_listings; $x++) {

  if(!empty($new_s_prices)) {
  for ($i = 0; $i < count($new_s_prices); $i++) {
if( $listing[$x]['products_id'] == $new_s_prices[$i]['products_id'] ) {
  $listing[$x]['specials_new_products_price'] = $new_s_prices[$i]['specials_new_products_price'];
  $listing[$x]['final_price'] = $new_s_prices[$i]['final_price'];
}
  }
 } // end if(!empty($new_s_prices)
} // end for ($x = 0; $x < $no_of_listings; $x++)

//	while ($listing = tep_db_fetch_array($listing_query)) { (was original code)
for ($x = 0; $x < $no_of_listings; $x++) {

$rows++;

if (($rows/2) == floor($rows/2)) {
  $list_box_contents[] = array('params' => 'class="productListing-even"');
} else {
  $list_box_contents[] = array('params' => 'class="productListing-odd"');
}

$cur_row = sizeof($list_box_contents) - 1;

for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
  $lc_align = '';
  $lc_params = '';

  switch ($column_list[$col]) {
	case 'MASTER_LIST_MODEL':
	  $lc_align = '';
	  $lc_text = ' ' . $listing[$x]['products_model'] . ' ';
	  break;
	case 'MASTER_LIST_NAME':
	  $lc_align = '';
	  if (isset($HTTP_GET_VARS['manufacturers_id'])) {
		$lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing[$x]['products_id']) . '">' . $listing[$x]['products_name'] . '</a>';
	  } else {
		$lc_text = ' ' . $listing[$x]['products_name'] . '</a> ';
	  }
	  break;
	case 'MASTER_LIST_DESCRIPTION':
	  $lc_align = '';
	  $lc_text = ' ' . osc_trunc_string(strip_tags($listing[$x]['products_description'], '<a><b><em><font><i><s><span><strong><sub><sup><u>'), MASTER_LIST_DESCRIPTION_LENGTH) . ' ';

	  break;			
	case 'MASTER_LIST_MANUFACTURER':
	  $lc_align = '';
	  $lc_text = ' <a href="' . tep_href_link(FILENAME_DEFAULT, 'manufacturers_id=' . $listing[$x]['manufacturers_id']) . '">' . $listing[$x]['manufacturers_name'] . '</a> ';
	  break;
	 case 'MASTER_LIST_PRICE':
	   $lc_align = 'right';
	   if (tep_not_null($listing[$x]['specials_new_products_price'])) {
		 $lc_text = ' <s>' .  $currencies->display_price($listing[$x]['products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . '</s>  <span class="productSpecialPrice">' . $currencies->display_price($listing[$x]['specials_new_products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . '</span> ';
	   } else {
		 $lc_text = ' ' . $currencies->display_price($listing[$x]['products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . ' ';
	   }
	   break;
	case 'MASTER_LIST_QUANTITY':
	  $lc_align = 'right';
	  $lc_text = ' ' . $listing[$x]['products_quantity'] . ' ';
	  break;
	case 'MASTER_LIST_WEIGHT':
	  $lc_align = 'right';
	  $lc_text = ' ' . $listing[$x]['products_weight'] . ' ';
	  break;
	case 'MASTER_LIST_IMAGE':
	  $lc_align = 'center';
	  if (isset($HTTP_GET_VARS['manufacturers_id'])) {
		$lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing[$x]['products_image'], $listing[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a>';
	  } else {
		$lc_text = ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing[$x]['products_image'], $listing[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a> ';
	  }
	  break;

	case 'MASTER_LIST_BUY_NOW':

	  $lc_align = 'center';
	  $lc_valign = 'top';
	  if ((STOCK_CHECK == 'true')&&(tep_get_products_stock($listing[$x]['products_id']) < 1)) {
	  $lc_text = TEXT_STOCK;
	} elseif ((STOCK_CHECK == 'false')&&(tep_get_products_stock($listing[$x]['products_id']) < 1)) {
	  $qty_array = array();
	  for ($i=0; $ns = 20, $i <= $ns; $i++) {
	  $qty_array[] = array('id' => $i, 'text' => $i);

	  $lc_text = tep_draw_pull_down_menu('Qty_ProdId_' . $listing[$x]['products_id'], $qty_array);

	  }
	} else {
	  $quantity = tep_get_products_stock($listing[$x]['products_id']);  
	  $qty_array = array();
	  for ($i=0; $ns = (($quantity < 20) ? $quantity : 20), $i <= $ns; $i++) {
	  $qty_array[] = array('id' => $i, 'text' => $i);

	  $lc_text = tep_draw_pull_down_menu('Qty_ProdId_' . $listing[$x]['products_id'], $qty_array);

	  }
  }
	 break;
  }
  $list_box_contents[$cur_row][] = array('align' => $lc_align,
										 'valign' => $lc_valign,
										 'params' => 'class="productListing-data"',
										 'text'  => $lc_text);
}
 }

 new productListingBox($list_box_contents);
} else {
 $list_box_contents = array();

 $list_box_contents[0] = array('params' => 'class="productListing-odd"');
 $list_box_contents[0][] = array('params' => 'class="productListing-data"',
							 'text' => TEXT_NO_PRODUCTS);

 new productListingBox($list_box_contents);
}
?>	 <table border="0" width="100%" cellspacing="0" cellpadding="2">
  <tr>
	<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
  </tr>
 </table>
<?php
 if ( ($listing_split->number_of_rows > 0) && ((MASTER_PREV_NEXT_BAR_LOCATION == '2') || (MASTER_PREV_NEXT_BAR_LOCATION == '3')) ) {
?>	 
 <table border="0" width="100%" cellspacing="0" cellpadding="2">		  
 <tr>
	<td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
	<td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
  <tr>
	<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
  </tr>

<?php
 }
?>

This version does change your Quantity field to a pull down menu of 0-10 (so the max quantity at this stage is 10 - whereas in the shopping cart a customer could click and type into the quantity field how many they want).

If you don't want the pull down menu look for this block of code:

case 'MASTER_LIST_BUY_NOW':

	  $lc_align = 'center';
	  $lc_valign = 'top';
	  if ((STOCK_CHECK == 'true')&&(tep_get_products_stock($listing[$x]['products_id']) < 1)) {
	  $lc_text = TEXT_STOCK;
	} elseif ((STOCK_CHECK == 'false')&&(tep_get_products_stock($listing[$x]['products_id']) < 1)) {
	  $qty_array = array();
	  for ($i=0; $ns = 20, $i <= $ns; $i++) {
	  $qty_array[] = array('id' => $i, 'text' => $i);

	  $lc_text = tep_draw_pull_down_menu('Qty_ProdId_' . $listing[$x]['products_id'], $qty_array);

	  }
	} else {
	  $quantity = tep_get_products_stock($listing[$x]['products_id']);  
	  $qty_array = array();
	  for ($i=0; $ns = (($quantity < 20) ? $quantity : 20), $i <= $ns; $i++) {
	  $qty_array[] = array('id' => $i, 'text' => $i);

	  $lc_text = tep_draw_pull_down_menu('Qty_ProdId_' . $listing[$x]['products_id'], $qty_array);

	  }
  }
	 break;

Twice in there you should see this line:

$lc_text = tep_draw_pull_down_menu('Qty_ProdId_' . $listing[$x]['products_id'], $qty_array);

Change it to this in both cases:

$lc_text = tep_draw_input_field('Qty_ProdId_' . $listing['products_id'], '', 'size="4"');

Also - if you want a quantity number in there automatically (like in the original Master Products files) then you just need to add that number between the '' in both cases. If you wanted it to automatically say "1" for example, your code would look like this:

$lc_text = tep_draw_input_field('Qty_ProdId_' . $listing['products_id'], '1', 'size="4"');

Hopefully this post will help someone else out who struggles with getting these two great contrib's to work together :thumbsup:

Jan Zonjee · December 20, 2006

Which brings me back to where I was in my previous post - I think we have to somehow change the listing_query to master_query and then change the pg_query to account for the difference in master_query not getting the product_id

Have you tried the master_products.php and master_listing.php that I posted this evening? Guess not but you have seemed to get it in order now. Just take a look at my suggestion for master_sql. I don't think the standard one is a good idea regarding the way specials work in SPPC...

Jan Zonjee · December 20, 2006

when I insert a new product, the image is uploaded into the images folder but the image name is not inserted in the database.

Going through the code and comparing it to the categories.php of SPPC I don't see anything obviously wrong with it. Seems fine. Aren't there more people having this problem? SPPC does nothing with the image and since everything else is apparently stored fine it does not seem logical that the name of the picture does not get saved...

TracyS · December 20, 2006

This version does change your Quantity field to a pull down menu of 0-10 (so the max quantity at this stage is 10 - whereas in the shopping cart a customer could click and type into the quantity field how many they want).

If you don't want the pull down menu look for this block of code:

OY! >_< Well - the bit about changing the pull down menu back to the input field didn't seem to work :'(

When I uploaded the changes I listed above for the quantity field all looked well - but then when I entered a quantity and clicked to add to cart it doesn't put anything in my cart :'(

Please - does somebody know how I can get back to the input field rather than a pull down menu for the quanity a person would like to order? (We have wholesale customers who order more than 10 at a time and it only allows up to 10 on the pull down - so a text input field would be much better).

Thank you

TracyS · December 20, 2006

Going through the code and comparing it to the categories.php of SPPC I don't see anything obviously wrong with it. Seems fine. Aren't there more people having this problem? SPPC does nothing with the image and since everything else is apparently stored fine it does not seem logical that the name of the picture does not get saved...

Hi Jan!! :D

I didn't see your post earlier :blush: (been to focused on this master_listing page I guess) - I will definitely be comparing it to the one I currently have :D

At the moment I don't have any specials listed for wholesale - but the one's for retail are showing the correct special price - I'll have to create one just for the wholesale group and check it out to see if that works. If so - then I think the original query should be fine - if not, then I guess I'll have to try changing it :blush:

Thank you very much for all of your support on this forum - there are only a few boards that I have found that have such great support for their contrib's! :D

Have a great evening - I'll work on this some more tomorrow :thumbsup:

TracyS · December 21, 2006

Have you tried the master_products.php and master_listing.php that I posted this evening? Guess not but you have seemed to get it in order now. Just take a look at my suggestion for master_sql. I don't think the standard one is a good idea regarding the way specials work in SPPC...

Thank you JanZ!! :D

I compared what you had put up with what I had - made some edits to what I had and got the quantity field back to a field where you just type in the number of products you want rather than a pull down menu and all is working - YAY!!!!! :thumbsup:

Now - I'm curious about this master_sql query. I'm still learning how all of this works so please bear with me :blush: I made a test special product in the wholesale group. If I'm not logged in, or logged in as a retail customer, I see the original price with the slash through it, and then the special price for a product on special in the retail group. When I log in as a wholesaler I get the same thing - only for the products that are on special for the wholesale group. Also, the products add to the cart correctly and show the correct special price in the cart for either group. So all seems to be working correctly.

The original master_sql query is:

$master_query = tep_db_query("select products_master from " . TABLE_PRODUCTS . " where products_master LIKE '%" . $HTTP_GET_VARS['products_id'] . "%'");

This is, I think, telling the server to get the products whose products_master field is the same as the current products products_id field. This way it knows which slave products it should be showing on the master products page.

If we change it to:

$master_sql = "select  " . $select_column_list . " p.products_id,  p.manufacturers_id, p.products_tax_class_id, NULL as specials_new_products_price, p.products_price from ". TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id where p.products_id = pd.products_id and p.products_master = '" . $thisquery['products_master'] . "' and p.products_status = '1' and pd.language_id = '" . (int)$languages_id . "'";

then how does it know which products have the current product_id as their products_master field?

Also - in the p.products_master = '" . $thisquery['products_master'] . "' part - is $thisquery literally meaning "this query" as in the $master_sql query - or does it mean that there should be another query somewhere named $this_query ?

I am sure you are going to look at my questions and feel the answers should be staring me in the face :blush: But I had just started learning how to create my own PHP / MySQL when we learned we needed to use osC - so this is my first ever osC website, my first full online shopping site, and quite the big plunge into some advanced PHP and MySQL :blink: So now that I'm getting things up and running I'm trying to slow down a little bit here and there and learn "why" they are up and running in hopes that I will be able to become a little more capable with all of this, and hopefully someday be able to offer support and contributions back to the community :)

For anyone who may be looking for this in the future - here is my modified for SPPC master_listing.php file :)

<?php
/*
 $Id: master_listing.php
 adapted for Separate Pricing Per Customer v4 2005/02/26

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Master Products MS2  - JOHNSON - 05/07/2003 [email protected]

 Copyright (c) 2003 Suomedia - Dynamic Content Management

 Released under the GNU General Public License

*/

 $listing_split = new splitPageResults($master_sql, MAX_DISPLAY_SEARCH_RESULTS, 'products_master');



 if ( ($listing_split->number_of_rows > 0) && ( (MASTER_PREV_NEXT_BAR_LOCATION == '1') || (MASTER_PREV_NEXT_BAR_LOCATION == '3') ) ) {


?>


<table border="0" width="100%" cellspacing="0" cellpadding="2">
 <tr>
<td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
<td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
<?php
 }



 $list_box_contents = array();

 for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
switch ($column_list[$col]) {
  case 'MASTER_LIST_MODEL':
	$lc_text = TABLE_HEADING_MODEL;
	$lc_align = '';
	break;
  case 'MASTER_LIST_NAME':
	$lc_text = TABLE_HEADING_PRODUCTS;
	$lc_align = '';
	break;
  case 'MASTER_LIST_DESCRIPTION':
	$lc_text = TABLE_HEADING_DESCRIPTION;
	$lc_align = 'center';
	break;
  case 'MASTER_LIST_ATTRIBUTES':
	$lc_text = TABLE_HEADING_ATTRIBUTES;
	$lc_align = 'center';
	break;				
  case 'MASTER_LIST_MANUFACTURER':
	$lc_text = TABLE_HEADING_MANUFACTURER;
	$lc_align = '';
	break;
  case 'MASTER_LIST_PRICE':
	$lc_text = TABLE_HEADING_PRICE;
	$lc_align = 'right';
	break;
  case 'MASTER_LIST_QUANTITY':
	$lc_text = TABLE_HEADING_QUANTITY;
	$lc_align = 'right';
	break;
  case 'MASTER_LIST_WEIGHT':
	$lc_text = TABLE_HEADING_WEIGHT;
	$lc_align = 'right';
	break;
  case 'MASTER_LIST_IMAGE':
	$lc_text = TABLE_HEADING_IMAGE;
	$lc_align = 'center';
	break;
  case 'MASTER_LIST_BUY_NOW':
	$lc_text = TABLE_HEADING_BUY_NOW;
	$lc_align = 'center';
	break;
	case 'MASTER_LIST_OPTIONS':
	  $lc_text = TABLE_HEADING_OPTIONS;
	  $lc_align = 'align="center"';
	  break;
}

if ( ($column_list[$col] != 'MASTER_LIST_BUY_NOW') && ($column_list[$col] != 'MASTER_LIST_IMAGE') && ($column_list[$col] != 'MASTER_LIST_MULTIPLE') && ($column_list[$col] != 'MASTER_LIST_DESCRIPTION') && ($column_list[$col] != 'MASTER_LIST_OPTIONS') ) {
  $lc_text = tep_create_sort_heading($HTTP_GET_VARS['sort'], $col+1, $lc_text);
}

$list_box_contents[0][] = array('align' => $lc_align,
								'params' => 'class="productListing-heading"',
								'text' => ' ' . $lc_text . ' ');
 }

 if ($listing_split->number_of_rows > 0) {
$rows = 0;
$master_query = tep_db_query($listing_split->sql_query);
// BOF Separate Pricing per Customer
 $no_of_listings = tep_db_num_rows($master_query);
  global $sppc_customer_group_id;
// global variable (session) $sppc_customer_group_id -> local variable customer_group_id

if(!tep_session_is_registered('sppc_customer_group_id')) {
$customer_group_id = '0';
} else {
$customer_group_id = $sppc_customer_group_id;
}

while ($_listing = tep_db_fetch_array($master_query)) {
$listing[] = $_listing;
$list_of_prdct_ids[] = $_listing['products_id'];
}
// next part is a debug feature, when uncommented it will print the info that this module receives

/*   echo '<pre>';
print_r($listing);
echo '</pre>'; */

$select_list_of_prdct_ids = "products_id = '".$list_of_prdct_ids[0]."' ";
if ($no_of_listings > 1) {
for ($n = 1; $n < count($list_of_prdct_ids); $n++) {
$select_list_of_prdct_ids .= "or products_id = '".$list_of_prdct_ids[$n]."' ";
}
}

// get all product prices for products with the particular customer_group_id
// however not necessary for customer_group_id = 0
if ($customer_group_id != '0') {
$pg_query = tep_db_query("select pg.products_id, customers_group_price as price from " . TABLE_PRODUCTS_GROUPS . " pg where (".$select_list_of_prdct_ids.") and pg.customers_group_id = '".$customer_group_id."' ");
//   $no_of_pg_products = tep_db_num_rows($pg_query);
 while ($pg_array = tep_db_fetch_array($pg_query)) {
 $new_prices[] = array ('products_id' => $pg_array['products_id'], 'products_price' => $pg_array['price'], 'specials_new_products_price' => '', 'final_price' => $pg_array['price']);
 }
for ($x = 0; $x < $no_of_listings; $x++) {
// replace products prices with those from customers_group table
if(!empty($new_prices)) {
   for ($i = 0; $i < count($new_prices); $i++) {
  if( $listing[$x]['products_id'] == $new_prices[$i]['products_id'] ) {
$listing[$x]['products_price'] = $new_prices[$i]['products_price'];
$listing[$x]['final_price'] = $new_prices[$i]['final_price'];
  }
 }
} // end if(!empty($new_prices)
$listing[$x]['specials_new_products_price'] = ''; // makes sure that a retail specials price doesn't carry over to another customer group
$listing[$x]['final_price'] = $listing[$x]['products_price']; // final price should not be the retail special price
} // end for ($x = 0; $x < $no_of_listings; $x++)
} // end if ($customer_group_id != '0')

// an extra query is needed for all the specials

$specials_query = tep_db_query("select products_id, specials_new_products_price from " . TABLE_SPECIALS . " where (".$select_list_of_prdct_ids.") and status = '1' and customers_group_id = '" .$customer_group_id. "'");
while ($specials_array = tep_db_fetch_array($specials_query)) {
$new_s_prices[] = array ('products_id' => $specials_array['products_id'], 'products_price' => '', 'specials_new_products_price' => $specials_array['specials_new_products_price'] , 'final_price' => $specials_array['specials_new_products_price']);
}

// add the correct specials_new_products_price and replace final_price
for ($x = 0; $x < $no_of_listings; $x++) {

  if(!empty($new_s_prices)) {
  for ($i = 0; $i < count($new_s_prices); $i++) {
if( $listing[$x]['products_id'] == $new_s_prices[$i]['products_id'] ) {
  $listing[$x]['specials_new_products_price'] = $new_s_prices[$i]['specials_new_products_price'];
  $listing[$x]['final_price'] = $new_s_prices[$i]['final_price'];
}
  }
 } // end if(!empty($new_s_prices)
} // end for ($x = 0; $x < $no_of_listings; $x++)

//	while ($listing = tep_db_fetch_array($listing_query)) { (was original code)
for ($x = 0; $x < $no_of_listings; $x++) {
$rows++;

if (($rows/2) == floor($rows/2)) {
  $list_box_contents[] = array('params' => 'class="productListing-even"');
} else {
  $list_box_contents[] = array('params' => 'class="productListing-odd"');
}

$cur_row = sizeof($list_box_contents) - 1;

for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
  $lc_align = '';
  $lc_params = '';

  switch ($column_list[$col]) {
	case 'MASTER_LIST_MODEL':
	  $lc_align = '';
	  $lc_text = ' ' . $listing[$x]['products_model'] . ' ';
	  break;
	case 'MASTER_LIST_NAME':
	  $lc_align = '';
	  if (isset($HTTP_GET_VARS['manufacturers_id'])) {
		$lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing[$x]['products_id']) . '">' . $listing[$x]['products_name'] . '</a>';
	  } else {
		$lc_text = ' ' . $listing[$x]['products_name'] . '</a> ';
	  }
	  break;
	case 'MASTER_LIST_DESCRIPTION':
	  $lc_align = '';
	  $lc_text = ' ' . osc_trunc_string(strip_tags($listing[$x]['products_description'], '<a><b><em><font><i><s><span><strong><sub><sup><u>'), MASTER_LIST_DESCRIPTION_LENGTH) . ' ';

	  break;			
	case 'MASTER_LIST_MANUFACTURER':
	  $lc_align = '';
	  $lc_text = ' <a href="' . tep_href_link(FILENAME_DEFAULT, 'manufacturers_id=' . $listing[$x]['manufacturers_id']) . '">' . $listing[$x]['manufacturers_name'] . '</a> ';
	  break;
	 case 'MASTER_LIST_PRICE':
	   $lc_align = 'right';
	   if (tep_not_null($listing[$x]['specials_new_products_price'])) {
		 $lc_text = ' <s>' .  $currencies->display_price($listing[$x]['products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . '</s>  <span class="productSpecialPrice">' . $currencies->display_price($listing[$x]['specials_new_products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . '</span> ';
	   } else {
		 $lc_text = ' ' . $currencies->display_price($listing[$x]['products_price'], tep_get_tax_rate($listing[$x]['products_tax_class_id'])) . ' ';
	   }
	   break;
	case 'MASTER_LIST_QUANTITY':
	  $lc_align = 'right';
	  $lc_text = ' ' . $listing[$x]['products_quantity'] . ' ';
	  break;
	case 'MASTER_LIST_WEIGHT':
	  $lc_align = 'right';
	  $lc_text = ' ' . $listing[$x]['products_weight'] . ' ';
	  break;
	case 'MASTER_LIST_IMAGE':
	  $lc_align = 'center';
	  if (isset($HTTP_GET_VARS['manufacturers_id'])) {
		$lc_text = '<a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'manufacturers_id=' . $HTTP_GET_VARS['manufacturers_id'] . '&products_id=' . $listing[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing[$x]['products_image'], $listing[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a>';
	  } else {
		$lc_text = ' <a href="' . tep_href_link(FILENAME_PRODUCT_INFO, ($cPath ? 'cPath=' . $cPath . '&' : '') . 'products_id=' . $listing[$x]['products_id']) . '">' . tep_image(DIR_WS_IMAGES . $listing[$x]['products_image'], $listing[$x]['products_name'], SMALL_IMAGE_WIDTH, SMALL_IMAGE_HEIGHT) . '</a> ';
	  }
	  break;

	case 'MASTER_LIST_BUY_NOW':

	  $lc_align = 'center';
	  $lc_valign = 'top';
	  if ((STOCK_CHECK == 'true')&&(tep_get_products_stock($listing[$x]['products_id']) < 1)) {
	  $lc_text = TEXT_STOCK;
	} elseif ((STOCK_CHECK == 'false')&&(tep_get_products_stock($listing[$x]['products_id']) < 1)) {
	  $qty_array = array();
	  for ($i=0; $ns = 20, $i <= $ns; $i++) {
	  $qty_array[] = array('id' => $i, 'text' => $i);

	  $lc_text = tep_draw_input_field('Qty_ProdId_' . $listing[$x]['products_id'], '', 'size="4"');

	  }
	} else {
	  $quantity = tep_get_products_stock($listing[$x]['products_id']);  
	  $qty_array = array();
	  for ($i=0; $ns = (($quantity < 20) ? $quantity : 20), $i <= $ns; $i++) {
	  $qty_array[] = array('id' => $i, 'text' => $i);

	  $lc_text = tep_draw_input_field('Qty_ProdId_' . $listing[$x]['products_id'], '', 'size="4"');

	  }
  }
	 break;

  case 'MASTER_LIST_OPTIONS':

		$lc_align = 'align="center"';
		$lc_text = '';
		// BOF: attribute options
		$opt_count=0;
		$products_options_name = tep_db_query("select distinct popt.products_options_id, popt.products_options_name from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_ATTRIBUTES . " patrib where patrib.products_id='" . $listing[$x]['products_id'] . "' and patrib.options_id = popt.products_options_id and popt.language_id = '" . $languages_id . "' ORDER by products_options_id");
		while ($products_options_name_values = tep_db_fetch_array($products_options_name)) {
		  $opt_count++;
		  $products_options_array = array();
		  $lc_text .= '<b>' . $products_options_name_values['products_options_name'] . '</b><br />' . "\n";
		  $products_options = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$listing[$x]['products_id'] . "' and pa.options_id = '" . (int)$products_options_name_values['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'");

		   while ($products_options_values = tep_db_fetch_array($products_options)) {
			 $products_options_array[] = array('id' => $products_options_values['products_options_values_id'], 'text' => $products_options_values['products_options_values_name'], 'style' => '');
			 if ($products_options_values['options_values_price'] != '0') {
			   $products_options_array[sizeof($products_options_array)-1]['text'] .= ' (' . $products_options_values['price_prefix'] . $currencies->display_price($products_options_values['options_values_price'], tep_get_tax_rate($product_info_values['products_tax_class_id'])) .') ';
			 }
		   }
		 $lc_text .= tep_draw_pull_down_menu('id_'.$listing[$x]['products_id'].'[' . $products_options_name_values['products_options_id'] . ']', $products_options_array);
		 $lc_text .= '<br />';
		}
	  if($opt_count==0) {
		$lc_text = MASTER_TEXT_NONE;
	  }
	  // EOF: attribute options
	  break;
  }
  $list_box_contents[$cur_row][] = array('align' => $lc_align,
										 'valign' => $lc_valign,
										 'params' => 'class="productListing-data"',
										 'text'  => $lc_text);
}
 }

 new productListingBox($list_box_contents);
} else {
 $list_box_contents = array();

 $list_box_contents[0] = array('params' => 'class="productListing-odd"');
 $list_box_contents[0][] = array('params' => 'class="productListing-data"',
							 'text' => TEXT_NO_PRODUCTS);

 new productListingBox($list_box_contents);
}
?>	 
  <table border="0" width="100%" cellspacing="0" cellpadding="2">
  <tr>
	<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
  </tr>
 </table>
<?php
 if ( ($listing_split->number_of_rows > 0) && ((MASTER_PREV_NEXT_BAR_LOCATION == '2') || (MASTER_PREV_NEXT_BAR_LOCATION == '3')) ) {
?>	 
 <table border="0" width="100%" cellspacing="0" cellpadding="2">		  
 <tr>
	<td class="smallText"><?php echo $listing_split->display_count(TEXT_DISPLAY_NUMBER_OF_PRODUCTS); ?></td>
	<td class="smallText" align="right"><?php echo TEXT_RESULT_PAGE . ' ' . $listing_split->display_links(MAX_DISPLAY_PAGE_LINKS, tep_get_all_get_params(array('page', 'info', 'x', 'y'))); ?></td>
 </tr>
</table>
  <tr>
	<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>
  </tr>

<?php
 }
?>

TracyS · December 21, 2006

This version does change your Quantity field to a pull down menu of 0-10 (so the max quantity at this stage is 10 - whereas in the shopping cart a customer could click and type into the quantity field how many they want).

If you don't want the pull down menu look for this block of code:
case 'MASTER_LIST_BUY_NOW':

	  $lc_align = 'center';
	  $lc_valign = 'top';
	  if ((STOCK_CHECK == 'true')&&(tep_get_products_stock($listing[$x]['products_id']) < 1)) {
	  $lc_text = TEXT_STOCK;
	} elseif ((STOCK_CHECK == 'false')&&(tep_get_products_stock($listing[$x]['products_id']) < 1)) {
	  $qty_array = array();
	  for ($i=0; $ns = 20, $i <= $ns; $i++) {
	  $qty_array[] = array('id' => $i, 'text' => $i);

	  $lc_text = tep_draw_pull_down_menu('Qty_ProdId_' . $listing[$x]['products_id'], $qty_array);

	  }
	} else {
	  $quantity = tep_get_products_stock($listing[$x]['products_id']);  
	  $qty_array = array();
	  for ($i=0; $ns = (($quantity < 20) ? $quantity : 20), $i <= $ns; $i++) {
	  $qty_array[] = array('id' => $i, 'text' => $i);

	  $lc_text = tep_draw_pull_down_menu('Qty_ProdId_' . $listing[$x]['products_id'], $qty_array);

	  }
  }
	 break;
Twice in there you should see this line:
$lc_text = tep_draw_pull_down_menu('Qty_ProdId_' . $listing[$x]['products_id'], $qty_array);
Change it to this in both cases:
$lc_text = tep_draw_input_field('Qty_ProdId_' . $listing['products_id'], '', 'size="4"');
Also - if you want a quantity number in there automatically (like in the original Master Products files) then you just need to add that number between the '' in both cases. If you wanted it to automatically say "1" for example, your code would look like this:
$lc_text = tep_draw_input_field('Qty_ProdId_' . $listing['products_id'], '1', 'size="4"');

I found my mistake in the above code change - after .$listing there should be an [$x] - so to change it from a pull down to a text input field you should use:

$lc_text = tep_draw_input_field('Qty_ProdId_' . $listing[$x]['products_id'], '', 'size="4"');

Hope that helps someone out there :thumbsup:

Jan Zonjee · December 21, 2006

Now - I'm curious about this master_sql query. I'm still learning how all of this works so please bear with me :blush: I made a test special product in the wholesale group. If I'm not logged in, or logged in as a retail customer, I see the original price with the slash through it, and then the special price for a product on special in the retail group. When I log in as a wholesaler I get the same thing - only for the products that are on special for the wholesale group. Also, the products add to the cart correctly and show the correct special price in the cart for either group. So all seems to be working correctly.

The original master_sql query is:
$master_query = tep_db_query("select products_master from " . TABLE_PRODUCTS . " where products_master LIKE '%" . $HTTP_GET_VARS['products_id'] . "%'");
This is, I think, telling the server to get the products whose products_master field is the same as the current products products_id field. This way it knows which slave products it should be showing on the master products page.

If we change it to:
$master_sql = "select  " . $select_column_list . " p.products_id,  p.manufacturers_id, p.products_tax_class_id, NULL as specials_new_products_price, p.products_price from ". TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id where p.products_id = pd.products_id and p.products_master = '" . $thisquery['products_master'] . "' and p.products_status = '1' and pd.language_id = '" . (int)$languages_id . "'";
then how does it know which products have the current product_id as their products_master field?

Also - in the p.products_master = '" . $thisquery['products_master'] . "' part - is $thisquery literally meaning "this query" as in the $master_sql query - or does it mean that there should be another query somewhere named $this_query ?

It doesn't because I didn't tell you to change $master_query into $master_sql. I suggested to change the $master_sql to something else. There is a second query (see further on, $master_sql) in master_products.php that uses the results of this first query:

$master_query = tep_db_query("select products_master from " . TABLE_PRODUCTS . " where products_master LIKE '%" . $HTTP_GET_VARS['products_id'] . "%'");
$thisquery = tep_db_fetch_array($master_query);
if ($thisquery['products_master'] != '0') {

My suggestion for avoiding adding the table specials in the query is because the table specials has been changed (customer_group_id added). Therefore, if you have a special for retail and the same for wholesale I think it could happen that the same product ends up twice on the page product_info.php because there are two rows for specials with that products_id (haven't tested it though). Since the prices are all corrected for customer groups in master_listing.php the specials price is not the problem, the number of rows returned might be.

Original:

$master_sql = "select  " . $select_column_list . " p.products_id,  p.manufacturers_id, p.products_tax_class_id, s.specials_new_products_price, s.status, p.products_price from ". TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_id = pd.products_id and p.products_master = '" . $thisquery['products_master'] . "' and p.products_status = '1' and pd.language_id = '" . (int)$languages_id . "'";

Suggested revision:

      $master_sql = "select  " . $select_column_list . " p.products_id,  p.manufacturers_id, p.products_tax_class_id, NULL as specials_new_products_price, p.products_price from ". TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id where p.products_id = pd.products_id and p.products_master = '" . $thisquery['products_master'] . "' and p.products_status = '1' and pd.language_id = '" . (int)$languages_id . "'";

I am sure you are going to look at my questions and feel the answers should be staring me in the face :blush: But I had just started learning how to create my own PHP / MySQL when we learned we needed to use osC - so this is my first ever osC website, my first full online shopping site, and quite the big plunge into some advanced PHP and MySQL

Ah, then you haven't read the books yet that I read recently. Need to read them again and hopefully it will make more sense then :D

TracyS · December 21, 2006

Well - aside from adding a couple of little features here and there to our store - it's almost done :D

My last (that I know of) major projects right now are shipping and a minimum order for wholesale customers.

I have a few issues with shipping:

1) - my shipping doesn't seem to be showing up on checkout_confirmation.php or on checkout_payment.php I do have Table Rate installed and have it set to charge shipping based on order subtotal for customers in the US and I do have Display Shipping set to true - but it isn't showing up when I go through and place test orders :huh: This is the only shipping module I have turned on at the moment.

2) I need to have the shipping adjusted according to customer group. There is the ability in the Order Total Modules to set Free Shipping for orders over a certain dollar amount only for US customers. I need to set retail orders to free shipping for orders over $150 and wholesale orders to free shipping on orders over $200 (US only)

3) I then need to try to get the USPS shipping module working for all customers outside of the US with as close to exact cost of shipping as possible.

As for the minimum order - I need to set it up so that retail customers can purchase any dollar amount, but wholesale customers have a minimum order amount of $50. I believe I've seen information on this somewhere - so I'll do a search too and see if I find something :)

In the meantime - do you have any ideas as to where I should start looking to figure out why my shipping information is not showing up during the checkout process?

The only difference between stock osC checkout_shipping.php and mine is I've commented out this line of code as we don't have any virtual products:

//  if ($order->content_type == 'virtual') {
//	if (!tep_session_is_registered('shipping')) tep_session_register('shipping');
//	$shipping = false;
//	$sendto = false;
//	tep_redirect(tep_href_link(FILENAME_CHECKOUT_PAYMENT, '', 'SSL'));
//  }

All of the other checkout_ .php pages are identical to osC stock pages

TracyS · December 21, 2006

It doesn't because I didn't tell you to change $master_query into $master_sql. I suggested to change the $master_sql to something else. There is a second query (see further on, $master_sql) in master_products.php that uses the results of this first query:

My suggestion for avoiding adding the table specials in the query is because the table specials has been changed (customer_group_id added). Therefore, if you have a special for retail and the same for wholesale I think it could happen that the same product ends up twice on the page product_info.php because there are two rows for specials with that products_id (haven't tested it though). Since the prices are all corrected for customer groups in master_listing.php the specials price is not the problem, the number of rows returned might be.

AHHHH!! Silly me - I didn't realize master_sql and master_query were two different things (even though it's quite obvious - LOL) :blush:

Thank you very much for your explanation - it sure does make a whole lot more sense now! <said as the lightbulb turns on over my head> :blush:

I am off to correct that query and make sure everything still functions properly :thumbsup:

TracyS · December 21, 2006

I am off to correct that query and make sure everything still functions properly :thumbsup:

Hi JanZ,

I made the suggested changes to the appropriate query (LOL) and everything is still functioning properly. I did create an identical special for each customer group - and I got the correct results for each group :D So at least now I know that if the other query would have caused a problem in the number of rows returned - this one definitely does not create any problems :thumbsup:

TracyS · December 21, 2006

As for the minimum order - I need to set it up so that retail customers can purchase any dollar amount, but wholesale customers have a minimum order amount of $50. I believe I've seen information on this somewhere - so I'll do a search too and see if I find something :)

YAY! I found some posts further back that walked others through doing this - and I now have the Minimum Order Amount contribution working with SPPC so that my retail customers do not have a minimum order amount, and my wholesale customers have a minimum order amount of $50.

For others who are searching for how to blend both Minimum Order Amount and Separate Price per Customer contrib's - here is the answer in one post :thumbsup:

In the Minimum Order Amount install instructions - on the pages it says to put this line of code:

// check order total minimum
 if ($order->info['subtotal'] < MIN_ORDER_AMOUNT) {
tep_redirect(tep_href_link(FILENAME_SHOPPING_CART, '', 'NONSSL'));
 }

You need to instead do this:

Under:

require('includes/application_top.php');

Put this:

  // BOF Separate Price per Customer
if(!tep_session_is_registered('sppc_customer_group_id')) {
$customer_group_id = '0';
} else {
$customer_group_id = $sppc_customer_group_id;
}
// EOF Separate Price per Customer

Where it tells you to put the if($order->info...... bit of code - place this instead:

// check order total minimum
 if ($customer_group_id != '0' && $order->info['subtotal'] < MIN_ORDER_AMOUNT) {
tep_redirect(tep_href_link(FILENAME_SHOPPING_CART, '', 'NONSSL'));
 }

In the install instructions for shopping_cart.php - change the code you are pasting into this file to this:

<?php
// minimum order total
if ($customer_group_id != '0' && $cart->show_total() < MIN_ORDER_AMOUNT) {
?>
  <tr>
	<td class="stockWarning" align="center"><br><?php echo sprintf(TEXT_ORDER_UNDER_MIN_AMOUNT, $currencies->format(MIN_ORDER_AMOUNT)); ?></td>
  </tr>
<?php
}
// end minimum order total after this closing php tag
?>

  <tr>

The rest of the install instructions for Minimum Order Amount stay the same :)

Now- when you are setting this up - the $customer_group_id != '0' bit of code means, if your customer group id is NOT equal to 0 - for me, my group with id 0 is retail and I needed the minimum order amount only for my wholesale group, which is 1. I could have put $customer_group_id == '1' if, for example, I also had a customer group 2 which I did not need a minimum amount for.

Hope this helps somebody :thumbsup:

Seperate Pricing Per Customer v3.5

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Popular Posts

TracyS

Jan Zonjee

KaoszNagymester

Posted Images

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation