PupStar Posted December 20, 2013 Posted December 20, 2013 Hi Guys, I have been pulling my hair out for a couple of days now so its time I threw this open to the community. I am running the Year Make Model contribution and the problem I am having is that when I insert a " into the name of the 'Part' then it produces this 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 ') and p.products_status = '1' and p.products_id = p2c.products_id and pd.produc' at line 1 select count(p.products_id) as total 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_id in () and p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '1' [TEP STOP] If I remove the " then the query works perfectly. I have posted this problem in the contribution thread but no response as yet and I could do with getting it sorted. This is the query from index.php if($current_category_id) { $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 " . (YMM_FILTER_PRODUCT_LISTING == 'Yes' ? $YMM_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 . "'"; }else{ $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 " . (YMM_FILTER_PRODUCT_LISTING == 'Yes' ? $YMM_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 . "'"; } I can not for the life of me see if there is a problem with this. Any ideas would be appreciated. Thanks Mark
MrPhil Posted December 21, 2013 Posted December 21, 2013 where p.products_id in () There needs to be one or more numbers in the ( ) list. I don't recall the solution off the top of my head, but I know this kind of thing has been discussed many times before.
PupStar Posted December 21, 2013 Author Posted December 21, 2013 where p.products_id in () There needs to be one or more numbers in the ( ) list. I don't recall the solution off the top of my head, but I know this kind of thing has been discussed many times before. Thanks Phil, I have tried it with more than 1 number in the list and the " still produces the error. I have searched but can not seem to find anything that really relates to this problem. Regards Mark
Guest Posted December 22, 2013 Posted December 22, 2013 Just a quick answer at first glance (I don't know the YMM-contrib): IMHO, the " is causing the PHP code to break the string. Try to use \" in order to escape the " character and see if this helps. if not, you could also try the entity code " (if the text is only shown on web pages). Hope it helps...
PupStar Posted December 22, 2013 Author Posted December 22, 2013 Just a quick answer at first glance (I don't know the YMM-contrib): IMHO, the " is causing the PHP code to break the string. Try to use \" in order to escape the " character and see if this helps. if not, you could also try the entity code " (if the text is only shown on web pages). Hope it helps... Hi Dirk, Thanks for the reply but the entity code did not work and neither does escaping the character with \ because it is part of the text in the database record. It somehow needs to be escaped when called in the query. Mark
MrPhil Posted December 22, 2013 Posted December 22, 2013 I thought I responded to this earlier today, but I don't see my post. I don't see a " in the error listing, and the PHP code you gave doesn't appear to have anything to do with the error listing. If the add-on is poorly written and doesn't escape various quotes and other special characters, that could be a problem. Also, are you sure you're talking about a real " and not an MS Smart Quote you copy and pasted in from Word or Outlook? A Smart Quote is not a valid Latin-1 or UTF-8 character and could give the database a headache trying to process it (as a control code).
PupStar Posted December 22, 2013 Author Posted December 22, 2013 I thought I responded to this earlier today, but I don't see my post. I don't see a " in the error listing, and the PHP code you gave doesn't appear to have anything to do with the error listing. If the add-on is poorly written and doesn't escape various quotes and other special characters, that could be a problem. Also, are you sure you're talking about a real " and not an MS Smart Quote you copy and pasted in from Word or Outlook? A Smart Quote is not a valid Latin-1 or UTF-8 character and could give the database a headache trying to process it (as a control code). no they are not cut and pasted from anywhere. The field in the database is set as VARCHAR so not really sure why it does not work. So if it works for the product name then surely it should work for this urgh Mark
MrPhil Posted December 25, 2013 Posted December 25, 2013 The error message you gave in your first post does not have a " anywhere, and does not appear to have a "name" field. You say you remove the " (from what?) and it works. It does have an ID list which is empty: (). You don't just randomly stick numbers in it and hope for the best... the PHP code building the query is supposed to supply a list of one or more ID numbers, and isn't for some reason. Perhaps a " in a name is breaking the code and not allowing the associated product ID to get into the ID list? This (an empty list) I'm sure has been discussed many times before, and it would be worth searching the forum for it (it might give a lead to where to find the "-in-name causing the problem). Finally, the PHP code you gave after the error message does not appear to have anything to do with the indicated query.
PupStar Posted December 26, 2013 Author Posted December 26, 2013 The error message you gave in your first post does not have a " anywhere, and does not appear to have a "name" field. You say you remove the " (from what?) and it works. It does have an ID list which is empty: (). You don't just randomly stick numbers in it and hope for the best... the PHP code building the query is supposed to supply a list of one or more ID numbers, and isn't for some reason. Perhaps a " in a name is breaking the code and not allowing the associated product ID to get into the ID list? This (an empty list) I'm sure has been discussed many times before, and it would be worth searching the forum for it (it might give a lead to where to find the "-in-name causing the problem). Finally, the PHP code you gave after the error message does not appear to have anything to do with the indicated query. I am not really sure how to explain it to make it any clearer. So here are some images Image 1 shows the 'products_car_part' field to contain no special characters i.e. " Image 2 shows the 'products_car_part' field to contain the special character "
burt Posted December 26, 2013 Posted December 26, 2013 If you have a " in a name, then you have to use ' to surround it. And vice versa. "Don't" '10" width'
PupStar Posted December 26, 2013 Author Posted December 26, 2013 If you have a " in a name, then you have to use ' to surround it. And vice versa. "Don't" '10" width' if that is the case then how are " handled in say the products name when the field type is set to the same VARCHAR??? Maybe I am totally missing the point and it is more simple than I think :thought_balloon:
Recommended Posts
Archived
This topic is now archived and is closed to further replies.