Problems with Sort by Price

I have a problem. I believe that the Sort by price feature
used to work but it no longer does. We are running version
1.3.8a with security upgrades. MySql version 5.0.91
PHP version 5.2.11 .

In the Zen Cart Admin I have done the following:

Reset Master Categories ID
Reset Price Sorter

In phpMyAdmin :

I have verified that the master_categories_ id and
products_price_sorter values are set in the products table
of the DB.

What might I have done to cause the sort by price
feature to not work?

Thanks for any help. If you need more info please
feel free to contact me.

Are you getting errors? Perhaps you have an URL that we could peek at to see what is happening ... :smile:

NOTE: there are numerous Known Bug Fixes for v1.3.8 ...
http://www.zen-cart.com/forum/showthread.php?t=82619

Plus a good time to review all of the Security Patches and Updates ...
http://www.zen-cart.com/forum/showthread.php?t=131115

NOTE: all of these and more are in the current v1.3.9 release on our home page ...

linda,

No, I am not getting errors. Right now the server response time
seems to be really bad, but here is a product page you can look at:

http://www.postlynx.com/index.php?main_page=index&cPath=1093

Thanks for any help you can give.

By the way, I THINK I have all of the security patches and
updates, for 1.3.8.a, installed.

John

Check that you have all of the Known Bug Fixes that mention master_categories_id or categories ...

Also be sure that the update by price is completing when you run it in the Store Manager ...

Linda,

I have checked that I have bug and security updates in place, to
the best of my knowledge.

I have updated the product price sorter several times.

The thing I find most unsettling is that the sort by price seems to work in one or two categories, but not in most of them. Since I can
not imagine that the code performing the sort function works
differently for different categories, I can only assume that there
is something in the database that is causing the problem.

I have looked at the database, using phpMyAdmin, and the
product table shows that the products_price_sorter and
master_categories_id fields have values. However, one thing
I see that is peculiar is that some products have the
manufacturers_id = NULL and others have the
manufacturers_id = 0 (zero) . Might this be having an adverse
effect on the sort function?

If you want it, I can send a copy of the product table.

Any suggestions ?

YIKES ! I just found something that disturbs me. One product
in the table has a master_categories_id of 0 (zero). This
was one of the earliest products that I created manually (prior
to using EasyPopulate).

I am not a mySql guru but I am sure that there is a way to
delete that product id.

Can somebody help me with the mySql code? Will I have
to delete items from multiple tables?

I don't know if this DB error has anything to do with the
sort problem or not.

If you have Products in the products table with the master_categories_id of 0 and then run the Reset Master Categories ID and still have Products in the products table with the master_categories_id of 0 then you have bad data ...

NOTE: if using Linked Products, running the Reset Master Categories ID can be a problem as the lowest value categories_id will be used for the master_categories_id ...

You can use the Multiple Categories Link Manager and set an entire Category of Products to use that Category as the categories_id for the master_categories_id or set them individually by Product ...

Well, I have tried everything I could think of and still have no
luck.

I have NO linked products.

I found one product that had an error and deleted it.
I reloaded the entire product table, 11000+ products,
using EasyPopulate.

I reset the master categories ID and then updated the
price sorter. Same results. Some categories sort properly
but most do not.

I give up !

Thanks for the suggestions given.

Could you search the file:
/includes/functions/functions_lookups.php

And find the function that starts with:

  function zen_get_products_category_id($products_id) {

and paste the contents here ...

Also look for the function:
/admin/includes/functions/general.php

and find the function that starts with:

  function zen_get_products_category_id($products_id) {

and paste it here as well ...

Linda,

Okay. I loaded my website on to my Linux machine running
LAMPP and the sort function works fine, so I guess your statement
that the DB is corrupt is the likely culprit.

The question is, how do I repair the DB ? I see that there is a
MySql function called mysqlcheck which has an auto-repair
option. It looks like it needs to be ran on each table that one
wishes to check/repair. Can you give me a heads up on
which of the tables might be corrupted and causing the sort
problem? I don't want to have to run the function on every table
unless necessary.

I know this is NOT a MySql forum so if you choose not to answer
I understand.

Thanks for all your help.

Corrupt data could be in a number of tables ...

There are two kinds of corrupt data ... damaged data that the Repair in phpMyAdmin might by able to fix ... and the corrupt data where you have bad records of data in the database tables and these need to be checked through to try to locate the bad records ...

Checking for bad records can be hard as there are a lot of checks to be done on the database table records ...

If you have a good copy of your database, then that can just be reloaded ...

If you do not, and do not want to manually re-add all of your data, then it might be worthwhile having someone go through your database table records and see if they can spot the problems and clean them up ...

You might need someone to do this kind of clean up for you and you might post in the Commercial Help Wanted to see if anyone is available to help you ...

Linda,

Here are the two functions, in the order requested.

From includes/functions/functions_lookups.php:

  function zen_get_products_category_id($products_id) {
    global $db;

    $the_products_category_query = "select products_id, categories_id from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id = '" . (int)$products_id . "'" . " order by products_id,categories_id";
    $the_products_category = $db->Execute($the_products_category_query);

    return $the_products_category->fields['categories_id'];
  }

From admin/includes/general.php:

  function zen_get_products_category_id($products_id) {
    global $db;

    $the_products_category_query = "select products_id, categories_id from " . TABLE_PRODUCTS_TO_CATEGORIES . " where products_id = '" . $products_id . "'" . " order by products_id,categories_id";
    $the_products_category = $db->Execute($the_products_category_query);

    return $the_products_category->fields['categories_id'];
  }

Thanks, John

Linda,

I don't know if this is significant, or not, but when I am in the
admin, the sort by price works correctly. It is only in the store that
it does not.

Also, as I stated earlier, some categories do sort correctly in the store. Seems to be categories that have very few products that
work, but not ALL categories with only a few products. Seems a bit strange to me.

Also, I did a check on the two MySql tables that I thought would be relevant to the problem ( zen_products and zen_products_to_categories ) and I do not see anything out of order.

It would seem to me that if it were a database corruption issue that the problem would also manifest itself in the admin.

I really appreciate your taking the time to help me on this.

John

Linda,

By the way, I did reload all my product data. No help.
I could, I suppose restore a backup. The problem is that I
am not sure when the problem started and I have not kept backups
that are older than about 2 months. My BAD ?

I had a thought about something that I could try. Assuming that
perhaps some PHP code might be corrupt, if I knew which code
might be culpable I could try reloading the code from my localhost
up to the live site. The reason for that is that the sort functions
appear to work correctly on the localhost. Just a thought!

Again, thanks for all your help. When we get this fixed I will talk to the store owner and see if we can't send something to the TEAM !
You folks are great !

John

If you look at those two functions in the Known Bug Fixes you have the wrong ones ...

You need to fix those functions to what is posted for the bug fix ... and it leads me to think you have other bugs not fixed as well ...

Linda,

Thank you. I will go through the list of bug fixes and hopefully fix
all of the bugs.

I am still somewhat confused as to why the sort works on my
localhost server but not on the live server, unless the bug fixes
had been incorporated into the 1.3.8a download I fetched last
week.

Again, thanks for your help.

John

Linda,

I went through the list of bugs and security patches (about 5
pages of fixes and bugs) as per the links you sent me, and updated the files.

The problem still exists so I guess there is most likely a DB
error. I will use the mysqlcheck function to look for
corrupt tables in the DB and will let you know the outcome.

Again, thanks for all your help.

John

Linda,

I checked the MySql tables using the CHECK TABLE tablename
function and there do not seem to be any tables that are corrupt.
I believe that the CHECK TABLE function looks at the keys for
each table to ensure that links to other tables are not broken, as
well as other checks.

This is really puzzling. As the sort by price works in the admin I have to assume (maybe a BAD IDEA) that the DB is okay.
That leaves me with trying to find out what PHP code might be
causing the problem.

As I stated earlier, I made all of the code changes as outlined
in the link you sent

http://www.zen-cart.com/forum/showthread.php?t=82619

so unless I goofed when making the code updates then there is
still something not quite right. I am going to keep after this until
I find the solution.

Thanks again for you help. I will let you know when I find the
problem.

John