The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Mysql code error ?

Discussion in 'General Discussion' started by duranduran, Jan 18, 2008.

  1. duranduran

    duranduran Well-Known Member

    Joined:
    Apr 30, 2004
    Messages:
    198
    Likes Received:
    0
    Trophy Points:
    16
    Hi gurus,

    I have this strange error in one of my sites: http://www.newlineeletronicos.com.br/loja/index.php?cPath=4

    This is the 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 = '4' and p2c.categories_id = '4'

    This is a mysql5 error ? (this site was using mysql4.1 before).
     
  2. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,474
    Likes Received:
    202
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    Try doing a repair on the DB via PHPMyAdmin in your cPanel, might help. Is that cart running up to date software?
     
  3. duranduran

    duranduran Well-Known Member

    Joined:
    Apr 30, 2004
    Messages:
    198
    Likes Received:
    0
    Trophy Points:
    16
    I tried everything. Would this error be a problem in the migration? (before the server used mysql4 now it uses mysql5).

    I bealive this is a old version of e-commerce script
     
  4. Infopro

    Infopro cPanel Sr. Product Evangelist
    Staff Member

    Joined:
    May 20, 2003
    Messages:
    14,474
    Likes Received:
    202
    Trophy Points:
    63
    Location:
    Pennsylvania
    cPanel Access Level:
    Root Administrator
    Twitter:
    Probably just out of date scripts is the issue. Back up your DB, upgrade the cart and see how it goes.
     
  5. duranduran

    duranduran Well-Known Member

    Joined:
    Apr 30, 2004
    Messages:
    198
    Likes Received:
    0
    Trophy Points:
    16
    I found the solution, it is about the diferences betwen mysql4 and mysql5 queries.

    The old code:

    SELECT count( DISTINCT p.products_id ) AS total
    FROM products p
    LEFT JOIN manufacturers m
    USING ( 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 = '4'
    AND p.products_id = p2c.products_id
    AND p2c.categories_id = c.categories_id
    AND (
    (
    pd.products_name LIKE '%dvd%'
    OR p.products_model LIKE '%dvd%'
    OR m.manufacturers_name LIKE '%dvd%'
    )
    )

    The new code (mysql5):

    SELECT count( DISTINCT p.products_id ) AS total
    FROM products p
    LEFT JOIN manufacturers m USING ( manufacturers_id )
    LEFT JOIN specials s ON (s.products_id = p.products_id),
    products_description pd, categories c, products_to_categories p2c
    WHERE p.products_status = '1'
    AND p.products_id = pd.products_id
    AND pd.language_id = '4'
    AND p.products_id = p2c.products_id
    AND p2c.categories_id = c.categories_id
    AND (
    (
    pd.products_name LIKE '%dvd%'
    OR p.products_model LIKE '%dvd%'
    OR m.manufacturers_name LIKE '%dvd%'
    )
    )
     
Loading...

Share This Page