[done 1.3.7] default_filter.php bug

[cees]

Hello,

This is not a question but a answer on a query error.

I have solved, after long searching, a query error in the de default_filter.php.
In de left join (± row 41) there where also a table presentation (TABLE_PRODUCTS_TO_CATEGORIES p2c) witch not should be done there.

The correct piece is:

// We show them all
$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status = '1', s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status ='1', s.specials_new_products_price, p.products_price) as final_price, p.products_sort_order from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

With kindley regards,

Cees Stoltenkamp

[DrByte]

what version of Zen Cart are you suggesting this applies to?
and what problem are you suggesting this fixes ?

[cees]

Hello

It does not matter, from 1.3.0.* till the most recent version.
In all of them thereis made the same mistake.

The reason for this problem is a newer version of MySQL
Server versie: 5.0.24a-community-nt

This version is somehow stricter then previeus versions in using the JOIN statements, jou can find comments about that on the Internet.

But overall, I think, its a fault.

left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . //til here it's Ok (dont forget to remove the ",")
TABLE_PRODUCTS_TO_CATEGORIES . " p2c
But this last part, belong somewhere else, so I put it where I think it belongs, in the FROM part of the SQL statement.


Greetings Cees

[cees]

Sorry

Forget to say the MySQL error message you then get.

Message:
1054 Unknown column 'p.products_id' in 'on clause'

When I use the newer defaul_filter.php in Zencart 1.3.0.*
Message:
Illegal Access

Cees

[cees]

Hello

After a meeting we decide that the problem not suffisient is solved, there has to be done something with that second part of the LEFT JOIN or it should work.

We now still searching the MySQL for fixing this problem, if there is some other solution let us know

Ok, ok, I get now the solution
The second JOIN uses a wrong id-field
p.products_id = s.products_id must be?
p2c.products_id = s.products_id

This solutions is one we can accept, and we will stop searching now.

But still it is wrong in all versions after 1.3.0.*, we don't understands that it was not a problem before.

Or is the problem NOT solved, in our case it works fine now.

I hope someone can give us the answer about that.

Cees

[cees]

Sorry,

This is realy the last time.
In the latest version the problem IS solved as we suggested.

Problem is solved for Zencart > 1.3.0.*.

Cees