osC and MySQL 5 - 1054 errors

[MrPhil]

osCommerce was written for earlier versions of MySQL (pre version 5), which did not completely conform to SQL standards. MySQL version 5, now commonly available from hosting services, was changed to conform with the SQL standard with regards to the relative precedence of the comma (,) and JOIN operators. Unfortunately, this breaks osC code. Fortunately, the fix is easy.

 

SELECT .... FROM table1 t1, table2 t2 LEFT JOIN ...

 

in MySQL 4 and earlier behaves as though there were parentheses around the list (comma and JOIN are the same precedence). In MySQL 5, the code needs explicit parentheses, because the JOIN takes precedence over the comma operator:

 

SELECT .... FROM (table1 t1, table2 t2) LEFT JOIN ...

 

Otherwise, only table2 will participate in the JOIN, and any fields from table1 used in the ON clause of the JOIN will be reported as missing (1054 error).

 

The following changes are needed for MySQL version 5. A few may not be absolutely necessary, as the "missing" table isn't used in the ON clause, but I included them anyway for completeness. As far as I know, they should be safe for MySQL 4, but are not required. PLEASE post if you find any errors (or missing fixes) here, or if it does not work with MySQL 4 or earlier.

 

This list is for the "vanilla" osCommerce 2.2 RC2a install. Line numbers may be slightly different in earlier versions. Please post any additions you have found for various osC contributions and add-ons.

 

===================

catalog/checkout_process.php at 204-206 find ==>

 

$attributes_query = "select popt.products_options_name, poval.products_options_values_name, pa.options_values_price, pa.price_prefix, pad.products_attributes_maxdays, pad.products_attributes_maxcount , pad.products_attributes_filename

from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_OPTIONS_VALUES . " poval, " . TABLE_PRODUCTS_ATTRIBUTES . " pa

left join " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad

 

change to ==>

 

$attributes_query = "select popt.products_options_name, poval.products_options_values_name, pa.options_values_price, pa.price_prefix, pad.products_attributes_maxdays, pad.products_attributes_maxcount , pad.products_attributes_filename

from (" . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_OPTIONS_VALUES . " poval, " . TABLE_PRODUCTS_ATTRIBUTES . " pa

) left join " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad

 

===================

catalog/index.php at 186-188 find ==>

 

// 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, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, 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 left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

}

 

change to ==>

 

// 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, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, 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 left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";

}

 

===================

catalog/admin/tax_rates.php at 94-96 find ==>

 

<?php

$rates_query_raw = "select r.tax_rates_id, z.geo_zone_id, z.geo_zone_name, tc.tax_class_title, tc.tax_class_id, r.tax_priority, r.tax_rate, r.tax_description, r.date_added, r.last_modified from " . TABLE_TAX_CLASS . " tc, " . TABLE_TAX_RATES . " r left join " . TABLE_GEO_ZONES . " z on r.tax_zone_id = z.geo_zone_id where r.tax_class_id = tc.tax_class_id";

$rates_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $rates_query_raw, $rates_query_numrows);

 

change to ==>

 

<?php

$rates_query_raw = "select r.tax_rates_id, z.geo_zone_id, z.geo_zone_name, tc.tax_class_title, tc.tax_class_id, r.tax_priority, r.tax_rate, r.tax_description, r.date_added, r.last_modified from (" . TABLE_TAX_CLASS . " tc, " . TABLE_TAX_RATES . " r) left join " . TABLE_GEO_ZONES . " z on r.tax_zone_id = z.geo_zone_id where r.tax_class_id = tc.tax_class_id";

$rates_split = new splitPageResults($HTTP_GET_VARS['page'], MAX_DISPLAY_SEARCH_RESULTS, $rates_query_raw, $rates_query_numrows);

 

===================

catalog/includes/classes/order.php at 141-143 find ==>

 

 

$customer_address_query = tep_db_query("select c.customers_firstname, c.customers_lastname, c.customers_telephone, c.customers_email_address, ab.entry_company, ab.entry_street_address, ab.entry_suburb, ab.entry_postcode, ab.entry_city, ab.entry_zone_id, z.zone_name, co.countries_id, co.countries_name, co.countries_iso_code_2, co.countries_iso_code_3, co.address_format_id, ab.entry_state from " . TABLE_CUSTOMERS . " c, " . TABLE_ADDRESS_BOOK . " ab left join " . TABLE_ZONES . " z on (ab.entry_zone_id = z.zone_id) left join " . TABLE_COUNTRIES . " co on (ab.entry_country_id = co.countries_id) where c.customers_id = '" . (int)$customer_id . "' and ab.customers_id = '" . (int)$customer_id . "' and c.customers_default_address_id = ab.address_book_id");

$customer_address = tep_db_fetch_array($customer_address_query);

 

change to ==>

 

 

$customer_address_query = tep_db_query("select c.customers_firstname, c.customers_lastname, c.customers_telephone, c.customers_email_address, ab.entry_company, ab.entry_street_address, ab.entry_suburb, ab.entry_postcode, ab.entry_city, ab.entry_zone_id, z.zone_name, co.countries_id, co.countries_name, co.countries_iso_code_2, co.countries_iso_code_3, co.address_format_id, ab.entry_state from (" . TABLE_CUSTOMERS . " c, " . TABLE_ADDRESS_BOOK . " ab) left join " . TABLE_ZONES . " z on (ab.entry_zone_id = z.zone_id) left join " . TABLE_COUNTRIES . " co on (ab.entry_country_id = co.countries_id) where c.customers_id = '" . (int)$customer_id . "' and ab.customers_id = '" . (int)$customer_id . "' and c.customers_default_address_id = ab.address_book_id");

$customer_address = tep_db_fetch_array($customer_address_query);

 

===================

catalog/includes/modules/payment/chronopay.php at 225-228 find ==>

catalog/includes/modules/payment/paypal_standard.php at 229-232 find ==>

catalog/includes/modules/payment/sofortueberweisung_direct.php at 227-230 find ==>

catalog/includes/modules/payment/worldpay_junior.php at 224-227 find ==>

 

$attributes_query = "select popt.products_options_name, poval.products_options_values_name, pa.options_values_price, pa.price_prefix, pad.products_attributes_maxdays, pad.products_attributes_maxcount , pad.products_attributes_filename

from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_OPTIONS_VALUES . " poval, " . TABLE_PRODUCTS_ATTRIBUTES . " pa

left join " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad

on pa.products_attributes_id=pad.products_attributes_id

 

change to ==>

 

$attributes_query = "select popt.products_options_name, poval.products_options_values_name, pa.options_values_price, pa.price_prefix, pad.products_attributes_maxdays, pad.products_attributes_maxcount , pad.products_attributes_filename

from (" . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_OPTIONS_VALUES . " poval, " . TABLE_PRODUCTS_ATTRIBUTES . " pa

) left join " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad

on pa.products_attributes_id=pad.products_attributes_id

 

-------------------

catalog/includes/modules/payment/chronopay.php at 413-416 find ==>

catalog/includes/modules/payment/paypal_standard.php at 473-476 find ==>

catalog/includes/modules/payment/sofortueberweisung_direct.php at 444-447 find ==>

catalog/includes/modules/payment/worldpay_junior.php at 410-413 find ==>

 

$attributes_query = "select popt.products_options_name, poval.products_options_values_name, pa.options_values_price, pa.price_prefix, pad.products_attributes_maxdays, pad.products_attributes_maxcount , pad.products_attributes_filename

from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_OPTIONS_VALUES . " poval, " . TABLE_PRODUCTS_ATTRIBUTES . " pa

left join " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad

on pa.products_attributes_id=pad.products_attributes_id

 

change to ==>

 

$attributes_query = "select popt.products_options_name, poval.products_options_values_name, pa.options_values_price, pa.price_prefix, pad.products_attributes_maxdays, pad.products_attributes_maxcount , pad.products_attributes_filename

from (" . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_OPTIONS_VALUES . " poval, " . TABLE_PRODUCTS_ATTRIBUTES . " pa

) left join " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad

on pa.products_attributes_id=pad.products_attributes_id

[MrPhil]

These same problems show up in a number of mods/add-ons/contributions out there. When you encounter one, please post the fix here, giving the contribution name and version, file name, and line. Also indicate whether you're fixing something in the patch file itself, or in the modified file after applying the contribution.

 

If a contribution is still being actively maintained, please bug the author about fixing this. When they release the update, they should note here that the contribution has been updated and no longer needs a manual fix.

[MrPhil]

A further note:

 

Sometimes a botched code modification or contribution installation can result in code 1054, rather than the JOIN problem. Some contributions/add-ons/mods require changes to the database, such as adding a new field. If you skip that step for some reason, you will be missing the field and will get a 1054 error when the PHP code tries to refer to this field. Read all instructions carefully to see if you are supposed to run some MySQL queries to add fields or otherwise modify the database!

[♥geoffreywalton]

I'd be very careful and not just change the code as above because the "corrected" code above might be missing extra fields that you have added as a result of installing contributions.

 

The way to fix these errors is described in the first post and for another explanation you could try this contribution

 

http://addons.oscommerce.com/info/4654

 

There are also some more SQL5 compatibility updates & patches at http://www.oscommerce.com/ext/update-20060817.txt

[sakkiotto]

Hi,

 

I have the same problem only if some customers login.

I don't know the difference, but some account work fine and some don't work with error like:

 

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

 

select pd.products_id, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from products p, products_description pd left join specials s on p.products_id = s.products_id where pd.products_id = '3468' and p.products_id = pd.products_id and pd.language_id = '4' order by products_name

 

[TEP STOP]

 

I fixed index.php page with () before "left join" but still don't work.

 

Any idea for help me?

 

Thanks

[MrPhil]

That doesn't look like "vanilla" SMF -- do you have any contributions/mods/add-ons installed? You will need to find the PHP code that produces this SQL query, and change it so that the query is

select pd.products_id, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from (products p, products_description pd) left join specials s on p.products_id = s.products_id where pd.products_id = '3468' and p.products_id = pd.products_id and pd.language_id = '4' order by products_name

 

I think if you scan your .php files for pd.products_id you should be able to zero in on it pretty quickly. Once you've successfully patched it, please report back here what contribution, file, and line, so that others may patch their copies. You may want to go ahead and scan your .php files for join and pick up other cases of this.

[MrPhil]

I'd be very careful and not just change the code as above because the "corrected" code above might be missing extra fields that you have added as a result of installing contributions.

 

In that case, I would hope that users would be bright enough to hand patch their code (adding parentheses) rather than cutting and pasting the "corrected" code. If they can't be bothered to check that the code they're replacing is an exact match, then they have no business running a Web site.

 

The way to fix these errors is described in the first post and for another explanation you could try this contribution

 

http://addons.oscommerce.com/info/4654

 

The statement made there that 20060817 is completely patched is obviously incorrect.

 

There are also some more SQL5 compatibility updates & patches at http://www.oscommerce.com/ext/update-20060817.txt

 

Up through 2.2 RC2a, there are no MySQL 5 patches, at least, none for this JOIN problem. 20060817 is completely unpatched. There are a number of PHP 5 patches from MS2 through RC2a, but no MySQL 5 that I know of.

[sakkiotto]

That doesn't look like "vanilla" SMF -- do you have any contributions/mods/add-ons installed? You will need to find the PHP code that produces this SQL query, and change it so that the query is

select pd.products_id, pd.products_name, pd.products_description, p.products_image, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from (products p, products_description pd) left join specials s on p.products_id = s.products_id where pd.products_id = '3468' and p.products_id = pd.products_id and pd.language_id = '4' order by products_name

 

I think if you scan your .php files for pd.products_id you should be able to zero in on it pretty quickly. Once you've successfully patched it, please report back here what contribution, file, and line, so that others may patch their copies. You may want to go ahead and scan your .php files for join and pick up other cases of this.

 

Yes, I have a lot of contributions installed.

 

If I scan pd.products_id I found it 138 times on all files.

 

Do you know why only if some users login I have this error?

Maybe some problem in the basket file?

[sakkiotto]

Ok I found the problem.

 

The contribution wishlist don't work fine.

If I delete the wishlist lines of those customers he can login.

 

Now I'm looking for the solution to fix the problem without lost the wishlist products...

[sakkiotto]

filled :D

 

in catalog and in includes/boxes/ the file wishlist.php have left join, just add ( after from and ) before left join.

[Guest]

Hi

 

It's been a while since I dabbled in the code for my shop which has been running happily on an ancient version for quite some time, so bear with me.

 

My hosts have just upgraded so I find myself with this problem. I opened default.php (yes, I know!) and found several instance of left join - but I'm not sure which I should change, or if I should change them all. I changed the one that the original fix seemed to indicated I should change but it made no difference.

 

I have the following:

 

// We are asked to show only a specific category
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c  left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . $languages_id . "' and p2c.categories_id = '" . $HTTP_GET_VARS['filter_id'] . "'";

 

and

 

// 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, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . $languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . $HTTP_GET_VARS['manufacturers_id'] . "'";

 

and

 

// We are asked to show only specific catgeory
	$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c  left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . $HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . $languages_id . "' and p2c.categories_id = '" . $current_category_id . "'";

 

then

 

// 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, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, 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 = '" . $languages_id . "' and p2c.categories_id = '" . $current_category_id . "'";

 

which has two in. This was the one that was most like the one I was told to change so I changed it to:

 

// 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, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, 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 = '" . $languages_id . "' and p2c.categories_id = '" . $current_category_id . "'";

 

but it did nothing.

 

The specific error I am getting is:

 

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

select count(*) 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'

[TEP STOP]

[♥ecartz]

You should look for a query that starts SELECT count(*). That query will look something like

tep_db_query("select count(*) 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 = '" . $language_id . "' and p2c.categories_id = '" . $category_id . "'");

Change to

tep_db_query("select count(*) 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 p2c.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 = '" . $language_id . "' and p2c.categories_id = '" . $category_id . "'");

Note how the left join of p2c to s now joins on p2c.products_id and s.products_id rather than p.products_id and s.products_id.

 

The query is counting the number of products in this language for this category and might be in a function rather than in the page itself. So check out the tep_ functions that might have queries in them.

[web-project]

No error on the latest mysql, it's mean something wrong with your extra modification....

[MrPhil]

As ecartz showed, it may be possible in some cases to restructure the SQL query so that the missing table definitions aren't needed in the "on" clause. However, I would suggest going ahead and putting parentheses around table lists between FROM and JOIN, as described in the first append above. It would be easier and much less error-prone than trying to restructure to use different tables.

 

As I said earlier, the list of changes are not necessarily complete, as earlier versions of osCommerce may be different, and various contributions may add their own SQL queries which need to be fixed. However, the basic principle is still the same: comma-separated lists of tables on the left side of a JOIN need to be wrapped in parentheses, because of the change in operator precedence. JulieCSM -- I must emphasize that you need to understand the principles involved so that you can find where in the code the changes need to be made, and why they need to be made, and how to make them. In other words, you can't just do this by rote, matching up patterns and blindly making suggested changes.

[satish]

p2c left join

these queries that were supported before mysql 5 are no more supported.

Look into Mysql5 patches and apply the solution.

 

Satish

[dstebo]

Server where I have an oscommerce store was amended version of MySQL 4.2 for 5, to run it in MySQL 5 the following error occurs:

 

1054 - Unknown column 'p.products_id' in 'on clause'
select p.products_id, p.frete_gratis, pd.products_name, p.products_image, IF(s.status, s.specials_new_products_price, p.products_price) as products_price from products p right join vitrine vi on vi.vit_idproduct = p.products_id, products_description pd, categories c, products_to_categories p2c left join specials s on p.products_id = s.products_id where categories_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 p.products_status = '1' order by vi.vit_order limit 9

 

 

This query:

 

$new_products_query = tep_db_query("select p.products_id, pd.products_name, p.products_image, IF(s.status, s.specials_new_products_price, p.products_price) as products_price from " . TABLE_PRODUCTS . " p right join vitrine vi on vi.vit_idproduct = p.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where categories_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . $languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and p.products_status = '1' order by vi.vit_order limit " . MAX_DISPLAY_NEW_PRODUCTS);

 

I tried unsuccessfully to change someone can help me ...

[Shannonw]

The following is an error message one of my customers is getting. I'm not sure where to go to fix this... Can someone point me in the right direction?? Thanks!

 

1054 - Unknown column 's.public_flag' in 'where clause'

 

select count(*) as total from orders o, orders_status s where o.customers_id = '14' and o.orders_status = s.orders_status_id and s.language_id = '1' and s.public_flag = '1'

 

[TEP STOP]

[davidell]

I'm almost positive the following code is where I'm getting the 1054 - Unknown column 'p.products_id' in 'on clause' - it wouldn't suprise me though if I was way off with my thinking :huh:

 

in product_info.php

 

line 79 (for me)

 

  } else {    $product_info_query = tep_db_query("select p.products_id, pd.products_name, pd.products_description, p.products_model, p.products_quantity, p.products_image, pd.products_url, p.products_price, p.products_price1, p.products_price2, p.products_price3, p.products_price4, p.products_price5, p.products_price6, p.products_price7, p.products_price8, p.products_price1_qty, p.products_price2_qty, p.products_price3_qty, p.products_price4_qty, p.products_price5_qty, p.products_price6_qty, p.products_price7_qty, p.products_price8_qty, p.products_qty_blocks, p.products_tax_class_id, p.products_date_added, p.products_date_available, p.manufacturers_id from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd where p.products_status = '1' and p.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "'");

 

I've searched the entire site for the left join fixes... still stumbling with the 1054 problem, anyone have any ideas?

[MrPhil]

As discussed in your other post on the subject, this is obviously the wrong code. No JOIN, no ON clause.

[davidell]

MrPhil, is this a response to my question? Just curious as I don't recall posting anywhere else regarding this error... if so, please can you elaborate on the No JOIN, no ON clause?

[MrPhil]

Ah, maybe I saw someone else's post the same day with almost the exact same wording, and thought it was yours.

 

If you look at the query whose code you posted, there is no JOIN in there (with no ON clause). The query in question is coded somewhere else. The error message you got should have contained the entire SQL query in question (and probably the file and line number), which would give an idea of where it came from.

 

1054s are not caused solely by the infamous "JOIN" problem. If you have a typo in a query (say, a misspelled field name), or forgot to add a new field for an add-on, you can also get a 1054. You say that the error message mentions an 'on clause', so it probably is a case where you need to add parentheses around a list (comma-separated) of tables to the left of a JOIN of any kind (not just LEFT JOINs).

 

Download your entire osC store to your PC, and search for "join" and "JOIN". I recommend getting a tool named "grep" to do the searching -- it will make it much easier. Look to the left of each "join" found (back to the FROM keyword), and if there are commas (separating table names), you need to add a level of parentheses. Not all these will be absolutely necessary, but they won't hurt.

[davidell]

Thanks for responding MrPhil, it's very much appreciated.

 

So I've used Dreamweaver to search for "join" and it's returned 101 instances, however 46 of these are in external javascripts so that limits it to 55 instances

 

The first 4 instances are found (no suprises) in advance_search_result.php

 

So I saw your other post but I'm uncertain where my ()'s go... :huh:

 

  $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

 if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {
   if (!tep_session_is_registered('customer_country_id')) {
     $customer_country_id = STORE_COUNTRY;
     $customer_zone_id = STORE_ZONE;
   }
   $from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id left join " . TABLE_ZONES_TO_GEO_ZONES . " gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "')";
 }

 

Would become?

 

 $from_str = "from " ((((. TABLE_PRODUCTS . " p) left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left) join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

 if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {
   if (!tep_session_is_registered('customer_country_id')) {
     $customer_country_id = STORE_COUNTRY;
     $customer_zone_id = STORE_ZONE;
   }
   $from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id left) join " . TABLE_ZONES_TO_GEO_ZONES . " gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "')";
 }

 

Just doesn't look right to me? I'm stumped and this is just the first 4 out of the 55... as you aluded to in the other post this must be an issue with older versions, same template but heavily modified :(

[MrPhil]

Sigh. I can see you aren't understanding anything about what's going on. Read my posting on 1054 errors http://www.oscommerce.com/forums/topic/335136-osc-and-mysql-5-1054-errors again and again and again until you understand it. You can not change code by rote. You need to get the parentheses into the SQL query, not into the PHP code. You only need parentheses around a comma-separated list of table names. You don't split a "left join" into "left) join".

  $from_str = "from ((" . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd) left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c)";

This one is a bit tricky, because the final "left join" may or may not be used. That's why I put the second ) right after the "p2c", rather than at the place where another "left join" is (sometimes) added. I think that should work, but if MySQL doesn't like the extra level of parentheses, the code will have to be modified to add the "from" separately, after adding the second ( at the front. First things first -- try my code instead.

[davidell]

You don't split a "left join" into "left) join".

 

You sir are a gentlemen and a scholar :thumbsup:

 

Looking back at what I posted yesterday I feel slightly embarrassed, thank you for being so polite and patient with me. I re-read your post again and again, and started from a new version of the site methodically going through each file, refreshing the browser after each individual change.

 

advanced_search_results.php

checkout_process.php

index.php

popup_image.php

products_new.php

* at this point I decided to skip the admin folder)

functions\general.php

boxes\manufacturer_info.php

classes\order.php

classes\PriceFormater.php

 

PriceFormater.php is a file which is part of a price break contribution... I believe an much older version of this contribution.

 

No more 1054 error!!! My gratitude can not be measured.

 

Now I ask... should I go through the remainder of the files and ammend, or is it a case of it aint broke don't fix it?

 

Again, many thanks for your support MrPhil!

[MrPhil]

If it ain't broke (is not producing 1054 errors), I'd leave it alone for the time being. Just be aware of the principles involved should you make future code changes, where you might have to put () around a list.