smartbhupi Posted March 7, 2006 Posted March 7, 2006 Hi, I need urgent help for search error, when i search with single keyword, it works fine , but when i use 2 keywords or more it gives me below a error. Please kindly help me to solve 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 '(pd.products_name like '%ADVANCED_SEARCH_DEFAULT_OPERATOR%' or p.products_model ' at line 1 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 = '1' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and ((pd.products_name like '%ss%' or p.products_model like '%ss%' or m.manufacturers_name like '%ss%' or pd.products_description like '%ss%')(pd.products_name like '%ADVANCED_SEARCH_DEFAULT_OPERATOR%' or p.products_model like '%ADVANCED_SEARCH_DEFAULT_OPERATOR%' or m.manufacturers_name like '%ADVANCED_SEARCH_DEFAULT_OPERATOR%' or pd.products_description like '%ADVANCED_SEARCH_DEFAULT_OPERATOR%')(pd.products_name like '%ss%' or p.products_model like '%ss%' or m.manufacturers_name like '%ss%' or pd.products_description like '%ss%') ) Bhupi
♥Vger Posted March 7, 2006 Posted March 7, 2006 This link may help: http://www.oscommerce.com/forums/index.php?act...ter&f=7&t=31484 Vger
smartbhupi Posted March 7, 2006 Author Posted March 7, 2006 Hi, Please check at www.jewelbuyersinternational.com and try to search something by putting 2 keywords like "ring pendants" it will give error. if we try with only "rings" in search it will work fine. Please kindly help me to solve this problem, as due to this i am unable to proceed. Please kindly help me, i will appreciate it Bhupi
boxtel Posted March 7, 2006 Posted March 7, 2006 This link may help: http://www.oscommerce.com/forums/index.php?act...ter&f=7&t=31484 Vger cannot see in what way. Treasurer MFC
smartbhupi Posted March 7, 2006 Author Posted March 7, 2006 cannot see in what way. In keywords please enter 2 words then it will not work, in single word it works Bhupi
♥Vger Posted March 7, 2006 Posted March 7, 2006 I posted that link because it was the only one that matched the search criteria. If you have something of use to offer then please do so. Vger cannot see in what way.
♥Vger Posted March 7, 2006 Posted March 7, 2006 Never mind - found the problem. If you enter gold%silver then it will return results for items which have either gold or silver in their description - so it's the seperator that's the problem. The only one it is accepting is the % sign. You need to look in includes/functions/general.php, around line 702 you should see this code: // add default logical operators if needed $temp = array(); for($i=0; $i<(count($objects)-1); $i++) { $temp[] = $objects[$i]; if ( ($objects[$i] != 'and') && ($objects[$i] != 'or') && ($objects[$i] != '(') && ($objects[$i+1] != 'and') && ($objects[$i+1] != 'or') && ($objects[$i+1] != ')') ) { $temp[] = ADVANCED_SEARCH_DEFAULT_OPERATOR; It looks as though you have something else in your code. Vger
smartbhupi Posted March 7, 2006 Author Posted March 7, 2006 Never mind - found the problem. If you enter gold%silver then it will return results for items which have either gold or silver in their description - so it's the seperator that's the problem. The only one it is accepting is the % sign. You need to look in includes/functions/general.php, around line 702 you should see this code: // add default logical operators if needed $temp = array(); for($i=0; $i<(count($objects)-1); $i++) { $temp[] = $objects[$i]; if ( ($objects[$i] != 'and') && ($objects[$i] != 'or') && ($objects[$i] != '(') && ($objects[$i+1] != 'and') && ($objects[$i+1] != 'or') && ($objects[$i+1] != ')') ) { $temp[] = ADVANCED_SEARCH_DEFAULT_OPERATOR; It looks as though you have something else in your code. Vger Hi I have the below code, but still it is not working // add default logical operators if needed $temp = array(); for($i=0; $i<(count($objects)-1); $i++) { $temp[] = $objects[$i]; if ( ($objects[$i] != 'and') && ($objects[$i] != 'or') && ($objects[$i] != '(') && ($objects[$i+1] != 'and') && ($objects[$i+1] != 'or') && ($objects[$i+1] != ')') ) { $temp[] = ADVANCED_SEARCH_DEFAULT_OPERATOR;
♥Vger Posted March 7, 2006 Posted March 7, 2006 I would try replacing the root level advanced_search.php file and see if this helps. Also you should edit Barcelets to Bracelets and Pandents to Pendants - nothing to do with this problem but the typos don't look good. Vger
smartbhupi Posted March 7, 2006 Author Posted March 7, 2006 I would try replacing the root level advanced_search.php file and see if this helps. Also you should edit Barcelets to Bracelets and Pandents to Pendants - nothing to do with this problem but the typos don't look good. Vger Hi Vger, I tried this already but not working, Thanks for telling me the typos error. I even changed the general.php page with new one, but same problem is coming up. Bhupi
♥Vger Posted March 7, 2006 Posted March 7, 2006 I'm sorry, but I'm out of ideas on this one. The only thing I can think of is that your database (MySQL) language is different from the store and so not using the correct operators. Vger
smartbhupi Posted March 8, 2006 Author Posted March 8, 2006 I'm sorry, but I'm out of ideas on this one. The only thing I can think of is that your database (MySQL) language is different from the store and so not using the correct operators. Vger Hi, Anybody in this Forum can solve my problem?? Bhupi
boxtel Posted March 8, 2006 Posted March 8, 2006 Hi, Anybody in this Forum can solve my problem?? Bhupi you could check in you database "configuration" table to see if there is an entry called: Default Search Operator with configuration_key: ADVANCED_SEARCH_DEFAULT_OPERATOR and configuration_value: either and or or currently it does not recognise this constant and as such the operator is not added to the query and the constant is seen as a keyword itself. Treasurer MFC
smartbhupi Posted March 8, 2006 Author Posted March 8, 2006 you could check in you database "configuration" table to see if there is an entry called: Default Search Operator with configuration_key: ADVANCED_SEARCH_DEFAULT_OPERATOR and configuration_value: either and or or currently it does not recognise this constant and as such the operator is not added to the query and the constant is seen as a keyword itself. There is no Configuration_key ADVANCED_SEARCH_DEFAULT_OPERATOR in configuration table. I appreciate if anyone can please tell me the problem solution
♥Vger Posted March 8, 2006 Posted March 8, 2006 In the 'configuration' table in your database, you should see this entry: 14 Default Search Operator ADVANCED_SEARCH_DEFAULT_OPERATOR and Default search operators 1 17 NULL 2004-03-17 23:55:51 NULL tep_cfg_select_option(array('and', 'or'), If it is definitely not there then you need to run this sql command into your database: INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Default Search Operator', 'ADVANCED_SEARCH_DEFAULT_OPERATOR', 'and', 'Default search operators', '1', '17', 'tep_cfg_select_option(array(\'and\', \'or\'), ', now()); Remember - only insert this if you have looked through all of the 'configuration' table (it comes to many pages and not just one) and not found that entry. And if you do run this query BACKUP your database first! Vger
boxtel Posted March 9, 2006 Posted March 9, 2006 In the 'configuration' table in your database, you should see this entry:If it is definitely not there then you need to run this sql command into your database: INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, set_function, date_added) VALUES ('Default Search Operator', 'ADVANCED_SEARCH_DEFAULT_OPERATOR', 'and', 'Default search operators', '1', '17', 'tep_cfg_select_option(array(\'and\', \'or\'), ', now()); Remember - only insert this if you have looked through all of the 'configuration' table (it comes to many pages and not just one) and not found that entry. And if you do run this query BACKUP your database first! Vger and if it is not there you might want to ask yourself why. it is not an entry which can easily be deleted with a press of a button. Treasurer MFC
Cesar Brod Posted May 2, 2009 Posted May 2, 2009 I wonder if you guys have been able to fix it after all. I have seen people using other search contributed modules just to avoid this problem but I haven't been able to find a solution for this yet...
germ Posted May 2, 2009 Posted May 2, 2009 I've been the admin of a store for two years and we've never gotten this error. :huh: If I suggest you edit any file(s) make a backup first - I'm not perfect and neither are you. "Given enough impetus a parallelogramatically shaped projectile can egress a circular orifice." - Me - "Headers already sent" - The definitive help "Cannot redeclare ..." - How to find/fix it SSL Implementation Help Like this post? "Like" it again over there >
Recommended Posts
Archived
This topic is now archived and is closed to further replies.