How to sort products by "non-standard" categories

Hi all, thanks in advance for any tips or clues I can use to solve this problem.

So, I'm nearing the end of my first zen cart shop (1 month development), now it's on a windows server, and one of the client's requirements is to be able to sort via non-standard fields, ie. these fields don't exist in the product table.

The site is

Basically - I'm running a 3 product-per-column display, and have static links above the products that allow the user to sort by:

name (asc)
price low to high
price high to low
best seller *
new *
sale *

So, the first 3 fields are super easy (sort=2a,2d etc), but the last 3 are NOT. I know that "new" is in the products table, since there is a field for when it was entered, but since it's not part of the product list ordering in the admin, well, I'm screwed.

Also I need to check and cross refernce the sale tables and products ordrered tables to fetch the ID's of the products that are best sellers in this category.

I've read through this thread http://www.zen-cart.com/forum/showthread.php?t=56118&highlight=product+listing+sort&page=4 and others, and have not found anyone with the same exact problem and solution.

I'm pretty good at php, but not too familiar in changing around ZC's core php files to get the output (array of products in the proper order) that I need.

So, what're my options, and what files (class files too?) do I need to change? THANKS IN ADVANCE TO THIS WONDERFUL COMMUNITY!:hug:

You would need on the select statement to include additional fields:
products_ordered
products_date_added

I am not sure what you mean by Sale vs sorting by Price which is already done with the products_price_sorter field ...

The "Sale" are the products that are on discount / special pricing, so if the user clicks on that, the order of the products would show the sale stuff first, then the regular items after that.

So, I'm having difficulty fudging around in the /includes/index_filters/default_filter.php - I think I'm close to being able to sort via the date added, but for some reason the system keeps adding an extra "order by p.products_sort_order, pd.products_name" to the end of the $listing_sql string.

So what I'm doing, is adding my own sort=7 and sort=8 to the URL, which I hope will denote that sort=7 is for the sale, and sort=7 is for the newly listed items ordering. I'm saving sort=9 for the best seller, which I think could be much more complicated than the first two.

So, you said products_ordered is part of the table? WOW - I didn't realize that, that would make it much easier since I don't have to check the products_order table to find the IDs sold, very nice - that should save me some time.

So, is that the best way of handling this, by editing the index_filters/default_filters.php file?

If you want a URL and password to see this site, let me know - thanks again!

All of this can be developed but there is quite a bit of coding involved to manage the display information of each field added ...

You will notice that there are several places where the select statement is built and each needs to be addressed ...

Then there are sections to manage the headers or displayed information as well as sections to manage the actual display area ...

It sounds like you are also using an add-on for displaying the data so this can complicate things a bit more ...

Find one set of information that already exists then you need to "clone" or duplicate the pattern throughout the sections of code ...

I would have to write this in order to show you all of the changes required for this ...

I'm happy to report that the 'custom product list ordering' is working fine now. I only had to edit where the SQL statement is built. Though I'm not sure where the "Limit 0, X" is added. For now, it works - and I only had to edit the main_template_vars (to add the products_ordered and products_date_added fields to the query) and then the index_filters/default_filter.php to accomodate my custom "sort=[7-9]d" GET variables and insert the proper 'order by' statement there according tot he sort variable.

My additional sub-question is, when (or if) I upgrade to the next zen cart version, all these files will be clobbered, right - since they're not in an overidden template directory?\

Thanks Ajeh.

Btw, you really saved my life when you mentioned there was a products_ordered field in the products table, that rocked.

Be sure to keep backups of all of your changes as most will be wiped out on an upgrade ...

Those that could be done with the overrides should be fine ...