Adding ability sort by date
 

Hi all,
I appreciate how helpful everyone has been towards me on this forum and hopefully this will allow me to give back a bit ;-). Balinor- please feel free to remove/move this post if it's in the wrong place or not allowed for any reason.
I contacted X-Cart regarding a fix to allow me to display products to customers in Newest First order. They gave me the following patch with instructions (no charge so I'm not taking money away from their services by posting this):
-------------------------
Unfortunately basic X-Cart does not provide the possibility to sort
products by the date they were added.

If you would like the pages to be sorted by date you should do the
following:

1) Login to the Admin back end of your shopping cart and open the
"Patch/Upgrade center" section there.

Find "Apply SQL patch" section, in the field "SQL query(ies)" insert
the following queries:

REPLACE INTO xcart_config VALUES ('products_order','Select the order of
products to display within
category','orderby','Appearance',110,'selector','o rderby','add_date:
lbl_add_date\nproductcode:lbl_sku\ntitle:lbl_produ ct\norderby: lbl_default\nprice:lbl_price');
INSERT INTO xcart_languages VALUES ('US','lbl_add_date','Add
date','Labels');

and press "Apply".


2) Edit "include/search.php" file. Find the following line:


"orderby" => func_get_langvar_by_name("lbl_default"),

and after it insert the line:

"add_date" => func_get_langvar_by_name("lbl_add_date"),


3) In the same file find the following line:


# Sort the search results...

Change the following line:

$direction = ($data["sort_direction"] ? "DESC" : "ASC");

to this one:

$direction = ($data["sort_direction"] ? "ASC" : "DESC");

4)edit the same file - find block:


case "title":
$sort_string = "$sql_tbl[products].product $direction";
break;


and after it insert the following block:

case "add_date":
$sort_string = "$sql_tbl[products].add_date $direction";
break;


Now you will be able to see the "Add date" link in the "Sort by" field
in the Customer area and in the Admin area (General settings/Appearance
options/Displaying products). If you want the sorting by "Add date" to
the be the default one, set the "Select the order in which products
should be displayed within a category" field value in "Add date" and
press the "Save" button.

Re: Adding ability sort by date
 

Thank you for sharing :)

Re: Adding ability sort by date
 

Cheers stylefly for a great share - I'll have to check this one out.

Re: Adding ability sort by date
 

Thanks for posting this I kind of ran into an problem. I got the "Add Date" to display on the customer section but its not in the drop down menu under general settings/display products. There is only a blank space for where its suppose to be. Also when I select that blank text in the drop down menu it gives me a SQL error on the customer section when viewing an item.

I did run into a problem earlier on I couldnt add "INSERT INTO xcart_languages VALUES ('US','lbl_add_date','Add date','Labels');"I was only able to add "REPLACE INTO xcart_config VALUES" query. Any suggestions

Re: Adding ability sort by date
 

Is there a way to do step #1 without going through the patch/upgrade center? I can not access mine, and have found no help for the problem yet. So I am wondering if I can change files manually instead of using that center? I would love to use this!

Re: Adding ability sort by date
 

That's an SQL query, so no, you need to have direct access to the DB to alter that. You can use PHPMyAdmin (available on most hosting control panels) to run that. Sorry I can't help you on the patch.php issue, haven't seen that before.

Re: Adding ability sort by date
 

Thanks! I sent a note to x-cart for help on the patch center issue. Hopefully they will know what is going on. :)

Re: Adding ability sort by date
 

I think that is because the sql query in the first post is incorrect. I had the same problem. I used phpmyadmin and went into the database...

I looked in "xcart_config" for "products_order" under the "name" heading (mine was on page 8 - your results may vary) I found there were spaces where there shouldn't be, so I fixed them.

The "variants" section should read...
add_date:lbl_add_date
productcode:lbl_sku
title:lbl_product
orderby:lbl_default
price:lbl_price

(make sure there are no added spaces or it throws the whole thing off)

EDIT: I did find another problem however...

When selecting "Add Date" as the choice for "Select the order in which products should be displayed within a category:" there is a SQL error in the Product page to the customer.

INVALID SQL: 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 3' at line 1
SQL QUERY FAILURE:SELECT xcart_products.productid, 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, 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_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 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_products.avail > 0 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_variants.avail > 0)) AND xcart_products.productid IN ('16463','18287','17351') GROUP BY xcart_products.productid ORDER BY LIMIT 3

Any ideas why?

Re: Adding ability sort by date
 

As an update on this problem, it appears to be tied to the Recommended Products. If I disable Recommended Products then the "Sort by Date" works with no problem, and the product page is fine. If I chose any function other than "Add Date" in the "Select the order in which products should be displayed within a category:" section the Recommended Products work fine. In other words, I can choose one but both will not work together, which is what I want. I'm using version 4.1.8 to test.

Re: Adding ability sort by date
 

I am getting the same errors... how do I back out this modification? Anyone?