[Contribution] Filter special offer by category

[bhbilbao 1]

When filtering:

 

1054 - Unknown column 'p2c.categories_id' in 'where clause'

select count(*) as total from products p, products_description pd, 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 = '3' and s.status = '1' and p2c.categories_id=11

[TEP STOP]

 

My categories:

categories_id categories_image parent_id sort_order date_added last_modified

901 0 1 08/12/2009 0:43 08/12/2009 0:50

910 0 10 08/12/2009 0:43 04/01/2010 11:23

911 0 11 08/12/2009 0:43 05/01/2010 11:50

912 0 12 08/12/2009 0:43 08/12/2009 0:50

913 0 13 08/12/2009 0:43 08/12/2009 0:50

914 0 14 08/12/2009 0:43 08/12/2009 0:50

915 0 15 08/12/2009 0:43 08/12/2009 0:50

916 0 16 08/12/2009 0:43 08/12/2009 0:50

917 0 17 08/12/2009 0:43 08/12/2009 0:50

918 0 18 08/12/2009 0:43 08/12/2009 0:50

919 0 19 08/12/2009 0:43 08/12/2009 0:50

902 0 2 08/12/2009 0:43 08/12/2009 0:50

920 0 20 08/12/2009 0:43 08/12/2009 0:50

921 0 21 08/12/2009 0:43 08/12/2009 0:50

922 0 22 08/12/2009 0:43 08/12/2009 0:50

923 0 23 08/12/2009 0:43 08/12/2009 0:50

924 0 24 08/12/2009 0:43 04/01/2010 10:17

925 0 25 08/12/2009 0:43 08/12/2009 0:50

926 0 26 08/12/2009 0:43 08/12/2009 0:50

927 0 27 08/12/2009 0:43 08/12/2009 0:50

903 0 3 08/12/2009 0:43 08/12/2009 0:50

904 0 4 08/12/2009 0:43 08/12/2009 0:50

905 0 5 08/12/2009 0:43 04/01/2010 11:23

906 0 6 08/12/2009 0:43 08/12/2009 0:50

907 0 7 08/12/2009 0:43 08/12/2009 0:50

908 0 8 08/12/2009 0:43 08/12/2009 0:50

909 0 9 08/12/2009 0:43 08/12/2009 0:50

4 901 4 08/12/2009 0:43 13/12/2009 20:27

99 909 99 08/12/2009 0:43 08/12/2009 0:50

[puggybelle 29]

I installed this today and it does not work for me, either. The dropdown menu appears fine on specials.php and is populated with all of my categories. But, when I select a category to filter the specials list with, I get this:

 

1109 - Unknown table 'p2c' in where clause

select count(*) as total from products p, products_description pd, 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 = '1' and s.status = '1' and p2c.categories_id=33

 

I use osc2.2-MS2...PHP 5.2.9 and MySQL 4.1.22. The contrib offers the following query in catalog/specials.php to accomplish this filtering:

 

$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'";
 if($categories_id > 0)
 	$specials_query_raw .= " and p2c.categories_id=".(int)$categories_id;
 $specials_query_raw .= " order by s.specials_date_added DESC";

 

Can any of you troubleshoot what may be wrong with the query? I'm thinking that p2c isn't 'defined' in the query. This would be fabulous if it worked.

 

- Andrea

[FOD 0]

Try this, probably a long winded way of doing it but it works for me.

 

FIND

 

$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 pd.language_id = '" . (int)$languages_id . "' and s.status = '1'";

if($categories_id > 0)

$specials_query_raw .= " and p2c.categories_id=".(int)$categories_id;

$specials_query_raw .= " order by s.specials_date_added DESC";

 

 

REPLACE WITH

 

if($categories_id > 0) {

$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_PRODUCTS_TO_CATEGORIES . " p2c, " . 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' and p.products_id = p2c.products_id and p2c.categories_id=".(int)$categories_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";;

}

[87up 0]

Thank you FOD. That worked for me too. :)

 

Is it possible to filter by categories. ex. I have category called Car. This Car category has alot of sub-categories like Brakes, Tires and windshields and so on... Is it possible if you choose Car in the drop down menu, to list all the specials in Brakes, Tires and Windshields at once ?

[bhbilbao 1]

Thanks FOD. Also worked for me.

 

A point for you.

[puggybelle 29]

Thanks from me, too. :thumbsup: That works!

 

I have the same concern as 87up. Is it possible to have that query filter specials when selecting the main category (even though there are no products actually in it) rather than have to select the sub-categories below them to find the specials?

 

Either way, this is terrific help you gave, FOD, Thank You!

 

- Andrea

[puggybelle 29]

Monkeying with the query and I'm getting somewhere. At present, the specials will only show when you click on the category they're actually listed in. For example, if you have the following category setup:

 

People Magazine
         The 90's
              1991
              1992
              1993

 

The item on special in year 1993 will only appear onscreen when you select 1993 from the dropdown. If you select The 90's you get zero results. Same with the main parent category of People Magazine.

 

I've changed the query, and this enables you to see the specials for 1993 when selecting either 1993 or The 90's. Replace what FOD offered with this:

 

if (isset($categories_id)){
         if (empty($categories_id) && $categories_id==""){
               $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";
       }
       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, " . TABLE_PRODUCTS_TO_CATEGORIES . " pcat, " . TABLE_CATEGORIES . " cat where p.products_status = '1' and p.products_id = pcat.products_id and pcat.categories_id = cat.categories_id and ((cat.categories_id = " . $categories_id . " or cat.parent_id = " . $categories_id . ")) 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";
       }
 }

 

Still can't figure out how to select the main category of People Magazine and have it display specials for all the cats beneath it, but...it's better than nothing! Perhaps some query-gurus out there can take a shot at it?

 

- Andrea

[TCDK 0]

Hi

 

Great contrib, but is it possible only to list top categories in the list?

 

Eg. I have

 

 

Car

• Brakes
  
• Wheels
  
• Windows
  

 

I only want to show "Car" and not "Brakes, Wheels, Windows" etc.