Jump to content
  • Checkout
  • Login
  • Get in touch


The e-commerce.

SQL Help


Recommended Posts

I'm trying to write my own contribution. I have almost everything working except for displaying the correct product names and prices in the admin panel.


First, a little background:

I sell an item for cars where it comes in three different colors and also in left and right.

I also offer a disount if the left and the right of the same color are ordered. Right now I use attributes for this (color as one attribute and side Left/Right/Both+$20 as another), but it doesn't allow me to use the inventory functions.


I based my contrib off of the specials code included with ms2.


I created a table "pair_discount" for the data:











My problem is modifying the query in /catalog/admin/pair_discount.php to give me the following data by only knowing the pair_discount_id:

product_id_a, product_id_b, amount, expires_date

product_name_a, product_name_b, products_price_a, products_price_b,


It needs to link TABLE_PRODUCTS_DESCRIPTION to TABLE_PAIR_DISCOUNT twice (once for product_id_a and once for product_id_B).


This is the original query line from /catalog/admin/specials.php:

$product_query = tep_db_query("select p.products_id, pd.products_name, p.products_price, s.specials_new_products_price, s.expires_date from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_SPECIALS . " s where p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = s.products_id and s.specials_id = '" . (int)$HTTP_GET_VARS['sID'] . "'");


and this is my modified query:

$product_query = tep_db_query("select p.products_id, pd.products_name, p.products_price, s.amount, s.expires_date from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PAIR_DISCOUNT . " s where p.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' and p.products_id = s.product_id_a and s.pair_discount_id = '" . (int)$HTTP_GET_VARS['sID'] . "'");


but it only gets the name for product A.


Sample database data:



pair_discount_id, product_id_a, product_id_b, amount, expires_date

1, 64, 65, 9.95, 09092005



products_id, products_price

64, 29.95

65, 29.95



products_id, products_name, language_id

64, "Product Left", 1

65, "Product Right", 1


Can someone who knows PHP a little better help me out here? I've been going through the php help, but I can't seem to find out how to do it.

Link to comment
Share on other sites

$product_query = tep_db_query('SELECT S.product_a_id, S.product_b_id, S.amount, S.expires_date, P1.name AS NAME1, P2.name AS NAME2, P1.products_price AS PP1, P2.products_price AS PP2, PD1.products_description AS DES1, PD2.products_description AS DES2 FROM ' . TABLE_PAIR_DISCOUNT . ' S JOIN ' . TABLE_PRODUCTS . ' P1 ON S.product_a_id=P1.products_id JOIN ' . TABLE_PRODUCTS . ' P2 ON S.product_b_id=P2.products_id JOIN ' . TABLE_PRODUCTS_DESCRIPTION . ' PD1 ON P1.products_id=PD1.products_id JOIN ' . TABLE_PRODUCTS_DESCRIPTION . ' PD2 ON P2.products_id=PD2.products_id WHERE PD1.language_id=' . (int)$languages_id . ' AND PD2.language_id=' . (int)$language_id . ' AND S.pair_discount_id=' . (int)$HTTP_GET_VARS['sID']);



Haven't tested it though.

Link to comment
Share on other sites

Yup, I corrected those "deliberate" typos you put in to keep me on my toes. :)


You gave me enough to figure out what was going on, so that I was able to tweak it to get it to work.


Thank you very much. I can't wait to release this contrib. I've been looking for something like this for over a year, but was too lazy to do anything about it.

Link to comment
Share on other sites


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

  • Create New...