Search Error

nazlim · June 10, 2005

Hi Guys,

I just installed oscommerce. Everything is working perfectly. But I have a problem with search function.

When i search I get this error message

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 'select distinct p.products_image, m.manufacturers_id, p.produc

select count(select distinct p.products_image, m.manufacturers_id, p.products_id, pd.products_name, 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 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 \'%brother%\' or p.products_model like \'%brother%\' or m.manufacturers_name like \'%brother%\' or pd.products_description like \'%brother%\') and (pd.products_name like \'%mfc%\' or p.products_model like \'%mfc%\' or m.manufacturers_name like \'%mfc%\' or pd.products_description like \'%mfc%\') and (pd.products_name like \'%7000%\' or p.products_model like \'%7000%\' or m.manufacturers_name like \'%7000%\' or pd.products_description like \'%7000%\') ) order by pd.products_name) as total

[TEP STOP]

Does Anyone know how to solve this problem.

Note: I did not install the sql. It was already installed by my hosting company.

Thanks for all replies to be

June 10, 2005

post a link. looks like your host may have modified things.

nazlim · June 10, 2005

post a link. looks like your host may have modified things.

<{POST_SNAPBACK}>

Do u think should reinstall it. I have another account from same hosting company. I do not have any problem with the other one.

But I will lose everything i have done so far if i choose to reinstall it. Please advise me for what i shoud do.

Thank you

June 10, 2005

dont do any reinstalling, i asked for a link so i can see what version of the search it is using, etc, perhaps give some ideas. if you want help, you need to help.

nazlim · June 10, 2005

dont do any reinstalling, i asked for a link so i can see what version of the search it is using, etc, perhaps give some ideas. if you want help, you need to help.

<{POST_SNAPBACK}>

The site is under construction right. Site is password protected right now so i can not give any link information but i will send you the codes

advanced_search.php

<?php

require('includes/application_top.php');

require(DIR_WS_LANGUAGES . $language . '/' . FILENAME_ADVANCED_SEARCH);

$breadcrumb->add(NAvb script:popupWindow(\'' . tep_href_link(FILENAME_POPUP_SEARCH_HELP) . '\')">' . TEXT_SEARCH_HELP_LINK . '</a>'; ?></td>

</tr>

</table></td>

</tr>

<tr>

</tr>

<tr>

<tr>

<td class="fieldValue"><?php echo tep_draw_pull_down_menu('categories_id', tep_get_categories(array(array('id' => '', 'text' => TEXT_ALL_CATEGORIES)))); ?></td>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

<td class="fieldValue"><?php echo tep_draw_pull_down_menu('manufacturers_id', tep_get_manufacturers(array(array('id' => '', 'text' => TEXT_ALL_MANUFACTURERS)))); ?></td>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

<tr>

</tr>

</table></td>

</tr>

</table></td>

</tr>

</table></form></td>

<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>

<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

nazlim · June 10, 2005

this is the advanced_search_results.php codes

Thank you for helping

<?php

require('includes/application_top.php');

require(DIR_WS_LANGUAGES . $language . '/' . FILENAME_ADVANCED_SEARCH);

$error = false;

if ( (isset($HTTP_GET_VARS['keywords']) && empty($HTTP_GET_VARS['keywords'])) &&

(isset($HTTP_GET_VARS['dfrom']) && (empty($HTTP_GET_VARS['dfrom']) || ($HTTP_GET_VARS['dfrom'] == DOB_FORMAT_STRING))) &&

(isset($HTTP_GET_VARS['dto']) && (empty($HTTP_GET_VARS['dto']) || ($HTTP_GET_VARS['dto'] == DOB_FORMAT_STRING))) &&

(isset($HTTP_GET_VARS['pfrom']) && !is_numeric($HTTP_GET_VARS['pfrom'])) &&

(isset($HTTP_GET_VARS['pto']) && !is_numeric($HTTP_GET_VARS['pto'])) ) {

$error = true;

$messageStack->add_session('search', ERROR_AT_LEAST_ONE_INPUT);

} else {

$dfrom = '';

$dto = '';

$pfrom = '';

$pto = '';

$keywords = '';

if (isset($HTTP_GET_VARS['dfrom'])) {

$dfrom = (($HTTP_GET_VARS['dfrom'] == DOB_FORMAT_STRING) ? '' : $HTTP_GET_VARS['dfrom']);

}

if (isset($HTTP_GET_VARS['dto'])) {

$dto = (($HTTP_GET_VARS['dto'] == DOB_FORMAT_STRING) ? '' : $HTTP_GET_VARS['dto']);

}

if (isset($HTTP_GET_VARS['pfrom'])) {

$pfrom = $HTTP_GET_VARS['pfrom'];

}

if (isset($HTTP_GET_VARS['pto'])) {

$pto = $HTTP_GET_VARS['pto'];

}

if (isset($HTTP_GET_VARS['keywords'])) {

$keywords = $HTTP_GET_VARS['keywords'];

}

$date_check_error = false;

if (tep_not_null($dfrom)) {

if (!tep_checkdate($dfrom, DOB_FORMAT_STRING, $dfrom_array)) {

$error = true;

$date_check_error = true;

$messageStack->add_session('search', ERROR_INVALID_FROM_DATE);

}

if (tep_not_null($dto)) {

if (!tep_checkdate($dto, DOB_FORMAT_STRING, $dto_array)) {

$error = true;

$date_check_error = true;

$messageStack->add_session('search', ERROR_INVALID_TO_DATE);

}

if (($date_check_error == false) && tep_not_null($dfrom) && tep_not_null($dto)) {

if (mktime(0, 0, 0, $dfrom_array[1], $dfrom_array[2], $dfrom_array[0]) > mktime(0, 0, 0, $dto_array[1], $dto_array[2], $dto_array[0])) {

$error = true;

$messageStack->add_session('search', ERROR_TO_DATE_LESS_THAN_FROM_DATE);

}

$price_check_error = false;

if (tep_not_null($pfrom)) {

if (!settype($pfrom, 'double')) {

$error = true;

$price_check_error = true;

$messageStack->add_session('search', ERROR_PRICE_FROM_MUST_BE_NUM);

}

if (tep_not_null($pto)) {

if (!settype($pto, 'double')) {

$error = true;

$price_check_error = true;

$messageStack->add_session('search', ERROR_PRICE_TO_MUST_BE_NUM);

}

if (($price_check_error == false) && is_float($pfrom) && is_float($pto)) {

if ($pfrom >= $pto) {

$error = true;

$messageStack->add_session('search', ERROR_PRICE_TO_LESS_THAN_PRICE_FROM);

}

if (tep_not_null($keywords)) {

if (!tep_parse_search_string($keywords, $search_keywords)) {

$error = true;

$messageStack->add_session('search', ERROR_INVALID_KEYWORDS);

}

if (empty($dfrom) && empty($dto) && empty($pfrom) && empty($pto) && empty($keywords)) {

$error = true;

$messageStack->add_session('search', ERROR_AT_LEAST_ONE_INPUT);

}

if ($error == true) {

tep_redirect(tep_href_link(FILENAME_ADVANCED_SEARCH, tep_get_all_get_params(), 'NONSSL', true, false));

}

$breadcrumb->add(NAVBAR_TITLE_1, tep_href_link(FILENAME_ADVANCED_SEARCH));

$breadcrumb->add(NAVBAR_TITLE_2, tep_href_link(FILENAME_ADVANCED_SEARCH_RESULT, tep_get_all_get_params(), 'NONSSL', true, false));

?>

<!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN">

<head>

</head>

<?php require(DIR_WS_INCLUDES . 'header.php'); ?>

<?php require(DIR_WS_INCLUDES . 'column_left.php'); ?>

<tr>

<tr>

</tr>

</table></td>

</tr>

<tr>

</tr>

<tr>

<td>

<?php

// create column list

$define_list = array('PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL,

'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME,

'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER,

'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE,

'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY,

'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT,

'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE,

'PRODUCT_LIST_BUY_NOW' => PRODUCT_LIST_BUY_NOW);

asort($define_list);

$column_list = array();

reset($define_list);

while (list($key, $value) = each($define_list)) {

if ($value > 0) $column_list[] = $key;

}

$select_column_list = '';

for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {

switch ($column_list[$i]) {

case 'PRODUCT_LIST_MODEL':

$select_column_list .= 'p.products_model, ';

break;

case 'PRODUCT_LIST_MANUFACTURER':

$select_column_list .= 'm.manufacturers_name, ';

break;

case 'PRODUCT_LIST_QUANTITY':

$select_column_list .= 'p.products_quantity, ';

break;

case 'PRODUCT_LIST_IMAGE':

$select_column_list .= 'p.products_image, ';

break;

case 'PRODUCT_LIST_WEIGHT':

$select_column_list .= 'p.products_weight, ';

break;

}

$select_str = "select distinct " . $select_column_list . " m.manufacturers_id, p.products_id, pd.products_name, 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 ";

if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {

$select_str .= ", SUM(tr.tax_rate) as tax_rate ";

}

$from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {

if (!tep_session_is_registered('customer_country_id')) {

$customer_country_id = STORE_COUNTRY;

$customer_zone_id = STORE_ZONE;

}

$from_str .= " left join " . TABLE_TAX_RATES . " tr on p.products_tax_class_id = tr.tax_class_id left join " . TABLE_ZONES_TO_GEO_ZONES . " gz on tr.tax_zone_id = gz.geo_zone_id and (gz.zone_country_id is null or gz.zone_country_id = '0' or gz.zone_country_id = '" . (int)$customer_country_id . "') and (gz.zone_id is null or gz.zone_id = '0' or gz.zone_id = '" . (int)$customer_zone_id . "')";

}

$where_str = " where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id ";

if (isset($HTTP_GET_VARS['categories_id']) && tep_not_null($HTTP_GET_VARS['categories_id'])) {

if (isset($HTTP_GET_VARS['inc_subcat']) && ($HTTP_GET_VARS['inc_subcat'] == '1')) {

$subcategories_array = array();

tep_get_subcategories($subcategories_array, $HTTP_GET_VARS['categories_id']);

$where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and (p2c.categories_id = '" . (int)$HTTP_GET_VARS['categories_id'] . "'";

for ($i=0, $n=sizeof($subcategories_array); $i<$n; $i++ ) {

$where_str .= " or p2c.categories_id = '" . (int)$subcategories_array[$i] . "'";

}

$where_str .= ")";

} else {

$where_str .= " and p2c.products_id = p.products_id and p2c.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['categories_id'] . "'";

}

if (isset($HTTP_GET_VARS['manufacturers_id']) && tep_not_null($HTTP_GET_VARS['manufacturers_id'])) {

$where_str .= " and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";

}

if (isset($search_keywords) && (sizeof($search_keywords) > 0)) {

$where_str .= " and (";

for ($i=0, $n=sizeof($search_keywords); $i<$n; $i++ ) {

switch ($search_keywords[$i]) {

case '(':

case ')':

case 'and':

case 'or':

$where_str .= " " . $search_keywords[$i] . " ";

break;

default:

$keyword = tep_db_prepare_input($search_keywords[$i]);

$where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%'";

if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description like '%" . tep_db_input($keyword) . "%'";

$where_str .= ')';

break;

}

$where_str .= " )";

}

if (tep_not_null($dfrom)) {

$where_str .= " and p.products_date_added >= '" . tep_date_raw($dfrom) . "'";

}

if (tep_not_null($dto)) {

$where_str .= " and p.products_date_added <= '" . tep_date_raw($dto) . "'";

}

if (tep_not_null($pfrom)) {

if ($currencies->is_set($currency)) {

$rate = $currencies->get_value($currency);

$pfrom = $pfrom / $rate;

}

if (tep_not_null($pto)) {

if (isset($rate)) {

$pto = $pto / $rate;

}

if (DISPLAY_PRICE_WITH_TAX == 'true') {

if ($pfrom > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) >= " . (double)$pfrom . ")";

if ($pto > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) * if(gz.geo_zone_id is null, 1, 1 + (tr.tax_rate / 100) ) <= " . (double)$pto . ")";

} else {

if ($pfrom > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) >= " . (double)$pfrom . ")";

if ($pto > 0) $where_str .= " and (IF(s.status, s.specials_new_products_price, p.products_price) <= " . (double)$pto . ")";

}

if ( (DISPLAY_PRICE_WITH_TAX == 'true') && (tep_not_null($pfrom) || tep_not_null($pto)) ) {

$where_str .= " group by p.products_id, tr.tax_priority";

}

if ( (!isset($HTTP_GET_VARS['sort'])) || (!ereg('[1-8][ad]', $HTTP_GET_VARS['sort'])) || (substr($HTTP_GET_VARS['sort'], 0, 1) > sizeof($column_list)) ) {

for ($i=0, $n=sizeof($column_list); $i<$n; $i++) {

if ($column_list[$i] == 'PRODUCT_LIST_NAME') {

$HTTP_GET_VARS['sort'] = $i+1 . 'a';

$order_str = ' order by pd.products_name';

break;

}

} else {

$sort_col = substr($HTTP_GET_VARS['sort'], 0 , 1);

$sort_order = substr($HTTP_GET_VARS['sort'], 1);

$order_str = ' order by ';

switch ($column_list[$sort_col-1]) {

case 'PRODUCT_LIST_MODEL':

$order_str .= "p.products_model " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";

break;

case 'PRODUCT_LIST_NAME':

$order_str .= "pd.products_name " . ($sort_order == 'd' ? "desc" : "");

break;

case 'PRODUCT_LIST_MANUFACTURER':

$order_str .= "m.manufacturers_name " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";

break;

case 'PRODUCT_LIST_QUANTITY':

$order_str .= "p.products_quantity " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";

break;

case 'PRODUCT_LIST_IMAGE':

$order_str .= "pd.products_name";

break;

case 'PRODUCT_LIST_WEIGHT':

$order_str .= "p.products_weight " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";

break;

case 'PRODUCT_LIST_PRICE':

$order_str .= "final_price " . ($sort_order == 'd' ? "desc" : "") . ", pd.products_name";

break;

}

$listing_sql = $select_str . $from_str . $where_str . $order_str;

require(DIR_WS_MODULES . FILENAME_PRODUCT_LISTING);

?>

</td>

</tr>

<tr>

</tr>

<tr>

<td class="main"><?php echo '<a href="' . tep_href_link(FILENAME_ADVANCED_SEARCH, tep_get_all_get_params(array('sort', 'page')), 'NONSSL', true, false) . '">' . tep_image_button('button_back.gif', IMAGE_BUTTON_BACK) . '</a>'; ?></td>

</tr>

</table></td>

<?php require(DIR_WS_INCLUDES . 'footer.php'); ?>

<?php require(DIR_WS_INCLUDES . 'application_bottom.php'); ?>

June 10, 2005

all i asked for is a link, let me be a bit more detailed . ..

post a link to your web site

nazlim · June 10, 2005

all i asked for is a link, let me be a bit more detailed . ..
post a link to your web site

<{POST_SNAPBACK}>

site is password protected right now.

nazlim · June 10, 2005

path for search function is

catalog/advanced_search.php

catalog/advanced_search_result.php

June 10, 2005

not much i can do without seeing the site, sorry

nazlim · June 10, 2005

not much i can do without seeing the site, sorry

<{POST_SNAPBACK}>

Thank You Mibble....

Anyone else who had the same problem? please post here how you fixed it. Your help means a lot to me

Search Error

Recommended Posts

nazlim

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

nazlim

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

nazlim

Link to comment

Share on other sites

nazlim

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

nazlim

Link to comment

Share on other sites

nazlim

Link to comment

Share on other sites

Guest

Link to comment

Share on other sites

nazlim

Link to comment

Share on other sites

Archived

Browse

Activity