Jump to content
  • Checkout
  • Login
  • Get in touch


The e-commerce.

SQL Help - Advanced Query Tweak


Recommended Posts

I have a store in the testing phase that has Easy Populate, Multi-Stores, QTPro, Credit Class/Gift Voucher and a couple lesser involved contributions installed. I seem to have everything working so far except a few minor things.


I just installed Order Editor v1.4 and am having an issue with a query thats not pulling everything I need.


To accomodate the multiple stores I created a category for each store, ie. Acme Apparel, Jacks Jerseys, etc, which is in turn filled with categories, ie. bags, headwear, tshirts, etc. The products then occupy these categories.


The problem Im having is that the main query in the Order Editor contrib only selects categories that are populated with products. This is because of a LEFT JOIN on the PRODUCTS_TO_CATEGORIES table. This makes a list that will contain several bags, headwear, and tshirts listings, one for each store/catgory in the pulldown that Order Editor uses. That makes it impossible to know which tshirt category I want to select to add a product to an order.


Here's the query;

$result = tep_db_query("SELECT products_name, p.products_id, x.categories_name, ptc.categories_id FROM " . TABLE_PRODUCTS . " p LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON pd.products_id=p.products_id LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " ptc ON ptc.products_id=p.products_id LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd ON cd.categories_id=ptc.categories_id LEFT JOIN " . TABLE_CATEGORIES_DESCRIPTION . " x ON x.categories_id=ptc.categories_id ORDER BY categories_id");


Any of you SQL Wizzes know how I can get this to include the main store categories so that when I open the pulldown each sub category is under its 'store' category?



Matt Mika

Installed Contributions: Multi-Stores, QTPro, CCGV(trad), Batch Print, EasyPopulate, Simple Manual Order Entry, Encrypting Credit Card Via Mcrypt, UPSXML, Down for Maintenance, On The Fly GD Thumbs, SPPC, SPPC Hide, and various personal tweaks

Link to comment
Share on other sites


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

  • Create New...