Advanced Search with products_info fields

[miramira]

Hello,

I'd like to search for the products specs using the Advanced Search. But it doesn't work. I'm getting a SQL syntax error and I don't know how to fix it.

 

Does anybody know if there is a contribution that give this feature?

 

Thanks,

Alex

[Velveeta]

Hello,

I'd like to search for the products specs using the Advanced Search. But it doesn't work. I'm getting a SQL syntax error and I don't know how to fix it.

 

Does anybody know if there is a contribution that give this feature?

 

Thanks,

Alex

It would help us to help you if you'd post the section of code this query is from, as well as the error text you're getting...

 

Richard.

[miramira]

Hi Richard,

 

The part of the code I'm having problem is on the file /template/content/advanced_search_result.tpl.php. It's between the lines 55 and 61.

 

 

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

 

I'm trying to add an option to search the field type of the table products_info.

 

The error I'm getting is

 

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 'manufacturers m using(manufacturers_id), products_description pd left join speci' at line 1

 

select count(distinct p.products_id) as total from products p left join products_info t 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 p.products_date_added <= '20070213'

 

Thanks for your help,

Alex

[Velveeta]

Hi Richard,

 

The part of the code I'm having problem is on the file /template/content/advanced_search_result.tpl.php. It's between the lines 55 and 61.

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

 

I'm trying to add an option to search the field type of the table products_info.

 

The error I'm getting is

 

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 'manufacturers m using(manufacturers_id), products_description pd left join speci' at line 1

 

select count(distinct p.products_id) as total from products p left join products_info t 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 p.products_date_added <= '20070213'

 

Thanks for your help,

Alex

I've never used the "using" clause in a query yet, so I can't say for sure, but 2 things you can try are 1) move the "manufacturers m using(manufacturers_id)" to right after the "products p", and put a comma between the 2, like this "products p, manufacturers m using(manufacturers_id)"... Otherwise, you can try moving the "manufacturers m" to the same place, right after "products p" and with a comma between the 2, and move the using clause to the very end maybe (and prefix it with the table alias), something like this: "and p.products_date_added <= '<whatever date>' using (m.manufacturers_id)"...

 

Richard.

[miramira]

Thanks again for your help. I replaced the file by the default OsCommerce file and it works.

 

$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";

After that, I just added the new table I want to search and it stops working:

 

$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, t.type ";

 

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 " . TABLE_PRODUCTS_INFO . " t";

 

 

Any ideas about what I am doing wrong?

 

Thanks,

Alex

[Velveeta]

Thanks again for your help. I replaced the file by the default OsCommerce file and it works.

 

$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";

After that, I just added the new table I want to search and it stops working:

 

$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, t.type ";

 

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 " . TABLE_PRODUCTS_INFO . " t";

Any ideas about what I am doing wrong?

 

Thanks,

Alex

You're missing a comma right after the p2c portion of this string:

 

$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, " . TABLE_PRODUCTS_INFO . " t";

 

Richard.