Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Server Upgrade To MySql5


p1a6u3

Recommended Posts

Well it seems our server host has decided to upgrade their MySql to 5.x.x

 

I have seen this error all over the board here and other places for oscommerce support....I have done the "patches" (editing of the index.php file as well advanced_search_results.php & advanced_search_results.tpl.php) needed for the php to combat this issue....but to no avail I am still having the same issue...

 

When clicking on any category, and upon viewing of the product category selected, or adding product to the shopping cart, we see this error:

 

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

 

select pd.products_name, p.products_model, p.products_image, p.products_id, p.manufacturers_id, p.products_price, p.products_weight, p.products_price1,p.products_price2,p.products_price3,p.products_price4, p.products_price5,p.products_price6,p.products_price7,p.products_price8,p.produc

ts_price9,p.products_price10,p.products_price11, p.products_price1_qty,p.products_price2_qty,p.products_price3_qty,p.products_pri

ce4_qty, p.products_price5_qty,p.products_price6_qty,p.products_price7_qty,p.products_pri

ce8_qty,p.products_price9_qty,p.products_price10_qty,p.products_price11_qty, p.products_qty_blocks, 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 products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id where p.products_id = '2' and pd.products_id = '2' and pd.language_id = '1'

 

[TEP STOP]

 

Now we do have creloaded and a template (svelte-140) which has worked well for us....but now since this upgrade we are done in until we can find a resolution to this.....we have also tried changing to the stock oscommerce look as well as other templates and we are still experiencing the same error on all of them so it isnt just the template(s) that is doing this...any help would be greatly appreciated in this matter...if you require more info or the posting of certain php files, please let me know and i will gladly post them up for you all to read....i am very patient and very open minded....thank you to all who chime in to help!

Link to comment
Share on other sites

Are you sure you made the correct changes? Here are the changes that need to be made.

 

MySQL 5.0 Compatibility


Problem:


MySQL 5.0 introduces Server SQL modes as part of its SQL 2003 standards support, and uses a more stricter approach to executing SQL queries. This is performed by default with setting STRICT_TRANS_TABLES as a Server SQL mode.



Due to this new setting, MySQL fails on certain SQL queries and produces error messages on the screen.



Solution:


Lines 213-223 in catalog/advanced_search_result.php must be changed from:



$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, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";



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 . "')";

}



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





to:



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



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



The following lines must be replaced in catalog/index.php:



Line 175, from:



$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 . " 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'] . "'";



to:



$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 left join " . TABLE_SPECIALS . " s on p.products_id = s.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['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'] . "'";





Line 178, from:



$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 . " 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'] . "'";



to:



$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 left join " . TABLE_SPECIALS . " s on p.products_id = s.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'] . "'";



Line 184, from:



$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 . " 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['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 . "'";



to:



$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 left join " . TABLE_SPECIALS . " s on p.products_id = s.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 . "'";





Line 187, from:



$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, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id 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 . "'";



to:



$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 . " 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 . "'";



Line 292 in catalog/admin/categories.php must be changed from:



tep_db_query("insert into " . TABLE_PRODUCTS . " (products_quantity, products_model,products_image, products_price, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id) values ('" . tep_db_input($product['products_quantity']) . "', '" . tep_db_input($product['products_model']) . "', '" . tep_db_input($product['products_image']) . "', '" . tep_db_input($product['products_price']) . "',  now(), '" . tep_db_input($product['products_date_available']) . "', '" . tep_db_input($product['products_weight']) . "', '0', '" . (int)$product['products_tax_class_id'] . "', '" . (int)$product['manufacturers_id'] . "')");



to:



tep_db_query("insert into " . TABLE_PRODUCTS . " (products_quantity, products_model,products_image, products_price, products_date_added, products_date_available, products_weight, products_status, products_tax_class_id, manufacturers_id) values ('" . tep_db_input($product['products_quantity']) . "', '" . tep_db_input($product['products_model']) . "', '" . tep_db_input($product['products_image']) . "', '" . tep_db_input($product['products_price']) . "',  now(), " . (empty($product['products_date_available']) ? "null" : "'" . tep_db_input($product['products_date_available']) . "'") . ", '" . tep_db_input($product['products_weight']) . "', '0', '" . (int)$product['products_tax_class_id'] . "', '" . (int)$product['manufacturers_id'] . "')");

 

In the change for the index page note the placement of the left join for the specials table. See how yours does not match?

When using the left join it must be written right after the table it's being joined to.

Link to comment
Share on other sites

here is the index.php file

 

Maybe this will help us all....

 

I have done alot of editing to this file, as well, it does have contributions added because of the creloaded we have installed......If what you have posted is the same patches by "Monika" that were written by someone else then i have done those same edits.....i have looked over the php file and it seems like i have done everyhting.....more eyes are better than just my pair so if you or anyone sees the culprit within the file, it would be greatly appreciated to point it out....thank you so much Brian for the response!

 

 

POST EDIT: UGGGG.....i need to get the file for you guys....ill be back n a bit to reedit this post....sorry for the waste of space

Link to comment
Share on other sites

Here is my index.php file from creloaded and advanced_search_result.tpl.php file from the template..sorry it took so long to get back to you on this

 

advanced_search_result.tpl.php

<table border="0" width="100%" cellspacing="0" cellpadding="<?php echo CELLPADDING_SUB; ?>">

<?php

// BOF: Lango Added for template MOD

if (SHOW_HEADING_TITLE_ORIGINAL == 'yes') {

$header_text = ' '

//EOF: Lango Added for template MOD

?>

<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>

<?php

}else{

$header_text = HEADING_TITLE_2;

}

?>

<?php

// BOF: Lango Added for template MOD

if (MAIN_TABLE_BORDER == 'yes'){

table_image_border_top(false, false, $header_text);

}

// EOF: Lango Added for template MOD

?>

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

}

 

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

 

 

 

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 . ")";

}

 

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>

<?php

// BOF: Lango Added for template MOD

if (MAIN_TABLE_BORDER == 'yes'){

table_image_border_bottom();

}

// EOF: Lango Added for template MOD

?>

<tr>

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

</tr>

<tr>

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

</tr>

<tr>

<td class="main" style="padding-left: 10px"><?php echo '<a href="' . tep_href_link(FILENAME_ADVANCED_SEARCH, tep_get_all_get_params(array('sort', 'page')), 'NONSSL', true, false) . '">' . tep_template_image_button('button_back.gif', IMAGE_BUTTON_BACK) . '</a>'; ?><br /><br style="line-height:10px"></td>

</tr>

</table>

Link to comment
Share on other sites

index.php

 

<?php

/*

$Id: index.php,v 1.2 2004/03/09 19:56:29 ccwjr Exp $

 

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');

 

 

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

 

if ($category_depth == 'nested') {

//Code change for Categories Description 1.5

// $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 . "'");

//Changed to the following

$category_query = tep_db_query("select cd.categories_name, cd.categories_heading_title, cd.categories_description, 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 = '" . $languages_id . "'");

//End Categories Description 1.5

 

$category = tep_db_fetch_array($category_query);

 

$content = CONTENT_INDEX_NESTED;

 

} 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;

}

// Eversun mod for sppc and qty price breaks

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')

// Eversun mod end for sppc and qty price breaks

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

}

}

// Get the category name and description

$category_query = tep_db_query("select cd.categories_name, cd.categories_heading_title, cd.categories_description, 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 = '" . $languages_id . "'");

$category = tep_db_fetch_array($category_query);

 

 

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

//Eversun mod for sppc and qty price breaks

 

/*

$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 . " 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'] . "'";

} else {

// We show them all

$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 . " 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'] . "'";

}

} 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

$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 . " 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['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 {

// We show them all

$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, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id 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 . "'";

*/

 

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 left join " . TABLE_SPECIALS . " s on p.products_id = s.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['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...

} else {

// We show them all

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 left join " . TABLE_SPECIALS . " s on p.products_id = s.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'] . "'";

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

}

} 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;

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 left join " . TABLE_SPECIALS . " s on p.products_id = s.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 . "'";

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

} else {

// We show them all

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), " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_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 . "'";

} 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 . " 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...

// Eversun mod for sppc and qty price breaks

}

}

 

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_column = CATEGORIES_SORT_ORDER;

$sort_order = 'a';

} else {

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

$sort_column = $column_list[$sort_col-1];

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

}

$listing_sql .= ' order by ';

switch ($sort_column) {

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;

}

 

$content = CONTENT_INDEX_PRODUCTS;

 

} else { // default page

 

$content = CONTENT_INDEX_DEFAULT;

 

}

 

require(DIR_WS_TEMPLATES . TEMPLATE_NAME . '/' . TEMPLATENAME_MAIN_PAGE);

 

require(DIR_WS_INCLUDES . 'application_bottom.php');

?>

Link to comment
Share on other sites

Not sure where it's coming from here is the part giving you problems.

This is form the error you posted above in the first post

from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join specials s on p.products_id = s.products_id

In paticular this part

left join specials s on p.products_id = s.products_id

That needs to go before products_to_categories p2c. You need to find that query and change that part.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...