Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

random_specials


Guest

Recommended Posts

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);

Link to comment
Share on other sites

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...
Link to comment
Share on other sites

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);

Link to comment
Share on other sites

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);

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?

Link to comment
Share on other sites

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);

Link to comment
Share on other sites

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);

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!

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...