Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Unknown column 'p.products_id' in 'on clause'

  1. #1
    Matt's Avatar
    Matt is offline GlowHost Administrator
    Join Date
    Jan 2005
    Location
    Behind your monitor
    Posts
    6,208

    Default Unknown column 'p.products_id' in 'on clause'

    Lots of Open source carts like Zen Cart, OS Commerce, OSCMax, Creloaded and many other derivitives have been having this issue upon upgrade to mysql 5.

    Here is a sample error:

    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 = '27'
    Some of them also say TEP Stop or TET Stop or [TEP Stop] in red letters or similar at the bottom of the error output.

    Possible solutions are below
    Last edited by Matt; 09-28-2007 at 03:11 PM.
    Send your friends and site visitors to GlowHost and get $125 plus bonus!
    GlowHost Affiliate Program | Read our Blog | Follow us on X |

  2. #2
    Matt's Avatar
    Matt is offline GlowHost Administrator
    Join Date
    Jan 2005
    Location
    Behind your monitor
    Posts
    6,208

    Default Fix For oscmax and mysql 5.0

    Fix for OSCmax on MySQL 5.0

    In the index.php files replace this:

    Code:
    "p.products_id = s.products_id"
    with:

    Code:
    "p2c.products_id = s.products_id "
    Except on the line that is like the below, around line 143.

    Code:
    $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 = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' 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)$HTTP_GET_VARS['filter_id'] . "'";
    Last edited by Matt; 09-25-2006 at 03:03 PM.
    Send your friends and site visitors to GlowHost and get $125 plus bonus!
    GlowHost Affiliate Program | Read our Blog | Follow us on X |

  3. #3
    Matt's Avatar
    Matt is offline GlowHost Administrator
    Join Date
    Jan 2005
    Location
    Behind your monitor
    Posts
    6,208

    Default Fix for OSCommerce or Creloaded MySQL 5.0

    Here is a user contribution for the same problem in Creloaded or Os Commerce installations having problems with mysql 5.


    Code:
    // Get the category name and description
    $category_query = tep_db_query("select cd.categories_name, cd.categories_heading_title, cd.categories_description, c.categories_image from " . TABLE_CATEGORIES . " c, " . TABLE_CATEGORIES_DESCRIPTION . " cd where c.categories_id = '" . $current_category_id . "' and cd.categories_id = '" . $current_category_id . "' and cd.language_id = '" . $languages_id . "'");
    $category = tep_db_fetch_array($category_query);
     
     
    // show the products of a specified manufacturer
    if (isset($HTTP_GET_VARS['manufacturers_id'])) {
    if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
    // 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 = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' 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)$HTTP_GET_VARS['filter_id'] . "'";
    } else {
    // 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 = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
    }
    } else {
    // show the products in a given categorie
    if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
    // 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 = '" . (int)$HTTP_GET_VARS['filter_id'] . "' 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 . "'";
    } else {
    // 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 = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
    }
    }

    TO THIS :

    Code:
    // show the products of a specified manufacturer
    if (isset($HTTP_GET_VARS['manufacturers_id'])) {
    if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
    // 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 = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' 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)$HTTP_GET_VARS['filter_id'] . "'";
    } else {
    // 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 = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
    }
    } else {
    // show the products in a given categorie
    if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
    // 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 = '" . (int)$HTTP_GET_VARS['filter_id'] . "' 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 . "'";
    } else {
    // 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 = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
    }
    }
    Send your friends and site visitors to GlowHost and get $125 plus bonus!
    GlowHost Affiliate Program | Read our Blog | Follow us on X |

  4. #4
    jmarcv's Avatar
    jmarcv is offline Cranky Coder
    Join Date
    Jan 2005
    Posts
    354

    Default Mysql 5 JOIN syntax change

    Many older applications have been failing due to non standard queries which produce errors about columns not existing.

    In most instances, merely changing the order of tables is enough to fix a statement.

    Example:

    SELECT f.* from forums f ,categories c LEFT JOIN moderator m
    ON (f.id=m.forum_id) WHERE f.category=c.id
    The above produces an error about column f.id not existing, when we know it does.
    My merely switching the order of the table references:
    categories c and forums f
    we fix the problem.


    SELECT f.* from categories c,forums f LEFT JOIN moderator m
    ON (f.id=m.forum_id) WHERE f.category=c.id

    Please refer to the following link for more info.

    http://dev.mysql.com/doc/refman/5.0/en/join.html
    See particularly "Join Processing Changes in MySQL 5.0.12
    Last edited by jmarcv; 08-16-2006 at 11:50 AM. Reason: formatting

  5. #5
    jmarcv's Avatar
    jmarcv is offline Cranky Coder
    Join Date
    Jan 2005
    Posts
    354

    Default Advanced search

    Here is another fix for the advanced search feature.

    NOTE! The several fixes I have seen on the web do NOT work!

    This GLOWHOST fix does.

    advanced_search_result.php

    change:
    Code:
    $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";
    to:
    Code:
    $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)";
    Last edited by Matt; 08-18-2006 at 03:05 PM.

  6. #6
    jmarcv's Avatar
    jmarcv is offline Cranky Coder
    Join Date
    Jan 2005
    Posts
    354

    Default ZEN Cart fix

    Here is a similar fix for Zen Cart:

    The fix for OSCmax on 'index.php' listed above applies to

    includes/index_filters/default_filter.php

    for Zen cart and potentially other scripts as well. Zen cart uses OSCommerce code.
    Last edited by Matt; 09-25-2006 at 07:04 PM. Reason: clarification

  7. #7
    sergey is offline Master Glow Jedi
    Join Date
    Aug 2005
    Posts
    472

    Default Zen Cart

    if you find the fix above does not correct all issues, you need to replace the following sql query in the file includes/modules/meta_tags.php to fix your Zen Cart:

    $sql= "select pd.products_name, p.products_model, p.products_price_sorter, p.products_tax_class_id,
    p.metatags_title_status, p.metatags_products_name_status, p.metatags_model_status,
    p.products_id, p.metatags_price_status, p.metatags_title_tagline_status,
    pd.products_description,
    mtpd.metatags_title, mtpd.metatags_keywords, mtpd.metatags_description
    from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd on mtpd.products_id = p.products_id and mtpd.language_id = '" . (int)$_SESSION['languages_id'] . "'
    where p.products_id = '" . (int)$_GET['products_id'] . "'
    and p.products_id = pd.products_id
    and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'";
    with:
    $sql= "select pd.products_name, p.products_model, p.products_price_sorter, p.products_tax_class_id,
    p.metatags_title_status, p.metatags_products_name_status, p.metatags_model_status,
    p.products_id, p.metatags_price_status, p.metatags_title_tagline_status,
    pd.products_description,
    mtpd.metatags_title, mtpd.metatags_keywords, mtpd.metatags_description
    from (" . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd) left join " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd on mtpd.products_id = p.products_id and mtpd.language_id = '" . (int)$_SESSION['languages_id'] . "'
    where p.products_id = '" . (int)$_GET['products_id'] . "'
    and p.products_id = pd.products_id
    and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'";
    Last edited by Matt; 09-26-2006 at 09:07 AM.

  8. #8
    andychev's Avatar
    andychev is offline Master Glow Jedi
    Join Date
    Apr 2005
    Location
    Chester, UK
    Posts
    150

    Default

    If anyone is getting 1064 errors in admin features in oscommerce after the upgrade such as the newsletter manager etc the following will fix it. The problem is related to the feature having no data to return.

    In admin/includes/classes/split_page_results.php approx line 35

    Find

    $offset = ($max_rows_per_page * ($current_page_number - 1));
    $sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

    Between enter:

    if ($offset < 0)
    {
    $offset = 0 ;
    }

    So it now reads:

    $offset = ($max_rows_per_page * ($current_page_number - 1));
    if ($offset < 0)
    {
    $offset = 0 ;
    }
    $sql_query .= " limit " . $offset . ", " . $max_rows_per_page;

  9. #9
    olagod is offline Almost no longer a newbie!
    Join Date
    Jan 2008
    Posts
    3

    Thumbs up This worked perfectly for me!

    Quote Originally Posted by sergey View Post
    if you find the fix above does not correct all issues, you need to replace the following sql query in the file includes/modules/meta_tags.php to fix your Zen Cart:


    with:
    This was the fix to a completely unexpected and out-of-the-blue error and since I don't write PHP I was really in a pinch to get a customer's shopping cart working again. The fix you listed had me back in place within minutes and this would have been a real mess without your help. A million thanks for this!

  10. #10
    olagod is offline Almost no longer a newbie!
    Join Date
    Jan 2008
    Posts
    3

    Default

    Quote Originally Posted by olagod View Post
    This was the fix to a completely unexpected and out-of-the-blue error and since I don't write PHP I was really in a pinch to get a customer's shopping cart working again. The fix you listed had me back in place within minutes and this would have been a real mess without your help. A million thanks for this!
    OOPS! spoke too soon, now I am getting Fatal error: Call to a member function Execute() on a non-object in /home/xxxxx/public_html/store1/includes/functions/sessions.php on line 72

    well, off to figure out what to do about this one now...

Page 1 of 2 12 LastLast

LinkBacks (?)


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14