oscommerce2006 Posted March 6, 2006 Posted March 6, 2006 I have just run an sql query for the first time. I have amended some of my Php files but am getting the following error: 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 'nlp_expires, p.products_tax_class_id, IF(s.status, s.specials_new_products_price' at line 1 select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL 31 DAY) nlp_expires, 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 p.products_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL 31 DAY) nlp_expires,) as final_price from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join specials s on p.products_id = s.products_id, 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 = '1' and p2c.categories_id = '21' order by pd.products_name limit 0, 20 [TEP STOP] Does anyone know what i've done wrong? Any help would be great!
kgt Posted March 6, 2006 Posted March 6, 2006 It looks like you've incorrectly installed a contribution. I see at least two places which would generate syntax errors. UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL 31 DAY) nlp_expires Is missing a second closing parenthesis: UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL 31 DAY)) nlp_expires and p.products_price p.products_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL 31 DAY) nlp_expires,) as final_price is pretty mangled. I have no idea what it's even supposed to be. I suggest you revisit the installation instructions and try to fix this query. Contributions Discount Coupon Codes Donations
oscommerce2006 Posted March 6, 2006 Author Posted March 6, 2006 Hi, It is the new low price contribution i am trying to implement. I believe it is this code that is messing my file up but cannot see what is wrong: 7. catalog/index.php Several places, $listing_sql is constructed. Where you find p.products_price, add p.products_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL ".NLP_DAYS." DAY) nlp_expires,
kgt Posted March 6, 2006 Posted March 6, 2006 Several places, $listing_sql is constructed. Where you find p.products_price, add p.products_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL ".NLP_DAYS." DAY) nlp_expires, Are you adding after p.products_price or replacing it? p.products_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL ".NLP_DAYS." DAY) ) nlp_expires, is what you want. Contributions Discount Coupon Codes Donations
oscommerce2006 Posted March 6, 2006 Author Posted March 6, 2006 Are you adding after p.products_price or replacing it? p.products_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL ".NLP_DAYS." DAY) ) nlp_expires, is what you want. Hi, I am adding after it. IS that wrong? I had spotted the ) missing so have added them in. Many thanks,
oscommerce2006 Posted March 6, 2006 Author Posted March 6, 2006 Also the code i have is: // We show them all $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL ".NLP_DAYS." DAY)) nlp_expires,) 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, p.products_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL ".NLP_DAYS." DAY)) nlp_expires,) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m 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_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL ".NLP_DAYS." DAY)) nlp_expires, 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, p.products_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL ".NLP_DAYS." DAY))nlp_expires,) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c 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_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL ".NLP_DAYS." DAY)) nlp_expires, 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, p.products_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL ".NLP_DAYS." DAY)) nlp_expires,) 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 . "'"; } } Have double checked it and can't find any errors but am still getting: 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 ' p.products_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVA' at line 1 select p.products_image, pd.products_name, p.products_id, p.manufacturers_id, p.products_price, p.products_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL 31 DAY)) nlp_expires, 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, p.products_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL 31 DAY)) nlp_expires,) as final_price from products_description pd, products p left join manufacturers m on p.manufacturers_id = m.manufacturers_id left join specials s on p.products_id = s.products_id, 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 = '1' and p2c.categories_id = '21' order by pd.products_name limit 0, 20 [TEP STOP]
kgt Posted March 6, 2006 Posted March 6, 2006 // We show them all $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL ".NLP_DAYS." DAY)) nlp_expires, 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 left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m 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_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL ".NLP_DAYS." DAY)) nlp_expires, 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 left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c 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_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL ".NLP_DAYS." DAY)) nlp_expires, 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 . "'"; } } Contributions Discount Coupon Codes Donations
oscommerce2006 Posted March 6, 2006 Author Posted March 6, 2006 // We show them all $listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL ".NLP_DAYS." DAY)) nlp_expires, 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 left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m 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_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL ".NLP_DAYS." DAY)) nlp_expires, 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 left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c 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_old_price, UNIX_TIMESTAMP(DATE_ADD(p.products_price_changed, INTERVAL ".NLP_DAYS." DAY)) nlp_expires, 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 . "'"; } } Thanks this has worked a treat. No more errors. I am very greatful. No just gotta work out why the contribution doesn't work. Cheers
kgt Posted March 7, 2006 Posted March 7, 2006 Keep in mind I don't know this contribution, so it's possible the changes I suggested may have altered the expected results of the query. Contributions Discount Coupon Codes Donations
Recommended Posts
Archived
This topic is now archived and is closed to further replies.