X-Cart forums

[Pretty Scrappy]

I am getting a SQL error notification. I have done everything but had problems with the SQL patch. I never got it to run with success. I skipped over it and went into changeing files. Finished and then since I couldn't run the SQL I went into MYPHP admin, and found the "xcart_config" - "products_order" and added add_date:lbl_add_date
productcode:lbl_sku
title:lbl_product
orderby:lbl_default
price:lbl_price
to the variants

Than I check it and it is working fine so I thought. Then my email gets blowen up with this error

SQL query   : SELECT xcart_products.productid, xcart_products.provider, IF(xcart_products_lng.productid != '', xcart_products_lng.product, xcart_products.product) as product, xcart_products.productcode, xcart_products.avail, xcart_pricing.price, xcart_quick_prices.variantid, IFNULL(xcart_variants.avail, xcart_products.avail) as avail, IFNULL(xcart_variants.weight, xcart_products.weight) as weight, IFNULL(xcart_variants.productcode, xcart_products.productcode) as productcode, IF(xcart_classes.classid IS NULL,'','Y') as is_product_options, IF(xcart_variants.variantid IS NULL,'','Y') as is_variant, IF(xcart_images_T.id IS NULL, '', 'Y') as is_thumbnail, xcart_images_T.image_path, xcart_product_taxes.taxid FROM xcart_pricing, xcart_products LEFT JOIN xcart_products_lng ON xcart_products.productid = xcart_products_lng.productid AND code = 'US' LEFT JOIN xcart_quick_prices ON xcart_quick_prices.productid = xcart_products.productid AND xcart_quick_prices.membershipid = '0' LEFT JOIN xcart_product_memberships ON xcart_product_memberships.productid = xcart_products.productid LEFT JOIN xcart_classes ON xcart_classes.productid = xcart_products.productid LEFT JOIN xcart_variants ON xcart_variants.productid = xcart_products.productid AND xcart_quick_prices.variantid = xcart_variants.variantid LEFT JOIN xcart_images_T ON xcart_images_T.id = xcart_products.productid LEFT JOIN xcart_product_taxes ON xcart_product_taxes.productid = xcart_products.productid INNER JOIN xcart_products_categories ON xcart_products_categories.productid = xcart_products.productid INNER JOIN xcart_categories ON xcart_categories.categoryid = xcart_products_categories.categoryid AND xcart_categories.avail = 'Y' LEFT JOIN xcart_category_memberships ON xcart_category_memberships.categoryid = xcart_categories.categoryid INNER JOIN xcart_customers as ACHECK ON xcart_products.provider=ACHECK.login AND ACHECK.activity='Y' WHERE (xcart_category_memberships.membershipid = '0' OR xcart_category_memberships.membershipid IS NULL) AND xcart_products.forsale='Y' AND (xcart_product_memberships.membershipid = '0' OR xcart_product_memberships.membershipid IS NULL) AND xcart_quick_prices.priceid = xcart_pricing.priceid AND xcart_products.productid = xcart_products_categories.productid AND xcart_products_categories.categoryid = xcart_categories.categoryid AND xcart_products.productid = xcart_pricing.productid AND xcart_pricing.quantity = '1' AND xcart_pricing.membershipid = '0' AND xcart_products.product_type <> 'C' AND xcart_products.forsale <> 'B' AND (xcart_pricing.variantid = '0' OR xcart_variants.variantid = xcart_pricing.variantid) AND xcart_products.productid IN ('16628','16330','16407','16514','16218','16374','16340','16481','16586','16214') GROUP BY xcart_products.productid ORDER BY  LIMIT 10
    Error code  : 1064
    Description : 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 10' at line 1
Request URI: /xcart/product.php?printable=Y&productid=16543&cat=306&bestseller=Y
Backtrace:
/hsphere/local/home/pmemorie/prettyscrappy.com/xcart/include/func/func.db.php:180
/hsphere/local/home/pmemorie/prettyscrappy.com/xcart/include/func/func.db.php:106
/hsphere/local/home/pmemorie/prettyscrappy.com/xcart/include/func/func.db.php:322
/hsphere/local/home/pmemorie/prettyscrappy.com/xcart/include/func/func.product.php:578
/hsphere/local/home/pmemorie/prettyscrappy.com/xcart/recommends.php:70
/hsphere/local/home/pmemorie/prettyscrappy.com/xcart/product.php:144

Any Ideas on how to fix?

[JWait]

Try shutting off recommended products and let me know what happens.

[Pretty Scrappy]

Originally Posted by JWait

Try shutting off recommended products and let me know what happens.

Well it seems to be working ok. Atleast thats error at the top of the page is gone and the emails have stopped. I will keep you posted. I still can't believe it was so easy. I don't need to recommended products because I have new products displayed on my home page. Thank again

[JWait]

That is the one thing for the life of me I can't figure out... it works fine unless "Recommends" is enabled.

[deadzebrainc]

I thought I'd give the BCSE free tool a try before this to see if I could do what I wanted to do, but apparently it has disappeared! They recently upgraded their site and it is no longer there, but my old bookmark still works. Does anyone have a copy of the "Free Product Sort" this they could send me?

[juinjuin]

As far as I can see, the sort by Date Added in the BCSE mod is only in Ascending Order. There is no option to do a Descending Order sort.

Can anyone confirm?

[JWait]

Are you talking about on the Admin side or the Customer side? The customer can choose any of the "sort" criteria to have "first being first" or "last being first".

[Joedee]

To fix recommended products (and other searches):

edit include/func/func.product.php

Find:
"price" => "price",

Insert after:
"add_date" => "$sql_tbl[products].add_date",

FYI, for those in the thread who may know me or my wife, I'm not Joedee - I'm Christopher Kovacs =) We acquired the store from Joedee, and the forum login is the only thing I do not know how to change =P

[Pretty Scrappy]

Recently took over this store and all of the default sorts don't work. No matter what I do it stays on ABC. From the old owners post above I see that he used this mod, but its no longer working. Can anyone help?

[Pretty Scrappy]

nm found a mod that worked