Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Need help with the database query


markmca

Recommended Posts

Posted

I would like to retrieve all the products which fall under the main category that is We have

 

Accesories->Car accesories->General

Accesories->Car accesories->Other

 

 

this is the flow of the categories I would like to view all the products which are under category Accesories which should include all the products of all the subcategories under Accesories

 

the tables all the same used in the oscommerce

 

Pls anyone help me out

Posted

To write such a sql query it is only possible to retrieve all products if you ALWAYS have the same number of subcategories; i.e. in this example you have main category accessories, then Sub 1 Car Accessories, Then Sub 2 General and Other.

 

If you write a query to have all products listed; logically, you have to fix where all the products will be located; in this case under Sub 2. Would you have any that were located under Sub 1, or if you would have another branch that has Sub 3 category, it would not find these with the same query; but seperate queries would need to be defined.

 

Hope this clarifies a bit how it works; now the question is, do you only have products listed under Sub 2 category level ?

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Posted

I would like to retrieve all the products which fall under the main category that is We have

 

Accesories->Car accesories->General

Accesories->Car accesories->Other

 

 

this is the flow of the categories I would like to view all the products which are under category Accesories which should include all the products of all the subcategories under Accesories

 

the tables all the same used in the oscommerce

 

Pls anyone help me out

Posted

Well thank you for the response

 

Let me make clear Like I have

 

Accesories(category)

->Car Acceesories(subcategory)

-> General(subsubcategory)

-> Other(subsubcategory)

 

This is the hierarchyof the categories and there will not be any more sub categories under subsubcategories.I would like to query all the products under which come under Accesories

 

Thank you

Posted

select mcd.categories_name, sc1d.categories_name, sc2d.categories_name, p.* , pd.* 
from categories_description mcd, categories mc, categories_description sc1d, categories sc1, categories_description sc2d, categories sc2,products p, products_description pd, products_to_categories ptc
where mc.categories_id = mcd.categories_id and sc1.categories_id = sc1d.categories_id and sc2.categories_id = sc2d.categories_id and sc2.parent_id = sc1.categories_id and sc1.parent_id = mc.categories_id and mcd.categories_name = "Accesories" and mcd.language_id = sc1d.language_id and sc1d.language_id = sc2d.language_id and p.products_id = ptc.products_id and ptc.categories_id = sc2.categories_id and pd.language_id = sc2d.language_id;

This code runs without errors; but since I don't have the categories structure like you have, haven't been able to get results back.

 

HTH

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Posted

Try this:

 

//get all subcategories

$all_categories_array = tep_get_category_tree((int)$current_category_id, '', '0', '', true);

$like_string = '';

foreach($all_categories_array AS $value){

$like_string .= $value['id'] . ', ';

}

$like_string = substr($like_string, 0, -2);

 

// We show the categorie and subcategories

$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, 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 " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id where products_status = '1' and p.products_id = pd.products_id and pd.language_id = '" . (int)$osC_Session->value('languages_id') . "' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id IN(" . $like_string . ")";

 

 

I hope this function is in the default catalog and the $current_category_id value is set. If not, you need copy it from admin to catalog and set the current category:

tep_get_category_tree((int)$current_category_id, '', '0', '', true);

 

HTH

 

Gunter

 

PS: If there is still a column in there which isn't in the default OSC and you get an error, you need to take it out.

Posted

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Posted

Hi Carine,

 

You properly point out one of best reasons for not allowing double posts!

 

Let's see how many good folks we can get to work independently solving the same problems.

 

Oh well don't let it get you down.

 

;)

 

Tom

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...