Filter special offer by category

[nguyen.chiencong]

Hi guys,

 

I can't get to install properly this add-on (Filter special offer by category):

http://addons.oscommerce.com/info/7193

 

The problem i have is when i choose a category for it to filter i got the following message:

"1054 - Unknown column 'p2c.categories_id' in 'where clause'

 

select count(*) as total from products p, products_description pd, specials s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '1' and s.status = '1' and p2c.categories_id=17"

 

(the p2c.categories_id change for each category but no category seems to work)

 

can u guys help me on this?

 

thanks in advance.

 

Cong

[MrPhil]

You've got tables 'p', 'pd', and 's' listed, but no products-to-categories (p2c) in your query.

[nguyen.chiencong]

thank you very much Phil but i'm very new to this because i'm more of a designer than developer so can u please tell me what should i do:

 

Should i add the p2c to the query?

What is exactly should i add?

 

thanks in advance for your answer

 

 

 

You've got tables 'p', 'pd', and 's' listed, but no products-to-categories (p2c) in your query.

[MrPhil]

The PHP code should produce the query

select count(*) as total from products p, products_description pd, specials s, products_to_categories p2c where 
p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and 
pd.language_id = '1' and s.status = '1' and p2c.categories_id=17"

In the PHP code you'll see something like TABLE_SPECIALS . " s and can change it to TABLE_SPECIALS . " s, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c. Or, they might have the table names hard-coded: specials s becomes specials s, products_to_categories p2c.

[nguyen.chiencong]

I tried that already and it didn't work.

 

Here's the code in my specials.php file :

 

<?php

$specials_query_raw = "select p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_image, s.specials_new_products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and s.status = '1'";

if($categories_id > 0)

$specials_query_raw .= " and p2c.categories_id=".(int)$categories_id;

$specials_query_raw .= " order by s.specials_date_added DESC";

 

//Only the condition " and p2c.categories_id=".(int)$categories_id; is appended in the query when $categories_id variable is greater than 0

 

$specials_split = new splitPageResults($specials_query_raw, MAX_DISPLAY_SPECIAL_PRODUCTS);

 

if (($specials_split->number_of_rows > 0) && ((PREV_NEXT_BAR_LOCATION == '1') || (PREV_NEXT_BAR_LOCATION == '3'))) {

?>

 

 

Can u enlighten me on the problem please.

 

Thanks man.

 

 

The PHP code should produce the query

select count(*) as total from products p, products_description pd, specials s, products_to_categories p2c where 
p.products_status = '1' and s.products_id = p.products_id and p.products_id = pd.products_id and 
pd.language_id = '1' and s.status = '1' and p2c.categories_id=17"

In the PHP code you'll see something like TABLE_SPECIALS . " s and can change it to TABLE_SPECIALS . " s, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c. Or, they might have the table names hard-coded: specials s becomes specials s, products_to_categories p2c.

[MrPhil]

Change

TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and s.products_id =

to

TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c 
where p.products_status = '1' and s.products_id =

[nguyen.chiencong]

Thanks Phil,

 

Actually i put this code inspiring on yours :

 

TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p2c.products_id = s.products_id and s.products_id = p.products_id and p.products_id = pd.products_id and pd.language_id = '"

 

Everything works when i select a subcategory on the specials page now. But i got another problem:

 

- When i select the categories, the page shows nothing.

(for example, when i select Action Movies, it shows all action movies but when i select DVD nothing shows up

or when i select mice, it shows my mice products but when i choose Hardware, it doesn't show anything)

 

Do you have any idea on this?

 

thanks Phil very much for your response.

 

 

 

 

 

Change

TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_status = '1' and s.products_id =

to

TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c 
where p.products_status = '1' and s.products_id =

[MrPhil]

No idea. All I can suggest is that you go into phpMyAdmin to the tab where you can run SQL queries, and experiment with your query there. You have to substitute all the table names and PHP variables so it's one long character string with no PHP codes. Take out pieces (clauses) of your query, one by one, until you find the bottleneck that's blocking the products you want to see. It's going to take some investigating, and you're going to learn more about SQL than you had ever dreamed.

 

Maybe your $categories_id is not set when you select categories, but is set when you are in a sub-category?

[nguyen.chiencong]

thanks phil but i have no idea what you're talking about. can u recommend me some tutorials on SQL so i can understand a bit more? because right now i'm kind of lost.

 

Thanks and cheers

 

No idea. All I can suggest is that you go into phpMyAdmin to the tab where you can run SQL queries, and experiment with your query there. You have to substitute all the table names and PHP variables so it's one long character string with no PHP codes. Take out pieces (clauses) of your query, one by one, until you find the bottleneck that's blocking the products you want to see. It's going to take some investigating, and you're going to learn more about SQL than you had ever dreamed.

 

Maybe your $categories_id is not set when you select categories, but is set when you are in a sub-category?

[nguyen.chiencong]

thanks phil but i have no idea what you're talking about. can u recommend me some tutorials on SQL so i can understand a bit more? because right now i'm kind of lost.

 

Thanks and cheers

 

No idea. All I can suggest is that you go into phpMyAdmin to the tab where you can run SQL queries, and experiment with your query there. You have to substitute all the table names and PHP variables so it's one long character string with no PHP codes. Take out pieces (clauses) of your query, one by one, until you find the bottleneck that's blocking the products you want to see. It's going to take some investigating, and you're going to learn more about SQL than you had ever dreamed.

 

Maybe your $categories_id is not set when you select categories, but is set when you are in a sub-category?

[nguyen.chiencong]

thanks phil but i have no idea what you're talking about. can u recommend me some tutorials on SQL so i can understand a bit more? because right now i'm kind of lost.

 

Thanks and cheers

 

No idea. All I can suggest is that you go into phpMyAdmin to the tab where you can run SQL queries, and experiment with your query there. You have to substitute all the table names and PHP variables so it's one long character string with no PHP codes. Take out pieces (clauses) of your query, one by one, until you find the bottleneck that's blocking the products you want to see. It's going to take some investigating, and you're going to learn more about SQL than you had ever dreamed.

 

Maybe your $categories_id is not set when you select categories, but is set when you are in a sub-category?

[MrPhil]

Have you tried contacting the author of this add-on, or finding an ongoing discussion of it? If you have no confidence in being able to diagnose SQL coding problems yourself, the author (or another user who knows PHP and SQL) would be the best one to deal with it.