Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Filterlist v1


scottybwoy

Recommended Posts

Hey guys

have to say great contribution I have a small problem someone might be able to help me with

I get a error when I do a price filter

 

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

 

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 left join tax_rates tr on p.products_tax_class_id = tr.tax_class_id left join 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 = '222') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '209') 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 pd.language_id = '1' and p2c.categories_id = '57' and m.manufacturers_id = '9' 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) ) >= 90) 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) ) <= 150)

 

this error is for product model search

 

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 'limit 12, 12' at line 1

 

select pd.products_name, p.products_image, 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(( 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 left join categories c on c.categories_id = p2c.categories_id where c.categories_status = '1' and p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '57' order by limit 12, 12

 

manufacturer filter, price , product name work but manufacturers and model do not

I have enable/disable contribution installed I'm not sure it would be affecting it

 

I followed the instruction about index.php to configure also

 

Thanks

alman

 

ps it is the literally the last php quote <?php //} ?>

Edited by alman
Link to comment
Share on other sites

  • 2 weeks later...
Then the delete the very last line..

 

Maybe this is what I am missing. Exactly delete what last line are we talking?

The filterlist shows up when someone comes from a direct link to a manufaturers list page.

 

But try to enter a keyword or display by more pages it redirects to homepage.

 

I noticed the basename file when this happens is index.php - not the category url as normal if that makes any sense.

Anyway is there a way to add a category sort when on a sitewide manufacturers list page?

 

This contrib is called by the products listing module, installed as standard. So when a page calls the products listing module, this contrib will appear. Unfortunately different pages use different methods to call/sort products. When one of the filter parameters is used it then sends the data to whatever file called the products listing module, so products listing knows how to sort it.

 

I did try changing this to a file that definately knows how to sort it, i.e advanced search, but then customers would only be able to filter through what they have already filtered. i.e. say they chose manufacturer a, they would not be able to see the results for manufacturer b in the same category, in fact if they chose that, they would see no products at all.

 

Also I don't see where the manufacturers list page is on my site, but I assume that is called by index.php which doesn't understand the search parameters, so would just show the home page. If you tell me your site, I can go have a quick look to see where you mean so I can be a bit more helpful.

 

Then the delete the very last line..

 

Maybe this is what I am missing. Exactly delete what last line are we talking?

 

Last line of filterlist.php

<?php } ?>

Yeah that could f**k up the rest of your script, if you remove one line without the other.

 

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

 

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 left join tax_rates tr on p.products_tax_class_id = tr.tax_class_id left join 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 = '222') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '209') 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 pd.language_id = '1' and p2c.categories_id = '57' and m.manufacturers_id = '9' 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) ) >= 90) 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) ) <= 150)

 

this error is for product model search

 

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 'limit 12, 12' at line 1

 

select pd.products_name, p.products_image, 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(( 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 left join categories c on c.categories_id = p2c.categories_id where c.categories_status = '1' and p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '57' order by limit 12, 12

 

manufacturer filter, price , product name work but manufacturers and model do not

I have enable/disable contribution installed I'm not sure it would be affecting it

 

I followed the instruction about index.php to configure also

 

Firstly check your db to see if you actually have products_tax_class_id in your products table.

 

It's a bit too much for me to look at at the moment, but it looks like you may need to check any amendments you made to the index.php where I specified in an earlier post. Please note the code I posted cannot be copied and pasted directly to yours as it will be different for each individual site, so I hope you backed yours up. If all else fails just use the original index.php and play around with the filterlist.php Lines 418 - 421 untill you get the right order.

 

Good Luck.

 

P.S that enable/disable contrib shouldn't affect this one.

Link to comment
Share on other sites

  • 1 month later...
  • 1 month later...

Hi there, Can anyone help me out?

 

Installed this add on great contribution every shows up fine but Sort items by : Model | Product Name | Manufacturer | Price [+] part is not clickable it's just words not links.

 

Can anyone tell me whats wrong?

Link to comment
Share on other sites

Hi there, Can anyone help me out?

 

Installed this add on great contribution every shows up fine but Sort items by : Model | Product Name | Manufacturer | Price [+] part is not clickable it's just words not links.

 

Can anyone tell me whats wrong?

 

 

Ayone?

Link to comment
Share on other sites

<div>

<?php

// Sort columns by ??? You may need to rearrange the numbers in order of your columns in product listing

echo SORT_BY . tep_create_sort_heading($HTTP_GET_VARS['sort'], 2, TABLE_HEADING_MODEL) . ' | ' .

tep_create_sort_heading($HTTP_GET_VARS['sort'], 1, TABLE_HEADING_PRODUCTS) . ' | ' .

tep_create_sort_heading($HTTP_GET_VARS['sort'], 2, TABLE_HEADING_MANUFACTURER) . ' | ' .

tep_create_sort_heading($HTTP_GET_VARS['sort'], 5, TABLE_HEADING_PRICE);

?>

</div>

 

This is the bit that ain't working it just appears as text not clickable links

Link to comment
Share on other sites

  • 4 months later...

Hello mcafee secure gave me a problem with this contribution with an SQL Injection error does anyone know how i can fix this error with out removing the contribution thanks

Perhaps it has to do with the sorting (filterlist.php line 332 and further).

In older osC versions and this file the "ordering" code on index.php starts with:

	  $sort_col = substr($HTTP_POST_VARS['sort'], 0 , 1);
  $sort_order = substr($HTTP_POST_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;

In later osC versions the $order_str = ' order by '; was removed and added further on (for each case). Like in the above piece of code:

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

     switch ($column_list[$sort_col-1]) {
       case 'PRODUCT_LIST_MODEL':
         $listing_sql .= " order by p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
         break;

Link to comment
Share on other sites

That did not fix it anything else i can try?

 

 

Perhaps it has to do with the sorting (filterlist.php line 332 and further).

In older osC versions and this file the "ordering" code on index.php starts with:

	  $sort_col = substr($HTTP_POST_VARS['sort'], 0 , 1);
  $sort_order = substr($HTTP_POST_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;

In later osC versions the $order_str = ' order by '; was removed and added further on (for each case). Like in the above piece of code:

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

     switch ($column_list[$sort_col-1]) {
       case 'PRODUCT_LIST_MODEL':
         $listing_sql .= " order by p.products_model " . ($sort_order == 'd' ? 'desc' : '') . ", pd.products_name";
         break;

Link to comment
Share on other sites

  • 1 month later...

One other question: when you search a keyword that doesn't exist, the filter disappears and you have to choose 'back' in your browser to enter a different choice. What do we have to modify to keep it there after "no results" are returned?

Link to comment
Share on other sites

I'm getting the 1054 - Unknown column 'p.products_tax_class_id' in 'on clause' error since upgrading to Mysql 5. Works fine on SQL4 with no changes.

 

Have read through and tried all suggestions in these posts to no avail. Has anyone got this to work on SQL5? Am using RC2a and have no problems anywhere else in the store - just when running a shop by price query using this contribution. Don't want to take it out as it would be a great contribution if I can get it to work on sql5 but don't know enough about sql to fix the queries.....

 

Please help!

Link to comment
Share on other sites

  • 7 months later...

Hello. I am having trouble to get this great contribution working on my site. I followed the entire instruction, and upon testing I received the following errors:

 

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 'order by pd.products_name desc limit 0, 20' at line 1

 

select pd.products_name, p.products_image, p.products_id, p.products_image_med, p.short_desc, 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 (((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) left join categories c on c.categories_id = p2c.categories_id where c.categories_status = '1' and p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' and p2c.categories_id = '199' order by order by pd.products_name desc limit 0, 20

 

[TEP STOP]

 

 

Please help. My OSC version is RC2a.

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