Sorting "Products purchased" by date of release - is that possible?

[CaroleAs]

I have been adding digital products almost every week for over 12 years now so I do have quite a large store. Even my regular customers tend to discover something "new" regularly. To help them, I started, a couple of years ago, to feature a past product that was popular.

To determine which product was popular, I use the number of sales. If I go to the "products purchased" report, I will get all the products sorted by the number of sales. This is a little skewed since I have some products that are free, some that have been there for 10 years, while others were released just last year. I also have products that are seasonal (Easter, Christmas, summer, Valentine, etc.) so I would like to get the products sorted by DATE instead.

Is that something possible?

So far, I have created a spreadsheet that I update regularly, with the product names, number of sales, month it was released (mostly for the seasonality) and it is just tedious to update so I am looking for something a little more "automated" if possible. (I have over 800 products!)

[dbltoe]

The products table has the products_date_added and products_last_modified fields already there. I would probably use the products_last_modified

The following does not include the number of sales for each product or take into consideration a product that is in a sub-category of a sub-category of a category

If you are using a MySQL statement to retrieve all the products, you would just add an ORDER BY

Something like

SELECT p.*, pd.products_nameFROM products AS p
JOIN products_description AS pd ON p.products_id = pd.products_id
ORDER BY p.products_last_modified DESC
INTO OUTFILE '/path/to/your/exported_file.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Note that this is set to get all the info from the products table and will show the newest date first. You may only want the products_id. if so, the p.* would become p.products_id. Changing to that would give you the product title and date last entered/modified.

If you wanted to include a link to the product in the spreadsheet, you would need to do a little more work for that as

https://your_site.com/index.php?main_page=product_info&cPath=1_36&products_id=338

has the main category of 1 with the sub-category of 26 and then the products_id of 338.

In the case above, the 1_36 is the combination of the parent_id for the categories_id of 38 in the categories table and, the 36 is the master_categories_id from the products table.

SELECT p.*, 
       pd.products_name,
       CONCAT('https://yoursite.com/index.php?main_page=product_info&cpPath=', c.parent_id, '_', c.master_categories_id, '&products_id=', p.products_id) AS product_url
FROM products AS p
JOIN products_description AS pd ON p.products_id = pd.products_id
JOIN categories AS c ON c.categories_id = p.master_categories_id
ORDER BY p.products_last_modified DESC
INTO OUTFILE '/path/to/your/exported_file.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Again, this code is getting everything from the products table.

CAVEAT EMPTOR as I have not thoroughly tested the above. The good news is that SELECT statements can and do fail without munging the DB.

STILL... BACKUP FIRST!

[dbltoe]

I've had a chance to test and both those are getting a 1045 error when submitting thru ZC.

I'll investigate some more.

[CaroleAs]

Yikes. That is scaaaaaaary! Especially with an error!

[dbltoe]

Not really, ZC is just being safe and either not wanting to build the csv or the URL. No damage is done when it kicks you out for permission.

If I have some time later, I will play with this some more. I've got a customer that I think could use this.

All other thoughts from the gallery are appreciated.

[lat9]

Not really, ZC is just being safe and either not wanting to build the csv or the URL. No damage is done when it kicks you out for permission.

If I have some time later, I will play with this some more. I've got a customer that I think could use this.

All other thoughts from the gallery are appreciated.

Noting that it's not Zen Cart kicking you out with the 1045, it's the site's webhost's MySQL server. While the query can be submitted via the webhost's phpMyAdmin (which has such permission), many webhosts disable the use of INTO OUTFILE clause for security reasons.

[CaroleAs]

A month later, any possible update?
I am not in a rush and it is not an essential feature, but it would be nice to have.