Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Seperate Pricing Per Customer v3.5


scendent

Recommended Posts

I'm having an issue which i haven't been able to find a previous post about.

 

Since installing SPPC 4 (which i believe is supported in this topic) and SPPC Price Break 4.15, my installation now runs painfully slow.

 

After examining my apache logs i found these errors:

 

[Tue Jan 30 14:13:59 2007] [error] [client IP ADDRESS] ModSecurity: Warning. Operator EQ match: 0. [id "960015"] [msg "Request Missing an Accept Header"] [hostname "webserver"] [uri "/E_P_Script"] [unique_id "OTEiDcCoAMcAAAxoAPgAAAD3"]

 

------------------------------------------------------------------------------------------------------------------------

 

[Tue Jan 30 14:12:26 2007] [error] [client 61.88.19.82] ModSecurity: Warning. Match of "rx \\\\b(?:(?:i(?:nterplay|hdr|d3)|(?:(?:ex|jf)i|%pd)f|m(?:ovi|thd)|r(?:ar!|iff)|f(?:lv|ws)|varg|.ra|cws)\\\\b|gif)" against "RESPONSE_BODY" required. [id "970902"] [msg "PHP source code leakage"] [severity "WARNING"] [hostname "URL"] [uri "/index.php"] [unique_id "M54ypMCoAMcAAAxoAO8AAAD4"]

 

------------------------------------------------------------------------------------------------------------------------

 

Sometimes i have it where the page runs ok, then it will slow down. sometimes the pictures on the site will load, othertimes no. im hoping someone can help me!!

Two things. The SPPC Price Break can slow down your site when you start adding products to the shopping basket. This is already changed in the "normal" Price Break contribution but that fix for the SPPC Price Break is not uploaded yet. I wanted to wait till the new version of SPPC is ready, but perhaps I can just upload the upgrade instructions.

 

The log lines you posted seems more like an attack on your server: E_P_Script? The ip-address originates from from a copy shop in Australia.

Link to comment
Share on other sites

Are you sure it is not a problem with includes/modules/product_listing.php? All instances of $listing['field_name'] in there should be replaced with $listing[$x]['field_name'] (after adding the logic to pick-up group prices of course). If not, than no picture, description, price etc.

Thankyou JanZ - you're a genius! :D

Link to comment
Share on other sites

Hi,

 

I have used SPPC for nearly two years. It's working fine. However, I come up with a problem about "product-listing".

 

That's a royal pain. It was for a good reason that tep_get_hide_status was added to the functions because one product might be in several categories. So in a query like this you cannot really join it with products_to_categories and then join again with categories to find out if it is in a hidden category. If the product is in more than one category it will be shown more than once...

 

I have to assign products to different categories. When I do searching for a product with reference to either its product reference or through manufacturer's name, the result page lists the product repeatedly according to the number of categories it belongs to, which means, if an item belongs to 5 categories, it repeatedly appears five times on the result page.

 

I wonder if a hack could fix the problem: by means of showing once ONLY disregarding how many categories the item belongs to.

 

I am not sure if such hack is already available. I'm also not sure if I should raise this question here. Any solution will be appreciated. Thanks in advance.

 

Cheers

 

James

Edited by booksfarm
Link to comment
Share on other sites

Hi there,

 

first of all: Sorry for the long post. I hope to give the most of information that is possible to help to fix my problems fast and well :'(

 

 

I have followed up all those pages to this last page and did not find the solution which fits my problems best.

 

My Provider has upgraded my MySQL 4.x to MySQL 5.0.27-standard almost over night. Lucky me they gave me a 2-day-warning :-)

 

Since then nothing is the same (wonder how often you guys would have heard this).

 

I believe that the problems I got come from running OSCommerce 2.2 with SPPC 4.15 running on MySQL 5

I have searched different forums (means the german forum and this forum) but none of the answers I have found would have fixed my problems.

 

So what are my problems:

(found on http://www.ichbinmobil.com)

 

On listing Products of a manufacturer (index.php, I gues) I get the error:

 

1054 - Unknown column 'p.products_id' in 'on clause'

 

select count(p.products_id) as total from products p, products_description pd, manufacturers m left join specials_retail_prices s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '2' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '3'

 

[TEP STOP]

 

When I search for a product I will get the error (advanced_search_result.php):

 

1066 - Not unique table/alias: 'pd'

 

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id), products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c, products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '2' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%a%' or p.products_model like '%a%' or m.manufacturers_name like '%a%') )

 

[TEP STOP]

 

The installation of oscommerce is 14 days old and I have installed the latest SPPC (Version 4.15 plus Mod; as found on the contribution site).

 

To complete the information I could give, my index.php looks like this:

 

<?php

/*

$Id: index.php,v 1.1 2003/06/11 17:37:59 hpdl Exp $

adapted for Separate Pricing per Customer 2005/02/06

osCommerce, Open Source E-Commerce Solutions

http://www.oscommerce.com

 

Copyright © 2003 osCommerce

 

Released under the GNU General Public License

*/

require('includes/application_top.php');

global $customer_group_id;

if(!isset($customer_group_id)) { $customer_group_id = '0'; }

// the following cPath references come from application_top.php

$category_depth = 'top';

if (isset($cPath) && tep_not_null($cPath)) {

$categories_products_query = tep_db_query("select count(*) as total from " . TABLE_PRODUCTS_TO_CATEGORIES . " where categories_id = '" . (int)$current_category_id . "'");

$cateqories_products = tep_db_fetch_array($categories_products_query);

if ($cateqories_products['total'] > 0) {

$category_depth = 'products'; // display products

} else {

$category_parent_query = tep_db_query("select count(*) as total from " . TABLE_CATEGORIES . " where parent_id = '" . (int)$current_category_id . "'");

$category_parent = tep_db_fetch_array($category_parent_query);

if ($category_parent['total'] > 0) {

$category_depth = 'nested'; // navigate through the categories

} else {

$category_depth = 'products'; // category has no products, but display the 'no products' message

}

}

}

 

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

?>

<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">

<html <?php echo HTML_PARAMS; ?>>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">

<title><?php echo TITLE; ?></title>

<base href="<?php echo (($request_type == 'SSL') ? HTTPS_SERVER : HTTP_SERVER) . DIR_WS_CATALOG; ?>">

<link rel="stylesheet" type="text/css" href="stylesheet.css">

</head>

<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0">

<!-- header //-->

<?php require(DIR_WS_INCLUDES . 'header.php'); ?>

<!-- header_eof //-->

 

<!-- body //-->

<table border="0" width="100%" cellspacing="3" cellpadding="3">

<tr>

<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2">

<!-- left_navigation //-->

<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>

<!-- left_navigation_eof //-->

</table></td>

<!-- body_text //-->

<?php

if ($category_depth == 'nested') {

$category_query = tep_db_query("select cd.categories_name, c.categories_image from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = '" . (int)$current_category_id . "' and cd.categories_id = '" . (int)$current_category_id . "' and cd.language_id = '" . (int)$languages_id . "'");

$category = tep_db_fetch_array($category_query);

?>

<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td class="pageHeading"><?php echo HEADING_TITLE; ?></td>

<td class="pageHeading" align="right"><?php echo tep_image(DIR_WS_IMAGES . $category['categories_image'], $category['categories_name'], HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>

</tr>

</table></td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

</tr>

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="2">

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="2">

<tr>

<?php

if (isset($cPath) && strpos('_', $cPath)) {

// check to see if there are deeper categories within the current category

$category_links = array_reverse($cPath_array);

for($i=0, $n=sizeof($category_links); $i<$n; $i++) {

$categories_query = tep_db_query("select count(*) as total from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$category_links[$i] . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "'");

$categories = tep_db_fetch_array($categories_query);

if ($categories['total'] < 1) {

// do nothing, go through the loop

} else {

$categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$category_links[$i] . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name");

break; // we've found the deepest category the customer is in

}

}

} else {

$categories_query = tep_db_query("select c.categories_id, cd.categories_name, c.categories_image, c.parent_id from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.parent_id = '" . (int)$current_category_id . "' and c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' order by sort_order, cd.categories_name");

}

 

$number_of_categories = tep_db_num_rows($categories_query);

 

$rows = 0;

while ($categories = tep_db_fetch_array($categories_query)) {

$rows++;

$cPath_new = tep_get_path($categories['categories_id']);

$width = (int)(100 / MAX_DISPLAY_CATEGORIES_PER_ROW) . '%';

echo ' <td align="center" class="smallText" width="' . $width . '" valign="top"><a href="' . tep_href_link(FILENAME_DEFAULT, $cPath_new) . '">' . tep_image(DIR_WS_IMAGES . $categories['categories_image'], $categories['categories_name'], SUBCATEGORY_IMAGE_WIDTH, SUBCATEGORY_IMAGE_HEIGHT) . '<br>' . $categories['categories_name'] . '</a></td>' . "\n";

if ((($rows / MAX_DISPLAY_CATEGORIES_PER_ROW) == floor($rows / MAX_DISPLAY_CATEGORIES_PER_ROW)) && ($rows != $number_of_categories)) {

echo ' </tr>' . "\n";

echo ' <tr>' . "\n";

}

}

 

// needed for the new products module shown below

$new_products_category_id = $current_category_id;

?>

</tr>

</table></td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

</tr>

<tr>

<td><?php include(DIR_WS_MODULES . FILENAME_NEW_PRODUCTS); ?></td>

</tr>

</table></td>

</tr>

</table></td>

<?php

} elseif ($category_depth == 'products' || isset($HTTP_GET_VARS['manufacturers_id'])) {

// create column list

$define_list = array('PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL,

'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME,

'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER,

'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE,

'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY,

'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT,

'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE,

'PRODUCT_LIST_BUY_NOW' => PRODUCT_LIST_BUY_NOW);

 

asort($define_list);

 

$column_list = array();

reset($define_list);

while (list($key, $value) = each($define_list)) {

if ($value > 0) $column_list[] = $key;

}

 

// BOF Separate Pricing Per Customer

if(!tep_session_is_registered('sppc_customer_group_id')) {

$customer_group_id = '0';

} else {

$customer_group_id = $sppc_customer_group_id;

}

// this will build the table with specials prices for the retail group or update it if needed

// this function should have been added to includes/functions/database.php

if ($customer_group_id == '0') {

tep_db_check_age_specials_retail_table();

}

$status_product_prices_table = false;

$status_need_to_get_prices = false;

 

// find out if sorting by price has been requested

if ( (isset($HTTP_GET_VARS['sort'])) && (ereg('[1-8][ad]', $HTTP_GET_VARS['sort'])) && (substr($HTTP_GET_VARS['sort'], 0, 1) <= sizeof($column_list)) && $customer_group_id != '0' ){

$_sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1);

if ($column_list[$_sort_col-1] == 'PRODUCT_LIST_PRICE') {

$status_need_to_get_prices = true;

}

}

 

if ($status_need_to_get_prices == true && $customer_group_id != '0') {

$product_prices_table = TABLE_PRODUCTS_GROUP_PRICES.$customer_group_id;

// the table with product prices for a particular customer group is re-built only a number of times per hour

// (setting in /includes/database_tables.php called MAXIMUM_DELAY_UPDATE_PG_PRICES_TABLE, in minutes)

// to trigger the update the next function is called (new function that should have been

// added to includes/functions/database.php)

tep_db_check_age_products_group_prices_cg_table($customer_group_id);

$status_product_prices_table = true;

 

} // end if ($status_need_to_get_prices == true && $customer_group_id != '0')

// EOF Separate Pricing Per Customer

 

$select_column_list = '';

 

for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {

switch ($column_list[$i]) {

case 'PRODUCT_LIST_MODEL':

$select_column_list .= 'p.products_model, ';

break;

case 'PRODUCT_LIST_NAME':

$select_column_list .= 'pd.products_name, ';

break;

case 'PRODUCT_LIST_MANUFACTURER':

$select_column_list .= 'm.manufacturers_name, ';

break;

case 'PRODUCT_LIST_QUANTITY':

$select_column_list .= 'p.products_quantity, ';

break;

case 'PRODUCT_LIST_IMAGE':

$select_column_list .= 'p.products_image, ';

break;

case 'PRODUCT_LIST_WEIGHT':

$select_column_list .= 'p.products_weight, ';

break;

}

}

 

// show the products of a specified manufacturer

if (isset($HTTP_GET_VARS['manufacturers_id'])) {

if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {

// We are asked to show only a specific category

// BOF Separate Pricing Per Customer

if ($status_product_prices_table == true) {

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, tmp_pp.products_price, p.products_tax_class_id, IF(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . $product_prices_table . " as tmp_pp using(products_id), " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";

} else { // either retail or no need to get correct special prices

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";

} // end else { // either retail...

// EOF Separate Pricing Per Customer

} else {

// We show them all

// BOF Separate Pricing Per Customer

if ($status_product_prices_table == true) {

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, tmp_pp.products_price, p.products_tax_class_id, IF(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . $product_prices_table . " as tmp_pp using(products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

} else { // either retail or no need to get correct special prices

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

} // end else { // either retail...

// EOF Separate Pricing Per Customer

}

} else {

// show the products in a given categorie

if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {

// We are asked to show only specific catgeory;

// BOF Separate Pricing Per Customer

if ($status_product_prices_table == true) {

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, tmp_pp.products_price, p.products_tax_class_id, IF(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . $product_prices_table . " as tmp_pp using(products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

} else { // either retail or no need to get correct special prices

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS_RETAIL_PRICES . " s using(products_id) where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

} // end else { // either retail...

// EOF Separate Pricing Per Customer

} else {

// We show them all

// BOF Separate Pricing Per Customer

if ($status_product_prices_table == true) {

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, tmp_pp.products_price, p.products_tax_class_id, IF(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . $product_prices_table . " as tmp_pp using(products_id) left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS . " p" . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

} else { // either retail or no need to get correct special prices

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_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_RETAIL_PRICES . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

} // end else { // either retail...

// EOF Separate Pricing 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] == 'PRODUCT_LIST_NAME') {

$HTTP_GET_VARS['sort'] = $i+1 . 'a';

$listing_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);

$listing_sql .= ' order by ';

switch ($column_list[$sort_col-1]) {

case 'PRODUCT_LIST_MODEL':

$listing_sql .= "p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";

break;

case 'PRODUCT_LIST_NAME':

$listing_sql .= "pd.products_name " . ($sort_order == 'd' ? 'desc' : '');

break;

case 'PRODUCT_LIST_MANUFACTURER':

$listing_sql .= "m.manufacturers_name " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";

break;

case 'PRODUCT_LIST_QUANTITY':

$listing_sql .= "p.products_quantity " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";

break;

case 'PRODUCT_LIST_IMAGE':

$listing_sql .= "pd.products_name";

break;

case 'PRODUCT_LIST_WEIGHT':

$listing_sql .= "p.products_weight " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";

break;

case 'PRODUCT_LIST_PRICE':

$listing_sql .= "final_price " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";

break;

}

}

?>

<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td class="pageHeading"><?php echo HEADING_TITLE; ?></td>

<?php

// optional Product List Filter

if (PRODUCT_LIST_FILTER > 0) {

if (isset($HTTP_GET_VARS['manufacturers_id'])) {

$filterlist_sql = "select distinct c.categories_id as id, cd.categories_name as name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where p.products_status = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p2c.categories_id = cd.categories_id and cd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' order by cd.categories_name";

} else {

$filterlist_sql= "select distinct m.manufacturers_id as id, m.manufacturers_name as name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and p.products_id = p2c.products_id and p2c.categories_id = '" . (int)$current_category_id . "' order by m.manufacturers_name";

}

$filterlist_query = tep_db_query($filterlist_sql);

if (tep_db_num_rows($filterlist_query) > 1) {

echo ' <td align="center" class="main">' . tep_draw_form('filter', FILENAME_DEFAULT, 'get') . TEXT_SHOW . ' ';

if (isset($HTTP_GET_VARS['manufacturers_id'])) {

echo tep_draw_hidden_field('manufacturers_id', $HTTP_GET_VARS['manufacturers_id']);

$options = array(array('id' => '', 'text' => TEXT_ALL_CATEGORIES));

} else {

echo tep_draw_hidden_field('cPath', $cPath);

$options = array(array('id' => '', 'text' => TEXT_ALL_MANUFACTURERS));

}

echo tep_draw_hidden_field('sort', $HTTP_GET_VARS['sort']);

while ($filterlist = tep_db_fetch_array($filterlist_query)) {

$options[] = array('id' => $filterlist['id'], 'text' => $filterlist['name']);

}

echo tep_draw_pull_down_menu('filter_id', $options, (isset($HTTP_GET_VARS['filter_id']) ? $HTTP_GET_VARS['filter_id'] : ''), 'onchange="this.form.submit()"');

echo '</form></td>' . "\n";

}

}

 

// Get the right image for the top-right

$image = DIR_WS_IMAGES . 'table_background_list.gif';

if (isset($HTTP_GET_VARS['manufacturers_id'])) {

$image = tep_db_query("select manufacturers_image from " . TABLE_MANUFACTURERS . " where manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'");

$image = tep_db_fetch_array($image);

$image = $image['manufacturers_image'];

} elseif ($current_category_id) {

$image = tep_db_query("select categories_image from " . TABLE_CATEGORIES . " where categories_id = '" . (int)$current_category_id . "'");

$image = tep_db_fetch_array($image);

$image = $image['categories_image'];

}

?>

<td align="right"><?php echo tep_image(DIR_WS_IMAGES . $image, HEADING_TITLE, HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>

</tr>

</table></td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

</tr>

<tr>

<td><?php include(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING); ?></td>

</tr>

</table></td>

<?php

} else { // default page

?>

<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td class="pageHeading"><?php echo HEADING_TITLE; ?></td>

<td class="pageHeading" align="right"><?php echo tep_image(DIR_WS_IMAGES . 'table_background_default.gif', HEADING_TITLE, HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>

</tr>

</table></td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

</tr>

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td class="main"><?php echo tep_customer_greeting(); ?></td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

</tr>

<tr>

<td class="main"><?php echo TEXT_MAIN; ?></td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

</tr>

<tr>

<td><?php include(DIR_WS_MODULES . FILENAME_NEW_PRODUCTS); ?></td>

</tr>

<?php

include(DIR_WS_MODULES . FILENAME_UPCOMING_PRODUCTS);

?>

</table></td>

</tr>

</table></td>

<?php

}

?>

<!-- body_text_eof //-->

<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2">

<!-- right_navigation //-->

<?php require(DIR_WS_INCLUDES . 'column_right.php'); ?>

<!-- right_navigation_eof //-->

</table></td>

</tr>

</table>

<!-- body_eof //-->

 

<!-- footer //-->

<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>

<!-- footer_eof //-->

<br>

</body>

</html>

<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

 

and my advanced_search_results.php looks like this:

 

<?php

/*

$Id: advanced_search_result.php,v 1.72 2003/06/23 06:50:11 project3000 Exp $

adapted for Separate Pricing Per Customer 2005/02/06

 

osCommerce, Open Source E-Commerce Solutions

http://www.oscommerce.com

 

Copyright © 2003 osCommerce

 

Released under the GNU General Public License

*/

 

require('includes/application_top.php');

 

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

 

$error = false;

 

if ( (isset($HTTP_GET_VARS['keywords']) && empty($HTTP_GET_VARS['keywords'])) &&

(isset($HTTP_GET_VARS['dfrom']) && (empty($HTTP_GET_VARS['dfrom']) || ($HTTP_GET_VARS['dfrom'] == DOB_FORMAT_STRING))) &&

(isset($HTTP_GET_VARS['dto']) && (empty($HTTP_GET_VARS['dto']) || ($HTTP_GET_VARS['dto'] == DOB_FORMAT_STRING))) &&

(isset($HTTP_GET_VARS['pfrom']) && !is_numeric($HTTP_GET_VARS['pfrom'])) &&

(isset($HTTP_GET_VARS['pto']) && !is_numeric($HTTP_GET_VARS['pto'])) ) {

$error = true;

 

$messageStack->add_session('search', ERROR_AT_LEAST_ONE_INPUT);

} else {

$dfrom = '';

$dto = '';

$pfrom = '';

$pto = '';

$keywords = '';

 

if (isset($HTTP_GET_VARS['dfrom'])) {

$dfrom = (($HTTP_GET_VARS['dfrom'] == DOB_FORMAT_STRING) ? '' : $HTTP_GET_VARS['dfrom']);

}

 

if (isset($HTTP_GET_VARS['dto'])) {

$dto = (($HTTP_GET_VARS['dto'] == DOB_FORMAT_STRING) ? '' : $HTTP_GET_VARS['dto']);

}

 

if (isset($HTTP_GET_VARS['pfrom'])) {

$pfrom = $HTTP_GET_VARS['pfrom'];

}

 

if (isset($HTTP_GET_VARS['pto'])) {

$pto = $HTTP_GET_VARS['pto'];

}

 

if (isset($HTTP_GET_VARS['keywords'])) {

$keywords = $HTTP_GET_VARS['keywords'];

}

 

$date_check_error = false;

if (tep_not_null($dfrom)) {

if (!tep_checkdate($dfrom, DOB_FORMAT_STRING, $dfrom_array)) {

$error = true;

$date_check_error = true;

 

$messageStack->add_session('search', ERROR_INVALID_FROM_DATE);

}

}

 

if (tep_not_null($dto)) {

if (!tep_checkdate($dto, DOB_FORMAT_STRING, $dto_array)) {

$error = true;

$date_check_error = true;

 

$messageStack->add_session('search', ERROR_INVALID_TO_DATE);

}

}

 

if (($date_check_error == false) && tep_not_null($dfrom) && tep_not_null($dto)) {

if (mktime(0, 0, 0, $dfrom_array[1], $dfrom_array[2], $dfrom_array[0]) > mktime(0, 0, 0, $dto_array[1], $dto_array[2], $dto_array[0])) {

$error = true;

 

$messageStack->add_session('search', ERROR_TO_DATE_LESS_THAN_FROM_DATE);

}

}

 

$price_check_error = false;

if (tep_not_null($pfrom)) {

if (!settype($pfrom, 'double')) {

$error = true;

$price_check_error = true;

 

$messageStack->add_session('search', ERROR_PRICE_FROM_MUST_BE_NUM);

}

}

 

if (tep_not_null($pto)) {

if (!settype($pto, 'double')) {

$error = true;

$price_check_error = true;

 

$messageStack->add_session('search', ERROR_PRICE_TO_MUST_BE_NUM);

}

}

 

if (($price_check_error == false) && is_float($pfrom) && is_float($pto)) {

if ($pfrom >= $pto) {

$error = true;

 

$messageStack->add_session('search', ERROR_PRICE_TO_LESS_THAN_PRICE_FROM);

}

}

 

if (tep_not_null($keywords)) {

if (!tep_parse_search_string($keywords, $search_keywords)) {

$error = true;

 

$messageStack->add_session('search', ERROR_INVALID_KEYWORDS);

}

}

}

 

if (empty($dfrom) && empty($dto) && empty($pfrom) && empty($pto) && empty($keywords)) {

$error = true;

 

$messageStack->add_session('search', ERROR_AT_LEAST_ONE_INPUT);

}

 

if ($error == true) {

tep_redirect(tep_href_link(FILENAME_ADVANCED_SEARCH, tep_get_all_get_params(), 'NONSSL', true, false));

}

 

$breadcrumb->add(NAVBAR_TITLE_1, tep_href_link(FILENAME_ADVANCED_SEARCH));

$breadcrumb->add(NAVBAR_TITLE_2, tep_href_link(FILENAME_ADVANCED_SEARCH_RESULT, tep_get_all_get_params(), 'NONSSL', true, false));

?>

<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">

<html <?php echo HTML_PARAMS; ?>>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">

<base href="<?php echo (($request_type == 'SSL') ? HTTPS_SERVER : HTTP_SERVER) . DIR_WS_CATALOG; ?>">

<title><?php echo TITLE; ?></title>

<link rel="stylesheet" type="text/css" href="stylesheet.css">

</head>

<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0">

<!-- header //-->

<?php require(DIR_WS_INCLUDES . 'header.php'); ?>

<!-- header_eof //-->

 

<!-- body //-->

<table border="0" width="100%" cellspacing="3" cellpadding="3">

<tr>

<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2">

<!-- left_navigation //-->

<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>

<!-- left_navigation_eof //-->

</table></td>

<!-- body_text //-->

<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td class="pageHeading"><?php echo HEADING_TITLE_2; ?></td>

<td class="pageHeading" align="right"><?php echo tep_image(DIR_WS_IMAGES . 'table_background_browse.gif', HEADING_TITLE_2, HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>

</tr>

</table></td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

</tr>

<tr>

<td>

<?php

// create column list

$define_list = array('PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL,

'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME,

'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER,

'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE,

'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY,

'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT,

'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE,

'PRODUCT_LIST_BUY_NOW' => PRODUCT_LIST_BUY_NOW);

 

asort($define_list);

 

$column_list = array();

reset($define_list);

while (list($key, $value) = each($define_list)) {

if ($value > 0) $column_list[] = $key;

}

// BOF Separate Pricing 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 Pricing Per Customer

 

$select_column_list = '';

 

for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {

switch ($column_list[$i]) {

case 'PRODUCT_LIST_MODEL':

$select_column_list .= 'p.products_model, ';

break;

case 'PRODUCT_LIST_MANUFACTURER':

$select_column_list .= 'm.manufacturers_name, ';

break;

case 'PRODUCT_LIST_QUANTITY':

$select_column_list .= 'p.products_quantity, ';

break;

case 'PRODUCT_LIST_IMAGE':

$select_column_list .= 'p.products_image, ';

break;

case 'PRODUCT_LIST_WEIGHT':

$select_column_list .= 'p.products_weight, ';

break;

}

}

 

// $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price ";

//

// if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {

// $select_str .= ", SUM(tr.tax_rate) as tax_rate ";

// }

//

// $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id";

// BOF Separate Pricing Per Customer

$status_tmp_product_prices_table = false;

$status_need_to_get_prices = false;

// find out if sorting by price has been requested

if ( (isset($HTTP_GET_VARS['sort'])) && (ereg('[1-8][ad]', $HTTP_GET_VARS['sort'])) && (substr($HTTP_GET_VARS['sort'], 0, 1) <= sizeof($column_list)) ){

$_sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1);

if ($column_list[$_sort_col-1] == 'PRODUCT_LIST_PRICE') {

$status_need_to_get_prices = true;

}

}

 

if ((tep_not_null($pfrom) || tep_not_null($pto) || $status_need_to_get_prices == true) && $customer_group_id != '0') {

$product_prices_table = TABLE_PRODUCTS_GROUP_PRICES.$customer_group_id;

// the table with product prices for a particular customer group is re-built only a number of times per hour

// (setting in /includes/database_tables.php called MAXIMUM_DELAY_UPDATE_PG_PRICES_TABLE, in minutes)

// to trigger the update the next function is called (new function that should have been

// added to includes/functions/database.php)

tep_db_check_age_products_group_prices_cg_table($customer_group_id);

$status_tmp_product_prices_table = true;

} elseif ((tep_not_null($pfrom) || tep_not_null($pto) || $status_need_to_get_prices == true) && $customer_group_id == '0') {

// to be able to sort on retail prices we *need* to get the special prices instead of leaving them

// NULL and do product_listing the job of getting the special price

// first make sure that table exists and needs no updating

tep_db_check_age_specials_retail_table();

$status_tmp_special_prices_table = true;

} // end elseif ((tep_not_null($pfrom) || (tep_not_null($pfrom)) && ....

 

if ($status_tmp_product_prices_table == true) {

$select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, tmp_pp.products_price, p.products_tax_class_id, if(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price ";

} elseif ($status_tmp_special_prices_table == true) {

$select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, if(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, if(s.status, s.specials_new_products_price, p.products_price) as final_price ";

} else {

$select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, NULL as specials_new_products_price, NULL as final_price ";

}

// next line original select query

// $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price ";

 

 

if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {

$select_str .= ", SUM(tr.tax_rate) as tax_rate ";

}

 

if ($status_tmp_product_prices_table == true) {

$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . $product_prices_table . " as tmp_pp using(products_id), " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

} elseif ($status_tmp_special_prices_table == true) {

$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id , " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

} else {

$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

}

// 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 ";

 

if (isset($HTTP_GET_VARS['categories_id']) && tep_not_null($HTTP_GET_VARS['categories_id'])) {

if (isset($HTTP_GET_VARS['inc_subcat']) && ($HTTP_GET_VARS['inc_subcat'] == '1')) {

$subcategories_array = array();

tep_get_subcategories($subcategories_array, $HTTP_GET_VARS['categories_id']);

 

$where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and (p2c.categories_id = '" . (int)$HTTP_GET_VARS['categories_id'] . "'";

 

for ($i=0, $n=sizeof($subcategories_array); $i<$n; $i++ ) {

$where_str .= " or p2c.categories_id = '" . (int)$subcategories_array[$i] . "'";

}

 

$where_str .= ")";

} else {

$where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['categories_id'] . "'";

}

}

 

if (isset($HTTP_GET_VARS['manufacturers_id']) && tep_not_null($HTTP_GET_VARS['manufacturers_id'])) {

$where_str .= " and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

}

 

if (isset($search_keywords) && (sizeof($search_keywords) > 0)) {

$where_str .= " and (";

for ($i=0, $n=sizeof($search_keywords); $i<$n; $i++ ) {

switch ($search_keywords[$i]) {

case '(':

case ')':

case 'and':

case 'or':

$where_str .= " " . $search_keywords[$i] . " ";

break;

default:

$keyword = tep_db_prepare_input($search_keywords[$i]);

$where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%'";

if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description like '%" . tep_db_input($keyword) . "%'";

$where_str .= ')';

break;

}

}

$where_str .= " )";

}

 

if (tep_not_null($dfrom)) {

$where_str .= " and p.products_date_added >= '" . tep_date_raw($dfrom) . "'";

}

 

if (tep_not_null($dto)) {

$where_str .= " and p.products_date_added <= '" . tep_date_raw($dto) . "'";

}

 

if (tep_not_null($pfrom)) {

if ($currencies->is_set($currency)) {

$rate = $currencies->get_value($currency);

 

$pfrom = $pfrom / $rate;

}

}

 

if (tep_not_null($pto)) {

if (isset($rate)) {

$pto = $pto / $rate;

}

}

 

// if (DISPLAY_PRICE_WITH_TAX == 'true') {

// if ($pfrom > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) >= " . (double)$pfrom . ")";

// if ($pto > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) <= " . (double)$pto . ")";

// } else {

// if ($pfrom > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) >= " . (double)$pfrom . ")";

// if ($pto > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) <= " . (double)$pto . ")";

// }

// BOF Separate Pricing Per Customer

if ($status_tmp_product_prices_table == true) {

if (DISPLAY_PRICE_WITH_TAX == 'true') {

if ($pfrom > 0) $where_str .= " and (IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) >= " . (double)$pfrom . ")";

if ($pto > 0) $where_str .= " and (IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) <= " . (double)$pto . ")";

} else {

if ($pfrom > 0) $where_str .= " and (IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) >= " . (double)$pfrom . ")";

if ($pto > 0) $where_str .= " and (IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) <= " . (double)$pto . ")";

}

} else { // $status_tmp_product_prices_table is not true: uses p.products_price instead of cg_products_price

// because in the where clause for the case $status_tmp_special_prices is true, the table

// specials_retail_prices is abbreviated with "s" also we can use the same code for "true" and for "false"

if (DISPLAY_PRICE_WITH_TAX == 'true') {

if ($pfrom > 0) $where_str .= " and (IF(s.status AND s.customers_group_id = '" . $customer_group_id . "', s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) >= " . (double)$pfrom . ")";

if ($pto > 0) $where_str .= " and (IF(s.status AND s.customers_group_id = '" . $customer_group_id . "', s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) <= " . (double)$pto . ")";

} else {

if ($pfrom > 0) $where_str .= " and (IF(s.status AND s.customers_group_id = '" . $customer_group_id . "', s.specials_new_products_price, p.products_price) >= " . (double)$pfrom . ")";

if ($pto > 0) $where_str .= " and (IF(s.status AND s.customers_group_id = '" . $customer_group_id . "', s.specials_new_products_price, p.products_price) <= " . (double)$pto . ")";

}

}

// EOF Separate Pricing Per Customer

 

if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {

$where_str .= " group by p.products_id, tr.tax_priority";

}

 

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] == 'PRODUCT_LIST_NAME') {

$HTTP_GET_VARS['sort'] = $i+1 . 'a';

$order_str = ' order by pd.products_name';

break;

}

}

} else {

$sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1);

$sort_order = substr($HTTP_GET_VARS['sort'], 1);

$order_str = ' order by ';

switch ($column_list[$sort_col-1]) {

case 'PRODUCT_LIST_MODEL':

$order_str .= "p.products_model " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";

break;

case 'PRODUCT_LIST_NAME':

$order_str .= "pd.products_name " . ($sort_order == 'd' ? "desc" : "");

break;

case 'PRODUCT_LIST_MANUFACTURER':

$order_str .= "m.manufacturers_name " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";

break;

case 'PRODUCT_LIST_QUANTITY':

$order_str .= "p.products_quantity " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";

break;

case 'PRODUCT_LIST_IMAGE':

$order_str .= "pd.products_name";

break;

case 'PRODUCT_LIST_WEIGHT':

$order_str .= "p.products_weight " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";

break;

case 'PRODUCT_LIST_PRICE':

$order_str .= "final_price " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";

break;

}

}

 

$listing_sql = $select_str . $from_str . $where_str . $order_str;

 

require(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING);

?>

</td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

</tr>

<tr>

<td class="main"><?php echo '<a href="' . tep_href_link(FILENAME_ADVANCED_SEARCH, tep_get_all_get_params(array('sort', 'page')), 'NONSSL', true, false) . '">' . tep_image_button('button_back.gif', IMAGE_BUTTON_BACK) . '</a>'; ?></td>

</tr>

Edited by yofresh
Link to comment
Share on other sites

Seems like something's cut my post :(

 

 

and my advanced_search_results.php looks like this:

 

<?php

/*

$Id: advanced_search_result.php,v 1.72 2003/06/23 06:50:11 project3000 Exp $

adapted for Separate Pricing Per Customer 2005/02/06

 

osCommerce, Open Source E-Commerce Solutions

http://www.oscommerce.com

 

Copyright © 2003 osCommerce

 

Released under the GNU General Public License

*/

 

require('includes/application_top.php');

 

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

 

$error = false;

 

if ( (isset($HTTP_GET_VARS['keywords']) && empty($HTTP_GET_VARS['keywords'])) &&

(isset($HTTP_GET_VARS['dfrom']) && (empty($HTTP_GET_VARS['dfrom']) || ($HTTP_GET_VARS['dfrom'] == DOB_FORMAT_STRING))) &&

(isset($HTTP_GET_VARS['dto']) && (empty($HTTP_GET_VARS['dto']) || ($HTTP_GET_VARS['dto'] == DOB_FORMAT_STRING))) &&

(isset($HTTP_GET_VARS['pfrom']) && !is_numeric($HTTP_GET_VARS['pfrom'])) &&

(isset($HTTP_GET_VARS['pto']) && !is_numeric($HTTP_GET_VARS['pto'])) ) {

$error = true;

 

$messageStack->add_session('search', ERROR_AT_LEAST_ONE_INPUT);

} else {

$dfrom = '';

$dto = '';

$pfrom = '';

$pto = '';

$keywords = '';

 

if (isset($HTTP_GET_VARS['dfrom'])) {

$dfrom = (($HTTP_GET_VARS['dfrom'] == DOB_FORMAT_STRING) ? '' : $HTTP_GET_VARS['dfrom']);

}

 

if (isset($HTTP_GET_VARS['dto'])) {

$dto = (($HTTP_GET_VARS['dto'] == DOB_FORMAT_STRING) ? '' : $HTTP_GET_VARS['dto']);

}

 

if (isset($HTTP_GET_VARS['pfrom'])) {

$pfrom = $HTTP_GET_VARS['pfrom'];

}

 

if (isset($HTTP_GET_VARS['pto'])) {

$pto = $HTTP_GET_VARS['pto'];

}

 

if (isset($HTTP_GET_VARS['keywords'])) {

$keywords = $HTTP_GET_VARS['keywords'];

}

 

$date_check_error = false;

if (tep_not_null($dfrom)) {

if (!tep_checkdate($dfrom, DOB_FORMAT_STRING, $dfrom_array)) {

$error = true;

$date_check_error = true;

 

$messageStack->add_session('search', ERROR_INVALID_FROM_DATE);

}

}

 

if (tep_not_null($dto)) {

if (!tep_checkdate($dto, DOB_FORMAT_STRING, $dto_array)) {

$error = true;

$date_check_error = true;

 

$messageStack->add_session('search', ERROR_INVALID_TO_DATE);

}

}

 

if (($date_check_error == false) && tep_not_null($dfrom) && tep_not_null($dto)) {

if (mktime(0, 0, 0, $dfrom_array[1], $dfrom_array[2], $dfrom_array[0]) > mktime(0, 0, 0, $dto_array[1], $dto_array[2], $dto_array[0])) {

$error = true;

 

$messageStack->add_session('search', ERROR_TO_DATE_LESS_THAN_FROM_DATE);

}

}

 

$price_check_error = false;

if (tep_not_null($pfrom)) {

if (!settype($pfrom, 'double')) {

$error = true;

$price_check_error = true;

 

$messageStack->add_session('search', ERROR_PRICE_FROM_MUST_BE_NUM);

}

}

 

if (tep_not_null($pto)) {

if (!settype($pto, 'double')) {

$error = true;

$price_check_error = true;

 

$messageStack->add_session('search', ERROR_PRICE_TO_MUST_BE_NUM);

}

}

 

if (($price_check_error == false) && is_float($pfrom) && is_float($pto)) {

if ($pfrom >= $pto) {

$error = true;

 

$messageStack->add_session('search', ERROR_PRICE_TO_LESS_THAN_PRICE_FROM);

}

}

 

if (tep_not_null($keywords)) {

if (!tep_parse_search_string($keywords, $search_keywords)) {

$error = true;

 

$messageStack->add_session('search', ERROR_INVALID_KEYWORDS);

}

}

}

 

if (empty($dfrom) && empty($dto) && empty($pfrom) && empty($pto) && empty($keywords)) {

$error = true;

 

$messageStack->add_session('search', ERROR_AT_LEAST_ONE_INPUT);

}

 

if ($error == true) {

tep_redirect(tep_href_link(FILENAME_ADVANCED_SEARCH, tep_get_all_get_params(), 'NONSSL', true, false));

}

 

$breadcrumb->add(NAVBAR_TITLE_1, tep_href_link(FILENAME_ADVANCED_SEARCH));

$breadcrumb->add(NAVBAR_TITLE_2, tep_href_link(FILENAME_ADVANCED_SEARCH_RESULT, tep_get_all_get_params(), 'NONSSL', true, false));

?>

<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">

<html <?php echo HTML_PARAMS; ?>>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=<?php echo CHARSET; ?>">

<base href="<?php echo (($request_type == 'SSL') ? HTTPS_SERVER : HTTP_SERVER) . DIR_WS_CATALOG; ?>">

<title><?php echo TITLE; ?></title>

<link rel="stylesheet" type="text/css" href="stylesheet.css">

</head>

<body marginwidth="0" marginheight="0" topmargin="0" bottommargin="0" leftmargin="0" rightmargin="0">

<!-- header //-->

<?php require(DIR_WS_INCLUDES . 'header.php'); ?>

<!-- header_eof //-->

 

<!-- body //-->

<table border="0" width="100%" cellspacing="3" cellpadding="3">

<tr>

<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2">

<!-- left_navigation //-->

<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>

<!-- left_navigation_eof //-->

</table></td>

<!-- body_text //-->

<td width="100%" valign="top"><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td><table border="0" width="100%" cellspacing="0" cellpadding="0">

<tr>

<td class="pageHeading"><?php echo HEADING_TITLE_2; ?></td>

<td class="pageHeading" align="right"><?php echo tep_image(DIR_WS_IMAGES . 'table_background_browse.gif', HEADING_TITLE_2, HEADING_IMAGE_WIDTH, HEADING_IMAGE_HEIGHT); ?></td>

</tr>

</table></td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

</tr>

<tr>

<td>

<?php

// create column list

$define_list = array('PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL,

'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME,

'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER,

'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE,

'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY,

'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT,

'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE,

'PRODUCT_LIST_BUY_NOW' => PRODUCT_LIST_BUY_NOW);

 

asort($define_list);

 

$column_list = array();

reset($define_list);

while (list($key, $value) = each($define_list)) {

if ($value > 0) $column_list[] = $key;

}

// BOF Separate Pricing 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 Pricing Per Customer

 

$select_column_list = '';

 

for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {

switch ($column_list[$i]) {

case 'PRODUCT_LIST_MODEL':

$select_column_list .= 'p.products_model, ';

break;

case 'PRODUCT_LIST_MANUFACTURER':

$select_column_list .= 'm.manufacturers_name, ';

break;

case 'PRODUCT_LIST_QUANTITY':

$select_column_list .= 'p.products_quantity, ';

break;

case 'PRODUCT_LIST_IMAGE':

$select_column_list .= 'p.products_image, ';

break;

case 'PRODUCT_LIST_WEIGHT':

$select_column_list .= 'p.products_weight, ';

break;

}

}

 

// $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price ";

//

// if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {

// $select_str .= ", SUM(tr.tax_rate) as tax_rate ";

// }

//

// $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id";

// BOF Separate Pricing Per Customer

$status_tmp_product_prices_table = false;

$status_need_to_get_prices = false;

// find out if sorting by price has been requested

if ( (isset($HTTP_GET_VARS['sort'])) && (ereg('[1-8][ad]', $HTTP_GET_VARS['sort'])) && (substr($HTTP_GET_VARS['sort'], 0, 1) <= sizeof($column_list)) ){

$_sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1);

if ($column_list[$_sort_col-1] == 'PRODUCT_LIST_PRICE') {

$status_need_to_get_prices = true;

}

}

 

if ((tep_not_null($pfrom) || tep_not_null($pto) || $status_need_to_get_prices == true) && $customer_group_id != '0') {

$product_prices_table = TABLE_PRODUCTS_GROUP_PRICES.$customer_group_id;

// the table with product prices for a particular customer group is re-built only a number of times per hour

// (setting in /includes/database_tables.php called MAXIMUM_DELAY_UPDATE_PG_PRICES_TABLE, in minutes)

// to trigger the update the next function is called (new function that should have been

// added to includes/functions/database.php)

tep_db_check_age_products_group_prices_cg_table($customer_group_id);

$status_tmp_product_prices_table = true;

} elseif ((tep_not_null($pfrom) || tep_not_null($pto) || $status_need_to_get_prices == true) && $customer_group_id == '0') {

// to be able to sort on retail prices we *need* to get the special prices instead of leaving them

// NULL and do product_listing the job of getting the special price

// first make sure that table exists and needs no updating

tep_db_check_age_specials_retail_table();

$status_tmp_special_prices_table = true;

} // end elseif ((tep_not_null($pfrom) || (tep_not_null($pfrom)) && ....

 

if ($status_tmp_product_prices_table == true) {

$select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, tmp_pp.products_price, p.products_tax_class_id, if(tmp_pp.status, tmp_pp.specials_new_products_price, NULL) as specials_new_products_price, IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) as final_price ";

} elseif ($status_tmp_special_prices_table == true) {

$select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, if(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, if(s.status, s.specials_new_products_price, p.products_price) as final_price ";

} else {

$select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, NULL as specials_new_products_price, NULL as final_price ";

}

// next line original select query

// $select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price ";

 

 

if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {

$select_str .= ", SUM(tr.tax_rate) as tax_rate ";

}

 

if ($status_tmp_product_prices_table == true) {

$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . $product_prices_table . " as tmp_pp using(products_id), " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

} elseif ($status_tmp_special_prices_table == true) {

$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS_RETAIL_PRICES . " s on p.products_id = s.products_id , " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

} else {

$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

}

// 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 ";

 

if (isset($HTTP_GET_VARS['categories_id']) && tep_not_null($HTTP_GET_VARS['categories_id'])) {

if (isset($HTTP_GET_VARS['inc_subcat']) && ($HTTP_GET_VARS['inc_subcat'] == '1')) {

$subcategories_array = array();

tep_get_subcategories($subcategories_array, $HTTP_GET_VARS['categories_id']);

 

$where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and (p2c.categories_id = '" . (int)$HTTP_GET_VARS['categories_id'] . "'";

 

for ($i=0, $n=sizeof($subcategories_array); $i<$n; $i++ ) {

$where_str .= " or p2c.categories_id = '" . (int)$subcategories_array[$i] . "'";

}

 

$where_str .= ")";

} else {

$where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['categories_id'] . "'";

}

}

 

if (isset($HTTP_GET_VARS['manufacturers_id']) && tep_not_null($HTTP_GET_VARS['manufacturers_id'])) {

$where_str .= " and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

}

 

if (isset($search_keywords) && (sizeof($search_keywords) > 0)) {

$where_str .= " and (";

for ($i=0, $n=sizeof($search_keywords); $i<$n; $i++ ) {

switch ($search_keywords[$i]) {

case '(':

case ')':

case 'and':

case 'or':

$where_str .= " " . $search_keywords[$i] . " ";

break;

default:

$keyword = tep_db_prepare_input($search_keywords[$i]);

$where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%'";

if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description like '%" . tep_db_input($keyword) . "%'";

$where_str .= ')';

break;

}

}

$where_str .= " )";

}

 

if (tep_not_null($dfrom)) {

$where_str .= " and p.products_date_added >= '" . tep_date_raw($dfrom) . "'";

}

 

if (tep_not_null($dto)) {

$where_str .= " and p.products_date_added <= '" . tep_date_raw($dto) . "'";

}

 

if (tep_not_null($pfrom)) {

if ($currencies->is_set($currency)) {

$rate = $currencies->get_value($currency);

 

$pfrom = $pfrom / $rate;

}

}

 

if (tep_not_null($pto)) {

if (isset($rate)) {

$pto = $pto / $rate;

}

}

 

// if (DISPLAY_PRICE_WITH_TAX == 'true') {

// if ($pfrom > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) >= " . (double)$pfrom . ")";

// if ($pto > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) <= " . (double)$pto . ")";

// } else {

// if ($pfrom > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) >= " . (double)$pfrom . ")";

// if ($pto > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) <= " . (double)$pto . ")";

// }

// BOF Separate Pricing Per Customer

if ($status_tmp_product_prices_table == true) {

if (DISPLAY_PRICE_WITH_TAX == 'true') {

if ($pfrom > 0) $where_str .= " and (IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) >= " . (double)$pfrom . ")";

if ($pto > 0) $where_str .= " and (IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) <= " . (double)$pto . ")";

} else {

if ($pfrom > 0) $where_str .= " and (IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) >= " . (double)$pfrom . ")";

if ($pto > 0) $where_str .= " and (IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) <= " . (double)$pto . ")";

}

} else { // $status_tmp_product_prices_table is not true: uses p.products_price instead of cg_products_price

// because in the where clause for the case $status_tmp_special_prices is true, the table

// specials_retail_prices is abbreviated with "s" also we can use the same code for "true" and for "false"

if (DISPLAY_PRICE_WITH_TAX == 'true') {

if ($pfrom > 0) $where_str .= " and (IF(s.status AND s.customers_group_id = '" . $customer_group_id . "', s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) >= " . (double)$pfrom . ")";

if ($pto > 0) $where_str .= " and (IF(s.status AND s.customers_group_id = '" . $customer_group_id . "', s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) <= " . (double)$pto . ")";

} else {

if ($pfrom > 0) $where_str .= " and (IF(s.status AND s.customers_group_id = '" . $customer_group_id . "', s.specials_new_products_price, p.products_price) >= " . (double)$pfrom . ")";

if ($pto > 0) $where_str .= " and (IF(s.status AND s.customers_group_id = '" . $customer_group_id . "', s.specials_new_products_price, p.products_price) <= " . (double)$pto . ")";

}

}

// EOF Separate Pricing Per Customer

 

if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {

$where_str .= " group by p.products_id, tr.tax_priority";

}

 

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] == 'PRODUCT_LIST_NAME') {

$HTTP_GET_VARS['sort'] = $i+1 . 'a';

$order_str = ' order by pd.products_name';

break;

}

}

} else {

$sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1);

$sort_order = substr($HTTP_GET_VARS['sort'], 1);

$order_str = ' order by ';

switch ($column_list[$sort_col-1]) {

case 'PRODUCT_LIST_MODEL':

$order_str .= "p.products_model " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";

break;

case 'PRODUCT_LIST_NAME':

$order_str .= "pd.products_name " . ($sort_order == 'd' ? "desc" : "");

break;

case 'PRODUCT_LIST_MANUFACTURER':

$order_str .= "m.manufacturers_name " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";

break;

case 'PRODUCT_LIST_QUANTITY':

$order_str .= "p.products_quantity " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";

break;

case 'PRODUCT_LIST_IMAGE':

$order_str .= "pd.products_name";

break;

case 'PRODUCT_LIST_WEIGHT':

$order_str .= "p.products_weight " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";

break;

case 'PRODUCT_LIST_PRICE':

$order_str .= "final_price " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";

break;

}

}

 

$listing_sql = $select_str . $from_str . $where_str . $order_str;

 

require(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING);

?>

</td>

</tr>

<tr>

<td><?php echo tep_draw_separator('pixel_trans.gif', '100%', '10'); ?></td>

</tr>

<tr>

<td class="main"><?php echo '<a href="' . tep_href_link(FILENAME_ADVANCED_SEARCH, tep_get_all_get_params(array('sort', 'page')), 'NONSSL', true, false) . '">' . tep_image_button('button_back.gif', IMAGE_BUTTON_BACK) . '</a>'; ?></td>

</tr>

</table></td>

<!-- body_text_eof //-->

<td width="<?php echo BOX_WIDTH; ?>" valign="top"><table border="0" width="<?php echo BOX_WIDTH; ?>" cellspacing="0" cellpadding="2">

<!-- right_navigation //-->

<?php require(DIR_WS_INCLUDES . 'column_right.php'); ?>

<!-- right_navigation_eof //-->

</table></td>

</tr>

</table>

<!-- body_eof //-->

 

<!-- footer //-->

<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>

<!-- footer_eof //-->

<br>

</body>

</html>

<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

 

I hope that there is someone out there who could help me with this...

or is it possible that I am the only one facing those problems?

 

Thank you in advance for your help.

 

Regards,

RAlf

Link to comment
Share on other sites

Seems like something's cut my post :(

and my advanced_search_results.php looks like this:

I hope that there is someone out there who could help me with this...

or is it possible that I am the only one facing those problems?

 

Thank you in advance for your help.

 

Regards,

RAlf

 

Hi Ralf,

 

When I was going through this mess JanZ helped me a great deal - here are the two posts that seemed to correct the issues for me ;)

 

First - follow the new install instructions here:

http://www.oscommerce.com/forums/index.php?s=&...st&p=991613

 

Then apply the osC bug fix from here:

http://www.oscommerce.com/forums/index.php?s=&...st&p=923577

 

Hopefully this will solve the problem for you as well :)

~Tracy
 

Link to comment
Share on other sites

Thank you for your help...

 

I will try this.

 

As "fast" workaround I helped myself with going back to MySQL 4.0.27 and using advanced_search Version 4.14.

 

On the funny side Version 4.14 works much better than 4.15. But don't aks me why.

Link to comment
Share on other sites

I'm working on customizing/fixing another contrib to do something similar to what it was created for. I am getting an error that the tep_get_products_image() is an undefined function. I'm not a PHP expert by any means - so I'm hoping someone here might be able to tell me if the syntax used is correct. The line it refers to has this:

$contents[] = array('text' => '' . tep_info_image(tep_get_products_image($tInfo->product1_id)));

 

Shouldn't there be a . or a ' or something inside the quotes for the tep_get_products_image ? :huh:

 

Thanks in advance to anyone who can help me :blush:

~Tracy
 

Link to comment
Share on other sites

I am getting an error that the tep_get_products_image() is an undefined function.
It is indeed not a standard function in osC (unlike e.g. tep_get_products_name, tep_get_products_stock) so perhaps it needs to be added to a general.php?
Link to comment
Share on other sites

this is whay I get:

Products meeting the search criteria Products meeting the search criteria

1054 - Unknown column 'guyrab_gemart_v2.m.products_id' in 'on clause'

 

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id) left join products_group_prices_cg_1 as tmp_pp using(products_id), products_description pd, categories c, products_to_categories p2c 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 p2c.products_id = p.products_id and p2c.products_id = pd.products_id and (p2c.categories_id = '62') and (IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) <= 1000000)

 

when I am logged in as a customer with a pricegroup (so not the default zero group). if the customer is a simple retail customer, the advanced search works. this error only happens when the customer is a part of a customer group.... weird.

 

the other stuff works great. brouwsing, etc. only the advanced search malfunctions when logged in as a customer of a price group.

 

please help

Link to comment
Share on other sites

It is indeed not a standard function in osC (unlike e.g. tep_get_products_name, tep_get_products_stock) so perhaps it needs to be added to a general.php?

 

Well - I'm going back to a prior version of the 2gether discount contrib and manually making all the changes as the most recent shuts my whole site down with fatal errors :(

 

In admin/includes/functions/general.php it wants me to add this:

function tep_get_products_price($product_id) {
$product_query = tep_db_query("select products_price from " . TABLE_PRODUCTS . " where products_id = '" . (int)$product_id . "'");
$product = tep_db_fetch_array($product_query);
return $product['products_price'];
 }

 

Now I know that won't be right with SPPC installed - is this what I should put there instead?

 

function tep_get_products_price($product_id) {
$product_query = tep_db_query("select customers_group_price, customers_group_id from " . TABLE_PRODUCTS_GROUPS . " where products_id = '" . (int)$product_id . "'");
$product = tep_db_fetch_array($product_query);
return $product['products_price'];
 }

 

Or - is there an existing function that I could use and just go through the scripts for the 2gether discount and replace all tep_get_products_price references with the already existing function ?

~Tracy
 

Link to comment
Share on other sites

this is what I get:

Products meeting the search criteria Products meeting the search criteria

1054 - Unknown column 'guyrab_gemart_v2.m.products_id' in 'on clause'

 

select count(distinct p.products_id) as total from products p left join manufacturers m using(manufacturers_id) left join products_group_prices_cg_1 as tmp_pp using(products_id), products_description pd, categories c, products_to_categories p2c 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 p2c.products_id = p.products_id and p2c.products_id = pd.products_id and (p2c.categories_id = '62') and (IF(tmp_pp.status, tmp_pp.specials_new_products_price, tmp_pp.products_price) <= 1000000)

 

when I am logged in as a customer with a pricegroup (so not the default zero group). if the customer is a simple retail customer, the advanced search works. this error only happens when the customer is a part of a customer group.... weird.

That is weird indeed, because there is not even a 'on clause' mentioned in the query. Perhaps you can try to find that part of the query and change it to:

.... left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join products_group_prices_cg_1 as tmp_pp on tmp_pp.products_id = m.products_id, products_description pd,...etc.

Of course the products_group_prices_cg_1 will be different in the sql, using the customer group id.

Link to comment
Share on other sites

Now I know that won't be right with SPPC installed - is this what I should put there instead?

 

function tep_get_products_price($product_id) {
$product_query = tep_db_query("select customers_group_price, customers_group_id from " . TABLE_PRODUCTS_GROUPS . " where products_id = '" . (int)$product_id . "'");
$product = tep_db_fetch_array($product_query);
return $product['products_price'];
 }

That won't work for sure. You will need to add the customer_group_id in it, be prepared for when it is not set yet and take into account that there may not be a customers_group_price set (yet) so you would need the products_price. Your query will also result in more than one row if there are more groups and prices set for that products_id and last the field products_price is not in the query.

 

If that is for a number of products (like in a listing), it will be very inefficient code (too many sql queries, slowing down the site). So it is better to do it smarter :)

Link to comment
Share on other sites

Also - in application_top it has me put this:

case 'buy_tinnx' : if (isset($_GET['products_id'])) {
if (tep_has_product_attributes($_GET['products_id'])) {
tep_redirect(tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $_GET['products_id']));
} else {
if (tep_get_products_stock($_GET['products_id']) > 0) {
$cart->add_cart($_GET['products_id'], $cart->get_quantity($_GET['products_id'])+1);
}
}
}
if (isset($_GET['buy_tinn_add'])) {
if (tep_has_product_attributes($_GET['buy_tinn_add'])) {
tep_redirect(tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $_GET['buy_tinn_add']));
} else {
if (tep_get_products_stock($_GET['buy_tinn_add']) > 0) {
$cart->add_cart($_GET['buy_tinn_add'], $cart->get_quantity($_GET['buy_tinn_add'])+1);
}
}
}
tep_redirect(tep_href_link($goto, tep_get_all_get_params($parameters)));
break;

 

Does this need to be modified for SPPC or for Hide Products/Categories for SPPC ?

~Tracy
 

Link to comment
Share on other sites

And in the includes/modules/2gether.php file I have this query:

 

<?php
$together_query = tep_db_query("select distinct product_1_id, product_2_id, discount, type from ". TABLE_2GETHER ."  where product_1_id = '" . $_GET['products_id'] . "' and status = 1");
$num_together = tep_db_num_rows($together_query);
if ($num_together > 0) {
 $record = tep_db_fetch_array($together_query);
 $discount = $record['discount'];
 $discount_type = $record['type'];
 $aq ="select p.products_id, p.products_image, p.products_price, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd  where p.products_id='". $record['product_1_id'] ."' and p.products_id = pd.products_id and products_status = '1' and pd.language_id ='". $languages_id ."'";
 $bq ="select p.products_id, p.products_image, p.products_price, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd  where p.products_id='". $record['product_2_id'] ."' and p.products_id = pd.products_id and products_status = '1' and pd.language_id ='". $languages_id ."'";
}
else {
  $together_query = tep_db_query("select distinct product_1_id, product_2_id, discount, type from ". TABLE_2GETHER ." where product_2_id = '" . $_GET['products_id'] . "' and status = 1 ");
  $num_together = tep_db_num_rows($together_query);
  if ($num_together > 0) {
 $record = tep_db_fetch_array($together_query);
 $discount = $record['discount'];
 $discount_type = $record['type'];
 $aq ="select p.products_id, p.products_image, p.products_price, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd  where p.products_id='". $record['product_2_id'] ."' and p.products_id = pd.products_id and products_status = '1' and pd.language_id ='". $languages_id ."'";
 $bq ="select p.products_id, p.products_image, p.products_price, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd  where p.products_id='". $record['product_1_id'] ."' and p.products_id = pd.products_id and products_status = '1' and pd.language_id ='". $languages_id ."'";
  }
}

 

I am sure I need to modify this for both SPPC and Hide Products/Categories for SPPC. I am looking at how I modified the Cross Sell contrib. to try to help me - but the $together_query is throwing me off and then $aq and $bq aren't listed as actual queries. Do I only need to modify the $aq and $bq lines?

 

If it helps any - the only thing above this code is

<?php
if ($_GET['products_id']) {
include(DIR_WS_LANGUAGES . $language . '/2gether.php');

// BOF Separate Pricing Per Customer
if(!tep_session_is_registered('sppc_customer_group_id')) {
$customer_group_id = '0';
} else {
 $customer_group_id = $sppc_customer_group_id;
}
?>
<tr>
<td>

 

So I'm guessing the }else{ has to do with the if )$GET['products_id']) statement. I added the SPPC bit as I'm sure I will need it on there ;)

 

Thank you very much for your assistance - unfortunately I cannot seem to get answers on the 2gether discount thread. Now that I've managed to get the contrb. working without fatal errors and shutting my site down I'm trying to modify it to work with the existing contrb's I have installed. :blush:

~Tracy
 

Link to comment
Share on other sites

That won't work for sure. You will need to add the customer_group_id in it, be prepared for when it is not set yet and take into account that there may not be a customers_group_price set (yet) so you would need the products_price. Your query will also result in more than one row if there are more groups and prices set for that products_id and last the field products_price is not in the query.

 

If that is for a number of products (like in a listing), it will be very inefficient code (too many sql queries, slowing down the site). So it is better to do it smarter :)

 

Does this look better?

function tep_get_products_price($product_id) {
if ($customer_group_id != '0') {
$product_query = tep_db_query("select p.products_price, IF(pg.customers_group_price IS NOT NULL, pg.customers_group_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_GROUPS . " pg using(products_id) where products_id = '" . (int)$product_id . "' and pg.customers_group_id = '".$customer_group_id."'");
}else{
$product_query = tep_db_query("select products_price from " . TABLE_PRODUCTS . " where products_id = '" . (int)$product_id . "'");
$product = tep_db_fetch_array($product_query);
return $product['products_price'];
 }

~Tracy
 

Link to comment
Share on other sites

Does this look better?

function tep_get_products_price($product_id) {
if ($customer_group_id != '0') {
$product_query = tep_db_query("select p.products_price, IF(pg.customers_group_price IS NOT NULL, pg.customers_group_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_GROUPS . " pg using(products_id) where products_id = '" . (int)$product_id . "' and pg.customers_group_id = '".$customer_group_id."'");
}else{
$product_query = tep_db_query("select products_price from " . TABLE_PRODUCTS . " where products_id = '" . (int)$product_id . "'");
$product = tep_db_fetch_array($product_query);
return $product['products_price'];
 }

 

I think I also need to add a } after the 2nd query and before the $product = tep_db_fetch_array($product_query); line to end the else statement - right? :blush:

~Tracy
 

Link to comment
Share on other sites

And in the includes/modules/2gether.php file I have this query:

 

Ok - I'm hoping I have this right at least for the SPPC part (have to add in the Hide Products for SPPC part next) :blush:

 

Keeping in mind that the if(!tep_session_is_registered('sppc_customer_group_id')) {

bit of code is above this part of the code in the page - does this look right to you?

 

$together_query = tep_db_query("select distinct product_1_id, product_2_id, discount, type from ". TABLE_2GETHER ."  where product_1_id = '" . $_GET['products_id'] . "' and status = 1");
$num_together = tep_db_num_rows($together_query);
if ($num_together > 0) {
 $record = tep_db_fetch_array($together_query);
 $discount = $record['discount'];
 $discount_type = $record['type'];
 //edit for SPPC
 if ($customer_group_id != '0'){
 $aq ="select p.products_id, p.products_image, IF(pg.customers_group_price IS NOT NULL, pg.customers_group_price, p.products_price) as products_price, pd.products_name from " . TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_GROUPS . " pg using(products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd  where p.products_id='". $record['product_1_id'] ."' and p.products_id = pd.products_id and products_status = '1' and pd.language_id ='". $languages_id ."' and pg.customers_group_id = '".$customer_group_id."'";
 }else{
 $bq ="select p.products_id, p.products_image, p.products_price, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd  where p.products_id='". $record['product_2_id'] ."' and p.products_id = pd.products_id and products_status = '1' and pd.language_id ='". $languages_id ."'";
 }//end edit for SPPC
}
else {
  $together_query = tep_db_query("select distinct product_1_id, product_2_id, discount, type from ". TABLE_2GETHER ." where product_2_id = '" . $_GET['products_id'] . "' and status = 1 ");
  $num_together = tep_db_num_rows($together_query);
  if ($num_together > 0) {
 $record = tep_db_fetch_array($together_query);
 $discount = $record['discount'];
 $discount_type = $record['type'];
 //edit for SPPC
 if ($customer_group_id != '0') {
 $aq ="select p.products_id, p.products_image, IF(pg.customers_group_price IS NOT NULL, pg.customers_group_price, p.products_price) as products_price, pd.products_name from " . TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_GROUPS . " pg using(products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd  where p.products_id='". $record['product_2_id'] ."' and p.products_id = pd.products_id and products_status = '1' and pd.language_id ='". $languages_id ."' and pg.customers_group_id = '".$customer_group_id."'";
 }else{
 $bq ="select p.products_id, p.products_image, p.products_price, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd  where p.products_id='". $record['product_1_id'] ."' and p.products_id = pd.products_id and products_status = '1' and pd.language_id ='". $languages_id ."'";
 }// end edit for SPPC
  }
}

~Tracy
 

Link to comment
Share on other sites

I think I also need to add a } after the 2nd query and before the $product = tep_db_fetch_array($product_query); line to end the else statement - right? :blush:
Yes. I see you are getting the hang of it. You do have to get the $customer_group_id inside the function though:

 

function tep_get_products_price($product_id) {

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

if ($customer_group_id != '0') {
etcetera

Link to comment
Share on other sites

Ok - I'm hoping I have this right at least for the SPPC part (have to add in the Hide Products for SPPC part next)

 

Keeping in mind that the if(!tep_session_is_registered('sppc_customer_group_id')) {

bit of code is above this part of the code in the page - does this look right to you?

Yes, it looks correct for SPPC, but the contribution does not look to be very efficient in sql queries.

 

$together_query = tep_db_query("select distinct product_1_id, product_2_id, discount, type from ". TABLE_2GETHER ."  where product_1_id = '" . (int)$_GET['products_id'] . "' or product_2_id = '" . (int)$_GET['products_id'] . "' and status = 1");

would catch both cases. The rest would have to be changed to only look up the price of the product that has the products_id != (int)$_GET['products_id'].

 

Now it first does a query to see if the $_GET['products_id'] is equal to product_1_id and if not again to see if it is in the table as product_2_id. <_<

Link to comment
Share on other sites

Yes. I see you are getting the hang of it. You do have to get the $customer_group_id inside the function though:

 

Thank you very much! I added the $customer_group_id as you showed and hopefully that will take care of getting the correct price :)

 

For the includes/modules/2gether.php I now have this:

<?php
if ($_GET['products_id']) {
include(DIR_WS_LANGUAGES . $language . '/2gether.php');

// BOF Separate Pricing Per Customer
if(!tep_session_is_registered('sppc_customer_group_id')) {
$customer_group_id = '0';
} else {
 $customer_group_id = $sppc_customer_group_id;
}
?>
<tr>
<td>
<?php
$together_query = tep_db_query("select distinct product_1_id, product_2_id, discount, type from ". TABLE_2GETHER ."  where product_1_id = '" . (int)$_GET['products_id'] . "' or product_2_id = '" . (int)$_GET['products_id'] . "' and status = 1");
$num_together = tep_db_num_rows($together_query);
if ($num_together > 0) {
 $record = tep_db_fetch_array($together_query);
 $discount = $record['discount'];
 $discount_type = $record['type'];
 //edit for SPPC
 if ($customer_group_id != '0'){
 $aq ="select p.products_id, p.products_image, IF(pg.customers_group_price IS NOT NULL, pg.customers_group_price, p.products_price) as products_price, pd.products_name from " . TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_GROUPS . " pg using(products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd  where p.products_id='". $record['product_1_id'] ."' and p.products_id = pd.products_id and products_status = '1' and pd.language_id ='". $languages_id ."' and pg.customers_group_id = '".$customer_group_id."'";
 }else{
 $bq ="select p.products_id, p.products_image, p.products_price, pd.products_name from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd  where p.products_id='". $record['product_2_id'] ."' and p.products_id = pd.products_id and products_status = '1' and pd.language_id ='". $languages_id ."'";
 }//end edit for SPPC
}

 

Is that what you meant with the double query? I just removed the }else{ block and changed the $together_query. I'm not sure if I needed the }else{ block for anything other than that extra query - but it looked to be the same aside from the query so I'm hoping the above is what I was supposed to do :blush:

 

Now - for more fun - if the above is correct I need to somehow add in the check to see if the product is hidden from the customer group :blush:

~Tracy
 

Link to comment
Share on other sites

Now - for more fun - if the above is correct I need to somehow add in the check to see if the product is hidden from the customer group :blush:

 

Need to do that to this query for Cross Sell too :blush:

if ($customer_group_id != '0') {
$xsell_query = tep_db_query("select distinct p.products_id, p.products_image, pd.products_name, p.products_tax_class_id, IF(pg.customers_group_price IS NOT NULL, pg.customers_group_price, p.products_price) as products_price from " . TABLE_PRODUCTS_XSELL . " xp, " . TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_GROUPS . " pg using(products_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where xp.products_id = '" . $HTTP_GET_VARS['products_id'] . "' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' and p.products_status = '1' and pg.customers_group_id = '".$customer_group_id."' order by sort_order asc limit " . MAX_DISPLAY_ALSO_PURCHASED);
} else {
$xsell_query = tep_db_query("select distinct p.products_id, p.products_image, pd.products_name, p.products_tax_class_id, products_price from " . TABLE_PRODUCTS_XSELL . " xp, " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where xp.products_id = '" . $HTTP_GET_VARS['products_id'] . "' and xp.xsell_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' and p.products_status = '1' order by sort_order asc limit " . MAX_DISPLAY_ALSO_PURCHASED);
}

~Tracy
 

Link to comment
Share on other sites

Hi,

 

I just installed v4.1.5 and its working great. However when I was testing the accounts, I noticed that once I say the user is in wholesale, when he/she logs in or trys to buy something, it prompts them to choose from a drop down menu, which group they want to be in.

 

Is there a reason why they have to choose? I thought we just had to do that in the admin panel. Also I wouldn't want people to see other groups.

 

Thanks

Link to comment
Share on other sites

Hi,

 

I just installed v4.1.5 and its working great. However when I was testing the accounts, I noticed that once I say the user is in wholesale, when he/she logs in or trys to buy something, it prompts them to choose from a drop down menu, which group they want to be in.

 

Is there a reason why they have to choose? I thought we just had to do that in the admin panel. Also I wouldn't want people to see other groups.

 

Thanks

 

Is this happening on the email address you setup to test the conrib. with? I know for me, if I used a different email address I didn't get the option to choose. Only the test email address gives you the option to choose what group to log in as so that you can test things are working for any of your groups through just one account.

 

It threw me for a loop until JanZ pointed that out - ;) But for me it only asks if I log in with my testing account. If I create a different account with a different email address then it doesn't ask me anymore ;)

~Tracy
 

Link to comment
Share on other sites

Since the queries have

and pg.customers_group_id = '".$customer_group_id."'

 

would I still be able to add

and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0

 

at the end of the query - or is that going to try to call the customer_group_id twice?

 

Do I need to instead use something like this:

$aq.= " and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0 ";
$bq.= " and find_in_set('".$customer_group_id."', products_hide_from_groups) = 0 ";

 

Not sure where to put the latter though - does it need to be inside of a statement?

~Tracy
 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...