Guest Posted January 24, 2005 Share Posted January 24, 2005 I'm wondering how to adapt this query from the above mod: $random_products_query = tep_db_query("SELECT products_id, products_model, products_price from " . TABLE_PRODUCTS . " WHERE products_status ='1' and products_price > '". SPECIAL_PRODUCT_MIN_PRICE ."' order by rand() DESC limit " . NUMBER_SPECIALS); to exclude 3 of my categories. Would something like this work? $random_products_query = tep_db_query("SELECT products_id, products_model, products_price from " . TABLE_PRODUCTS . " WHERE products_status ='1' and products_price > '". SPECIAL_PRODUCT_MIN_PRICE ."' and SELECT categories_id from " . TABLE_PRODUCTS_TO_CATEGORIES . " WHERE `categories_id` NOT LIKE 65 and `categories_id` NOT LIKE 44 and `categories_id` NOT LIKE 71 order by rand() DESC limit " . NUMBER_SPECIALS); Quote Link to comment Share on other sites More sharing options...
Jan Zonjee Posted January 24, 2005 Share Posted January 24, 2005 Personally, my first try would be: $random_products_query = tep_db_query("SELECT products_id, products_model, products_price from " . TABLE_PRODUCTS . ", " . TABLE_PRODUCTS_TO_CATEGORIES . " WHERE products_status ='1' and products_price > '". SPECIAL_PRODUCT_MIN_PRICE ."' and (categories_id != '65' and categories_id != '44' and categories_id != '71') order by rand() DESC limit " . NUMBER_SPECIALS);but then again, I'm not an SQL-expert... Quote Link to comment Share on other sites More sharing options...
Jan Zonjee Posted January 25, 2005 Share Posted January 25, 2005 Probably that wouldn't have worked, so then I would have tried: $random_products_query = tep_db_query("SELECT products_id, products_model, products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . "? pc WHERE products_status ='1' and products_price > '". SPECIAL_PRODUCT_MIN_PRICE ."' and p.products_id = pc.products_id and (categories_id != '65' and? categories_id != '44' and categories_id != '71') order by rand() DESC limit " . NUMBER_SPECIALS); Quote Link to comment Share on other sites More sharing options...
Guest Posted January 25, 2005 Share Posted January 25, 2005 Probably that wouldn't have worked, so then I would have tried: $random_products_query = tep_db_query("SELECT products_id, products_model, products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " pc WHERE products_status ='1' and products_price > '". SPECIAL_PRODUCT_MIN_PRICE ."' and p.products_id = pc.products_id and (categories_id != '65' and categories_id != '44' and categories_id != '71') order by rand() DESC limit " . NUMBER_SPECIALS); <{POST_SNAPBACK}> Thank you but it says: 1052 - Column: 'products_id' in field list is ambiguous Hmm- maybe there's a way to have it check the special product against the category after it's been selected as it does if something's already on special? Quote Link to comment Share on other sites More sharing options...
Jan Zonjee Posted January 25, 2005 Share Posted January 25, 2005 maybe there's a way to have it check the special product against the category after it's been selected as it does if something's already on special?Sure, but it is more work. Easier and more efficient to let mysql do it.The error is easy to fix (SELECT products_id -> SELECT p.products_id), but another one might come up or it might not work properly, anyway you could try: $random_products_query = tep_db_query("SELECT p.products_id, products_model, products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " ?pc WHERE products_status ='1' and products_price > '". SPECIAL_PRODUCT_MIN_PRICE ."' and p.products_id = pc.products_id and (categories_id != '65' and ?categories_id != '44' and categories_id != '71') order by rand() DESC limit " . NUMBER_SPECIALS); Quote Link to comment Share on other sites More sharing options...
Guest Posted January 25, 2005 Share Posted January 25, 2005 Sure, but it is more work. Easier and more efficient to let mysql do it.The error is easy to fix (SELECT products_id -> SELECT p.products_id), but another one might come up or it might not work properly, anyway you could try: $random_products_query = tep_db_query("SELECT p.products_id, products_model, products_price from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_TO_CATEGORIES . " pc WHERE products_status ='1' and products_price > '". SPECIAL_PRODUCT_MIN_PRICE ."' and p.products_id = pc.products_id and (categories_id != '65' and categories_id != '44' and categories_id != '71') order by rand() DESC limit " . NUMBER_SPECIALS); <{POST_SNAPBACK}> Great- that seems to be working- now it's just a matter of time I guess to see if any of my freebies or club memberships show up as the daily special LOL! Thank you! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.