5 replies to this topic

[superfrank]

I have installed the "Activate Inactivate Categories" contribution (http://addons.oscommerce.com/info/6462) on a by now heavily modified install of osC 2.2MS2. Part of this contribution is to add 'status_categ' as a new column in the table 'categories'.

As "doinitanistor" pointed out, this contribution is not really complete, as it still sometimes displays inactive categories, or lists products of inactive categories. To overcome this, it was enough to add status_categ = 1 or c.status_categ = 1 in relevant queries in some additional files.

But I couldn't find a solution for the file products_new.php. Probably my question is just a MySQL syntax question...

The original query is:
$products_new_query_raw = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";


I cannot just add the status_categ = 1, since the table TABLE_CATEGORIES is not mentioned. If I add it in the query like below, every article will be listed multiple times (14 times to be exact, the total number of my categories):
$products_new_query_raw = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name, c.status_categ from " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where c.status_categ = 1 AND p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";

So I thought to include the table TABLE_PRODUCTS_TO_CATEGORIES as well, and next to make additional 'where' clausules, it would fix that problem:  
$products_new_query_raw = "select p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd where c.status_categ = 1 AND c.categories_id = p2c.categories_id AND p.products_id = p2c.products_id AND p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";

But now I get a result of 406 products, instead of the 393, which it should be. I suspect this is (partly) due to 'cloned' entries. So I tried to add a "distinct" in the query:
$products_new_query_raw = "select distinct p.products_id, pd.products_name, p.products_image, p.products_price, p.products_tax_class_id, p.products_date_added, m.manufacturers_name from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on (p.manufacturers_id = m.manufacturers_id), " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_DESCRIPTION . " pd where c.status_categ = 1 AND c.categories_id = p2c.categories_id AND AND p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by p.products_date_added DESC, pd.products_name";
and also add "products_id" in the following line to get to:
$products_new_split = new splitPageResults($products_new_query_raw, MAX_DISPLAY_PRODUCTS_NEW, 'p.products_id');

But this will still generate an error:

Quote

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND p.products_status = '1' and p.products_id = pd.products_id and pd.language_i' at line 1

select count(distinct p.products_id) as total from products p left join manufacturers m on (p.manufacturers_id = m.manufacturers_id), categories c, products_description pd where c.status_categ = 1 AND c.categories_id = p2c.categories_id AND AND p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1'

So okay, now I am stuck... Can anybody get me going again? 'All I want' is to display ONLY the articles which are in ACTIVE categories.

[ianric]

Hi

Sorry to hijack another post but my prob is similar with the same contrib. Did anyone have an answer to this??

I've got it all working except for the specials and new_products pages (boxes seem to be OK). If I have a hidden category, when I pick on the arrow in the box heading I get a 1064 error

Quote

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) as total from products p, products_description pd, specials s, products_to_c' at line 1

select count(distinct *) as total from products p, products_description pd, specials s, products_to_categories p2c where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and p.products_id = p2c.products_id and (not p2c.categories_id in (26,53)) and pd.language_id = '1' and s.status = '1'

26 & 53 are my hidden category id's. I'm also using the default specials.php from RC2.2a, PHP 5.3 and mysql 5.1.37

I can't find that SQL anywhere in my files. This is what the contrib install says for the specials page

Quote

Around line 54 find the line that reads as follows:

  $specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by s.specials_date_added DESC";

and REPLACE it with the following:

  if (!empty($hiddencats)) {
    $specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and p.products_id = p2c.products_id and (not p2c.categories_id in (" . implode(',', $hiddencats) . ")) and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' group by p.products_id order by s.specials_date_added DESC";
  } else {
    $specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1' order by s.specials_date_added DESC";
  }

If I var_dump() the contents of the new SQL, I get

Quote

string(488) "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from products p, products_description pd, specials s, products_to_categories p2c where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and p.products_id = p2c.products_id and (not p2c.categories_id in (26,53)) and pd.language_id = '1' and s.status = '1' group by p.products_id order by s.specials_date_added DESC"

Pasting that into phpmyadmin works, no errors. I've tried the various fixes on the contrib page but non have worked.

It's probably something easy. Any help would be appreciated.

Many thanks

Ian

[IainF]

ianric, on 18 October 2009 - 11:18 AM, said:

Hi Ian (if you haven't given up),

I'm very new to this, so apologies for the messy code, but we had the same problem.

count(distinct *) is where the error is; it's generated in catalog\includes\classes\split_page_results.php

As a quick and dirty fix I just added the following:

// Mod - added line below below to avoid sql error on catalog/specials.php
  if ($count_string = 'distinct *') $count_string = 'distinct p.products_id';

under

if (strpos($this->sql_query, 'distinct') || strpos($this->sql_query, 'group by')) {
        $count_string = 'distinct ' . tep_db_input($count_key);
      } else {
        $count_string = tep_db_input($count_key);
      }

( at about line 53 in catalog\includes\classes\split_page_results.php )

I don't have time just now to look into why it's trying to build the expression the wrong way, but at least this fixes it after it does so.
Someone may be along shortly with a more elegant solution!

[CosmeaNailArt]

IainF, on 18 February 2010 - 01:12 PM, said:

Hi Ian (if you haven't given up),

I'm very new to this, so apologies for the messy code, but we had the same problem.

count(distinct *) is where the error is; it's generated in catalog\includes\classes\split_page_results.php

As a quick and dirty fix I just added the following:

// Mod - added line below below to avoid sql error on catalog/specials.php
  if ($count_string = 'distinct *') $count_string = 'distinct p.products_id';

under

if (strpos($this->sql_query, 'distinct') || strpos($this->sql_query, 'group by')) {
$count_string = 'distinct ' . tep_db_input($count_key);
  } else {
$count_string = tep_db_input($count_key);
  }

( at about line 53 in catalog\includes\classes\split_page_results.php )

I don't have time just now to look into why it's trying to build the expression the wrong way, but at least this fixes it after it does so.
Someone may be along shortly with a more elegant solution!

With this fix is the specials.php page is repaired. On the otherside account_history.php won't work anymore. If you remove the fix account_history.php will work again, and specials.php won't.

So it's a choice you'll have to make, or someone else has a solution for this????

Tjappie W

Edited by CosmeaNailArt, 03 October 2011 - 09:49 PM.

[jaderrosa]

Hi.

On products_new.php find:
$products_new_split = new splitPageResults($products_new_query_raw, MAX_DISPLAY_PRODUCTS_NEW);

Change to:
$products_new_split = new splitPageResults($products_new_query_raw, MAX_DISPLAY_PRODUCTS_NEW, 'p.products_id');

On specials.php find:
$specials_split = new splitPageResults($specials_query_raw, MAX_DISPLAY_SPECIAL_PRODUCTS);

Change to:
$specials_split = new splitPageResults($specials_query_raw, MAX_DISPLAY_SPECIAL_PRODUCTS, 'p.products_id');

Jader Rosa
www.aminhaloja.com.br

[prvhk3]

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

select count(distinct p.products_date_added) as total from products p left join manufacturers m using(manufacturers_id) left join specials s on p.products_id = s.products_id, products_description pd, categories c, products_to_categories p2c, categories_description cd 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 c.categories_id = cd.categories_id and ((pd.products_name like '%64%' or p.products_model like '%64%' or m.manufacturers_name like '%64%')


OsCommerce V2.2


I get this error if i search any thing on my website.

Please help to solve the error ASAP.


Thanks in advance.