SQL Help

[braclark]

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:

pair_discount_id

product_id_a

product_id_b

amount

pair_discount_date_added

pair_discount_last_modified

expires_date

date_status_change

status

 

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

pair_discount_id, product_id_a, product_id_b, amount, expires_date

1, 64, 65, 9.95, 09092005

 

PRODUCTS

products_id, products_price

64, 29.95

65, 29.95

 

PRODUCTS_DESCRIPTION

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.

[FalseDawn]

$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.

[braclark]

Thanks, I'm working on it, I'll let you know :)

[FalseDawn]

You might want to correct the "deliberate" typos :)

product_a_id should be product_id_a, and same for product_b_id

[braclark]

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.