Need to fix SQL errors in v1.1.4d

[Brian1234]

I tacked this onto an older thread but didn't receive a reply.... trying here...

I guess the first thing that I see in the errors is that p.products_id is not found, but the field exists and seems to be OK. Also, I see that the error message comes from adodb-errorhandler.inc.php ... how can I tell where the code is that actually generated the error?

---- Original post ----

Due to continuing problems getting the new version installed from scratch (mainly a mostly unresponsive ISP) and the need to get something back up and running, I am hoping someone here can help with the errors I am getting in v1.1.4d, likely due to MySQL upgrades or perhaps a corrupted file.(?) Might need to do some SQL query tweaks.

It is *almost* working but no products show in the store's user interface under categories. The products DO show in the admin interface. Here are the errors:


Go to main store (contains a categories sidebox):
NO ERRORS


Go to a category in main store's category sidebox (no products display within):
in /xyz/store.old/includes/classes/adodb/adodb-errorhandler.inc.php on line 139
PHP Fatal error: mysql error: [1054: Unknown column 'p.products_id' in 'on clause'] in EXECUTE("select count(p.products_id) as total from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id, products_to_categories p2c left join 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 = '1' and p2c.categories_id = '56'")


Admin: going to Categories / Products - Top:
in /xyz/store.old/includes/classes/adodb/adodb-errorhandler.inc.php on line 139
where p.products_id = pd.products_id
from products p, products_description pd, products_to_categories p2c
p.products_quantity_order_max
p.product_is_free, p.product_is_call, p.products_quantity_mixed,
p.products_quantity_order_min, p.products_quantity_order_units, p.products_priced_by_attribute,
p.products_status, p.products_model,
p.products_last_modified, p.products_date_available,
p.products_image, p.products_price, p.products_date_added,
PHP Fatal error: mysql error: [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 '-10, 10' at line 13] in EXECUTE("select p.products_id, pd.products_name, p.products_quantity,
[yes, that is the end of this error message]


Admin: Going to same category as tried in Store (products display OK here):
NO ERROR


Admin: Going to product edit:
NO ERROR


Interesting how the store itself has an error when going *into* a category, but the admin interface has one when showing the categories top but not the products.


HELP appreciated, and I do have some SQL buddies that can help if we get on the right path.

[yellow1912]

Wouldnt it be better to just upgrade the store? Since you may have many hidden errors which you may not find now (but your customers will encounter them along the road)

Also, there are a number of serious bugs needed to be fixed by upgrading your cart.

[DrByte]

You have to search for pieces of the SQL query that it's quoting in order to find the PHP file where it was initiated from.

I suspect your first one comes from /includes/modules/pages/index/main_template_vars.php on line 145:[FONT="Courier New"]

$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 from (" . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c 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 . "'";

[/FONT]

And if you're using a newer version of MySQL, you'll need bandage things by putting parentheses around the table names .... ie: after the word "from" add a "(" and before the word "where" add a ")", as shown in the above code snippet.

You could probably get around these issues if your host were to change their MySQL settings so that the sql-mode does NOT include the "STRICT_TRANS_TABLES" option. But, if as you say, your host is being uncooperative, that's probably not an option for you.

Your second SQL query that you mentioned is related to a bug in your old code. A fix was posted here:
http://www.zen-cart.com/forum/showpo...05&postcount=4




And ... test test test test test !!!

[Brian1234]

DrByte,

Thanks SO much for trying. I think I have to stop kicking a dead horse here.

I don't think that was the right line but I made the change to all the queries in that file and still get the same error (this time with parens included, so I know the change took effect and they're where you said they should be).

An SQL buddy says:
> ok, the problem is that you're mixing explicit and implicit join syntax
> mysql at least historically gets the precedence of joins completely wrong, if they've fixed that in newer versions it would explain the breakage

Assuming there are probably MANY other places that would need fixing, I've decided to give up on the old store and see again if I can get the new install going, without much help from the ISP, so I'll probably have some basic questions coming up about that.

[DrByte]

Assuming there are probably MANY other places that would need fixing ...

Yes, there are many other places that have been fixed in each of the 15 versions that have been released since then :)