Addon causing a SQL 1024 error in Search

[izar74]

I get this error every time i use the SEARCH function:

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 'order by p.products_sort_order, pd.products_name limit 0, 15' at line 19
in:
[SELECT DISTINCT p.products_image, p.products_quantity , m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter, p.products_qty_box_status, p.master_categories_id FROM (products p LEFT JOIN manufacturers m USING(manufacturers_id), products_description pd, categories c, products_to_categories p2c ) LEFT JOIN meta_tags_products_description mtpd ON mtpd.products_id= p2c.products_id AND mtpd.language_id = 2 WHERE (p.products_status = 1 AND p.products_id = pd.products_id AND pd.language_id = 2 AND p.products_id = p2c.products_id AND p2c.categories_id = c.categories_id AND p.products_price > 0 AND ((pd.products_name LIKE '%incenso%' OR p.products_model LIKE '%incenso%' OR m.manufacturers_name LIKE '%incenso%' OR (mtpd.metatags_keywords LIKE '%incenso%' AND mtpd.metatags_keywords !='') OR (mtpd.metatags_description LIKE '%incenso%' AND mtpd.metatags_description !='') OR pd.products_description LIKE '%incenso%') )) AND p.products_price > 0 order by p.products_sort_order, pd.products_name order by p.products_sort_order, pd.products_name limit 0, 15]

The error is always the same, i would like to understand what is causing it (which file). Note that I've many mod running and it's not doable to uninstall one by one to address the error...

Obiovsly it regards MYSQL 5.x.x cause with the 4.x.x i didn't get this error.

Actually I've:
Zencart 1.3.8a
MySQL 5.0.82sp1-log
PHP Version 5.2.13

the site is http://www.laviadellincenso.it/commerce/index.php

any help would be greatly appreciated ;-)

[DrByte]

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 'order by p.products_sort_order, pd.products_name limit 0, 15' at line 19
in:
[... AND p.products_price > 0 order by p.products_sort_order, pd.products_name order by p.products_sort_order, pd.products_name limit 0, 15]

One or more of your addons is causing the "order by" clause to be stated twice, instead of just once.

Obiovsly it regards MYSQL 5.x.x cause with the 4.x.x i didn't get this error.

I'd be surprised if it actually worked on MySQL 4, since it's definitely creating a SQL syntax error.

[izar74]

Thanks DrByte,

Yep you are right, just to try i reinstalled it on a Mysql 4.x.x ver and the error was there :-)!

Just to ask... trying to search a .php file for the "order by" clause will lead me to something or it's just waste of time?

Else where should i look for to find the mess?

Thanks for the help :-)

[izar74]

** Update**

Using WindowsGrep I found out that "order by p,products_sort_order" is present in

category_product_listing.php (1 matches)
default_filter.php (3 matches)
listing_dispaly_order.php (1 matches)
header_php.php (2 matches)
category_product_listing.php (1 metches)

Assuming that is a duplicate line that cause the problem i looked into the files with more matches:

default_filte.php:

if (isset($column_list)) {
if ((!isset($_GET['sort'])) || (isset($_GET['sort']) && !ereg('[1-8][ad]', $_GET['sort'])) || (substr($_GET['sort'], 0, 1) > sizeof($column_list)) ) {
for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {
if (isset($column_list[$i]) && $column_list[$i] == 'PRODUCT_LIST_NAME') {
$_GET['sort'] = $i+1 . 'a';
$listing_sql .= " order by p.products_sort_order, pd.products_name";
break;
} else {
// sort by products_sort_order when PRODUCT_LISTING_DEFAULT_SORT_ORDER is left blank
// for reverse, descending order use:
// $listing_sql .= " order by p.products_sort_order desc, pd.products_name";
$listing_sql .= " order by p.products_sort_order, pd.products_name";
break;
}
}

here one i s commented so should i remove one of the other two?

header_php.php:

//die('I SEE ' . $_GET['sort'] . ' - ' . PRODUCT_LISTING_DEFAULT_SORT_ORDER);
if ((!isset($_GET['sort'])) || (!ereg('[1-8][ad]', $_GET['sort'])) || (substr($_GET['sort'], 0 , 1) > sizeof($column_list))) {
for ($col=0, $n=sizeof($column_list); $col<$n; $col++) {
if ($column_list[$col] == 'PRODUCT_LIST_NAME') {
$_GET['sort'] = $col+1 . 'a';
$order_str = ' order by pd.products_name';
break;
} else {
// sort by products_sort_order when PRODUCT_LISTING_DEFAULT_SORT_ORDER ia left blank
// for reverse, descending order use:
// $listing_sql .= " order by p.products_sort_order desc, pd.products_name";
$order_str .= " order by p.products_sort_order, pd.products_name";
break;
}
}

here one is commented so it' seem to me that's fine.... or should i remove the line completely?

[izar74]

Ok... the good old method of "i'll try it all and see what happen.." is still the best way to go around the problems ;-)

the problem was in the

header_php.php (includes\modules\pages\advanced_search_results)

I just commented the line above and the problem was solved.

before:

} else {
// sort by products_sort_order when PRODUCT_LISTING_DEFAULT_SORT_ORDER ia left blank
// for reverse, descending order use:
// $listing_sql .= " order by p.products_sort_order desc, pd.products_name";
$order_str .= " order by p.products_sort_order, pd.products_name";
break;
}
}

after:

} else {
// sort by products_sort_order when PRODUCT_LISTING_DEFAULT_SORT_ORDER ia left blank
// for reverse, descending order use:
// $listing_sql .= " order by p.products_sort_order desc, pd.products_name";
// $order_str .= " order by p.products_sort_order, pd.products_name";
break;
}
}

If it's possible to change the title of the post with a [SOLVED] it may be of some help to someone else :-)

[CJPinder]

the problem was in the

header_php.php (includes\modules\pages\advanced_search_results)

I just commented the line above and the problem was solved.

Rather than hacking at core files I would suggest uploading a clean version of that file from the ZC 1.3.8a distribution and see if that fixes your problem.

Regards,
Christian.

[izar74]

It's not a Core file problem but a Mod problem. The above file was modified due to a Mod installed so a "clean" install would have disable the mod causing errors..
Modifing the file the way i wrote before solved the problem.