Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Errors Across website MYSQL 5.1 and PHP5


atlasdidshrug

Recommended Posts

Hello,

 

I have a website which is spitting out errors left, right and center.

 

I have implemented many of the fixes online, none have worked.

 

The osc has been heavily modifide as I can tell. I did go in and fix /index.php and instead of p.products_id changed now to p2c.products_id. This fixed the main page. Now the following errors come;

 

Using the search results area I get:

 

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 'where p.products_status = '1' and p.products_id = pd.products_id and pd.language' at line 1

 

select count(distinct p.products_id) as total from ((products p left join products_to_products_extra_fields p2pef on p.products_id=p2pef.products_id) left join manufacturers m on m.manufacturers_id=p.manufacturers_id, products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%drc%' or p.products_model like '%drc%' or m.manufacturers_name like '%drc%' or p2pef.products_extra_fields_value

 

I have tried the /class fix on many other forums. This has not fixed that parituclar error.

 

Going to catalog brings the following error;

 

1054 - Unknown column 'p.products_id' in 'on clause'

 

SELECT s.status, s.specials_new_products_price, p.products_id, p.products_model, p.products_price, p.products_tax_class_id, 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

 

 

 

Lastly manufcators gives the following error too;

1054 - Unknown column 'p2c.products_id' in 'where clause'

 

select count(p.products_id) as total from products p, products_description pd, manufacturers m left join specials s on p2c.products_id = s.products_id where p.products_status = '1' and pd.products_id = p2c.products_id and pd.language_id = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '70'

 

 

 

 

 

 

 

Any help would be wonderful, I have spent many hours on this particular issue and kicking myself now.

Link to comment
Share on other sites

Well, in the first problem, your parentheses () appear to be very unbalanced. Do you understand the principle of what you're trying to do when adding parentheses to an SQL statement, or just copying instructions by rote? The problem being solved is that MySQL 5 had to change the precedence between the comma operator (,) and JOIN commands, to match the SQL definition. Basically, the JOIN (LEFT or otherwise) now binds more tightly to the table immediately to its left, than it does to the rest of the comma-separated list to its left. You need to have () around the entire list to the left of the JOIN, or the "ON" statement won't see any table other than the one immediately to the left.

 

The second case should produce

SELECT s.status, s.specials_new_products_price, p.products_id, p.products_model, p.products_price, 
p.products_tax_class_id, 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

 

The third case should produce

select count(p.products_id) as total from 
(products p, products_description pd, manufacturers m) left join specials s on p.products_id = s.products_id where
p.products_status = '1' and pd.products_id = p2c.products_id and pd.language_id = '1' and 
p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '70'

I changed p2c back to p, which should be what it was originally.

 

You have to understand why you need to make these changes, and can't just blindly try to do it by rote.

 

Have you thought about installing osC 2.3.3 and migrating the product data to it? That might save you a lot more work.

Link to comment
Share on other sites

Hello,

 

This is not my website and helping someone out. The original server died and had to be reinstalled. With that this particular website had problems.

 

Their website was heavily customized, so creating a new one would probably take some time.

Link to comment
Share on other sites

Try putting everything back as it was, and let someone who knows what they are doing sort the errors. It looks like you may be making things worse, not better.

REMEMBER BACKUP, BACKUP AND BACKUP

Link to comment
Share on other sites

Try putting everything back as it was, and let someone who knows what they are doing sort the errors. It looks like you may be making things worse, not better.

 

Hello Steve,

 

I'm not sure about making it worse, with the patch I did make the owner could take orders and part of the website worked. I've placed the website back as it was and now the major error across the board are;

 

1054 - Unknown column 'p.products_id' in 'on clause'

 

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 = '10'

 

Along with clicking on the catalog;

 

1054 - Unknown column 'p.products_id' in 'on clause'

 

SELECT s.status, s.specials_new_products_price, p.products_id, p.products_model, p.products_price, p.products_tax_class_id, 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

 

search brings;

 

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 'where p.products_status = '1' and p.products_id = pd.products_id and pd.language' at line 1

 

select count(distinct p.products_id) as total from ((products p left join products_to_products_extra_fields p2pef on p.products_id=p2pef.products_id) left join manufacturers m on m.manufacturers_id=p.manufacturers_id, products_description pd left join specials s on p.products_id = s.products_id, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%sex%' or p.products_model like '%sex%' or m.manufacturers_name like '%sex%' or p2pef.products_extra_fields_value like '%sex%') )

Link to comment
Share on other sites

You'll have to dig up where the PHP code is that creates these queries (the error message should give you a hint). See below for where the () should end up.

 

1054 - Unknown column 'p.products_id' in 'on clause'

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 = '10'

 

1054 - Unknown column 'p.products_id' in 'on clause'

SELECT s.status, s.specials_new_products_price, p.products_id, p.products_model, p.products_price, 
p.products_tax_class_id, 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

 

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 'where p.products_status = '1' and p.products_id = pd.products_id and 
pd.language' at line 1

select count(distinct p.products_id) as total 
from ((products p left join products_to_products_extra_fields p2pef on p.products_id=p2pef.products_id) left join manufacturers m on m.manufacturers_id=p.manufacturers_id, products_description pd) left join specials s 
on p.products_id = s.products_id, categories c, products_to_categories p2c 
where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '1' and 
p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and (pd.products_name like '%sex%' or 
p.products_model like '%sex%' or m.manufacturers_name like '%sex%' or p2pef.products_extra_fields_value like '%sex%')

 

Let me know if you can't locate where the PHP code is, and I'll try to help out.

Link to comment
Share on other sites

  • 2 weeks later...

Hello,

 

I have lost my oscommerce admin panel, and now my home page comes up in the original web address which contained my admin program. Has this happened to anyone? Can anyone help me fix this please?

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...