Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

1054 Error on List All Products Page


Sance21

Recommended Posts

I have a working osCommerce store that has all functional pages but one.

http://electroind.com/store/

 

When you click the List of All Products Link underneath the Categories section, you get this error.

http://www.electroind.com/store/allprods.php?osCsid=9771e8e4b4e64c94a873297f74299ca2

 

That links to the allprods.php

 

How do I go about removing the text to that link? Or is there a fix to get this working again?

I've already tried to modify the index.php and advanced_search_result.php to no avail.

Link to comment
Share on other sites

This is the infamous MySQL 5 change in operator precedence (between comma and JOINs). You need to find the PHP code that creates this query:

SELECT s.specials_new_products_price, p.products_id, p.products_model, p.products_price, pd.products_name 
FROM products p, products_description pd LEFT JOIN specials s ON p.products_id=s.products_id 
WHERE p.products_id = pd.products_id AND p.products_status = 1 AND pd.language_id = 1 ORDER BY pd.products_name

and add parentheses to change the precedence:

SELECT s.specials_new_products_price, p.products_id, p.products_model, p.products_price, pd.products_name 
FROM (products p, products_description pd) LEFT JOIN specials s ON p.products_id=s.products_id 
WHERE p.products_id = pd.products_id AND p.products_status = 1 AND pd.language_id = 1 ORDER BY pd.products_name

Link to comment
Share on other sites

This is the infamous MySQL 5 change in operator precedence (between comma and JOINs). You need to find the PHP code that creates this query:

SELECT s.specials_new_products_price, p.products_id, p.products_model, p.products_price, pd.products_name 
FROM products p, products_description pd LEFT JOIN specials s ON p.products_id=s.products_id 
WHERE p.products_id = pd.products_id AND p.products_status = 1 AND pd.language_id = 1 ORDER BY pd.products_name

and add parentheses to change the precedence:

SELECT s.specials_new_products_price, p.products_id, p.products_model, p.products_price, pd.products_name 
FROM (products p, products_description pd) LEFT JOIN specials s ON p.products_id=s.products_id 
WHERE p.products_id = pd.products_id AND p.products_status = 1 AND pd.language_id = 1 ORDER BY pd.products_name

 

Which file would I be able to find this in, index.php?

Link to comment
Share on other sites

I can't find that SQL query anywhere in vanilla osC 2.3.1. Have you added any add-ons, templates, or other new code? Did you add something to get "List of All Products Available Online"? If you did, the query is in there, and it needs updating for MySQL v5.

Link to comment
Share on other sites

I can't find that SQL query anywhere in vanilla osC 2.3.1. Have you added any add-ons, templates, or other new code? Did you add something to get "List of All Products Available Online"? If you did, the query is in there, and it needs updating for MySQL v5.

 

Well, it's an existing store that I was brought in to fix so I'm not sure what, if anything was added. How do I upgrade the store, and would it affect the existing store that is on the site in any way (graphics or info changing) Would the change be seamless?

Link to comment
Share on other sites

It's going to be a problem to maintain that store if neither you nor your client have any idea what version the base code is, or what templates or mods were installed on top of that. It is possible, with some work, to migrate the store's product data to a fresh install of the current version (2.3.1). I wouldn't call it "seamless", but you ought to be able to run the new store in parallel with the old one while you're bring it up (on a copy of the database) and seeing what add-ons/mods need to be installed to duplicate the function of the old store. Then you disable the old store and cut over to the new one. So it says, here on the back of the cereal box... In the long run, that would probably be cleaner than tinkering with the existing one, hoping to figure out what's in there. You can look for the current version number (application_top.php? there are posts around explaining how to do it) and see if there are upgrades (possibly multiple steps) to get to a modern version. Also look around for how to secure your store. If it's not SQL v5-ready, it's probably not 2.3.1 and is unsecured.

 

The nuts and bolts of it, if I were doing it, would be to install a vanilla 2.3.1. Get the schema of the old and new databases to get an idea of what you'll have to add to the .sql backup of the old database before you can import it (or use phpMyAdmin to add/change fields after importing the data). It's possible that a template or mod on the old store will have its own database fields or even tables, so be careful. You'll have to either drop the mod's data, or when adding the mod, suppress changes to the database.

 

This will take some time, so for the short term fix, you'll just have to search the .php files for the SQL query in question. Start by getting a good search tool such as "grep". Look for all JOINs (LEFT and otherwise), and any comma-separated table list to the left of a JOIN must be put in parentheses. You might as well do all of them, rather than fixing just this one now and having to do other ones later. If your store is old enough, you'll find plenty of MySQL v5 and PHP 5 issues to keep you busy, plus the security matters. That's why it would be good to look at moving to 2.3.1 as soon as you can.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...