How to Show SaleMaker Sales on the Specials Page

When you are a customer browsing a website, you probably do not differentiate a special from a sale.  Why would you, they are exactly the same thing right?  Not as far as Zen Cart is concerned.  The default is that only specials display in their own listing and all sales items display inside the regular product listings and can only be found if the customer goes to these listings.  The only way to get all of your discounted products into a single place is to put them on special.  So what’s the problem with this approach?  Well, with Zen Cart you can only put one product on special at a time making it a very tedious task if you have more than a few products to discount…

Alright, so enough with the introductions, I’m sure you are already convinced that there is a problem with how Zen Cart handles discounted products.  Here is how you can show items discounted with the Zen Cart SaleMaker in the Specials product listing.

Step 1 of, well, 1…:

Open includes/modules/pages/specials/main_template_vars.php and find:

  $specials_query_raw = "SELECT p.products_id, p.products_image, pd.products_name,
                          p.master_categories_id
                         FROM (" . TABLE_PRODUCTS . " p
                         LEFT JOIN " . TABLE_SPECIALS . " s on p.products_id = s.products_id
                         LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id )
                         WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1'
                         AND s.status = 1
                         AND pd.language_id = :languagesID
                         ORDER BY s.specials_date_added DESC";

  $specials_query_raw = $db->bindVars($specials_query_raw, ':languagesID', $_SESSION['languages_id'], 'integer');
  $specials_split = new splitPageResults($specials_query_raw, MAX_DISPLAY_SPECIAL_PRODUCTS);

And replace with:

// INCLUDE SALE ITEMS IN SPECIALS LISTING
$sale_categories = $db->Execute("SELECT sale_categories_all FROM " . TABLE_SALEMAKER_SALES . " WHERE sale_status = 1");
if ($sale_categories->RecordCount() > 0) {
  $sale_categories_all = '';
  while(!$sale_categories->EOF) {
    $sale_categories_all .= substr($sale_categories->fields['sale_categories_all'], 0, -1); // remove trailing comma
    $sale_categories->MoveNext();
  }
  $sale_categories_all = substr($sale_categories_all, 1); // remove preceeding comma
  $specials_query_raw = "SELECT DISTINCT(p.products_id), p.products_image, pd.products_name, p.master_categories_id
                         FROM " . TABLE_PRODUCTS . " p
                         LEFT JOIN " . TABLE_SPECIALS . " s ON (s.products_id = p.products_id)
                         LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd ON (pd.products_id = p.products_id)
                         LEFT JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c ON (p2c.products_id = p.products_id)
                         WHERE p.products_status = '1'
                           AND ( (s.status = 1 AND p.products_id = s.products_id) OR (p2c.categories_id IN ($sale_categories_all)) )
                           AND p.products_id = pd.products_id
                           AND pd.language_id = :languagesID
                         GROUP BY p.products_id
                         ORDER BY p.products_id DESC";
  $specials_query_raw = $db->bindVars($specials_query_raw, ':languagesID', $_SESSION['languages_id'], 'integer');
  $specials_split = new splitPageResults($specials_query_raw, MAX_DISPLAY_SPECIAL_PRODUCTS, 'p.products_id');
} else {
  // DEFAULT ZEN CART SPECIALS LISTING
  $specials_query_raw = "SELECT p.products_id, p.products_image, pd.products_name, p.master_categories_id
                         FROM (" . TABLE_PRODUCTS . " p
                         LEFT JOIN " . TABLE_SPECIALS . " s on p.products_id = s.products_id
                         LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id )
                         WHERE p.products_id = s.products_id and p.products_id = pd.products_id and p.products_status = '1'
                           AND s.status = 1
                           AND pd.language_id = :languagesID
                        ORDER BY s.specials_date_added DESC";
  $specials_query_raw = $db->bindVars($specials_query_raw, ':languagesID', $_SESSION['languages_id'], 'integer');
  $specials_split = new splitPageResults($specials_query_raw, MAX_DISPLAY_SPECIAL_PRODUCTS);
}

Save the file and you are done. Pretty simple!

26 thoughts on “How to Show SaleMaker Sales on the Specials Page

  1. for those of you like me that suddenly got the “WARNING: An Error occurred, please refresh the page and try again.” using Zen Cart 1.5.1 – I dug around and for some reason when a new salemaker is being created its addin in 2 commas st the start of the field – which the MySQL cannot work with

    I changed

    $sale_categories_all = substr($sale_categories_all, 1); // remove preceeding comma

    too

    $sale_categories_all = substr($sale_categories_all, 2); // remove preceeding 2 comma’s

    and error went away – now I don’t know why this is happening now – as it was working fine

    but for those that just want a solution try it

    Thanks
    Sarah


  2. With the new code nothing shows up if there are products using Salemaker but none on Specials, otherwise, same Mysql error.

    PHP Fatal error: 1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘*) as total FROM products p LEFT JOIN specials s ON (s’ at line 1 :: select count(distinct *) as total FROM products p LEFT JOIN specials s ON (s.products_id = p.products_id) LEFT JOIN products_description pd ON (pd.products_id = p.products_id) LEFT JOIN products_to_categories p2c ON (p2c.products_id = p.products_id) WHERE p.products_status = ‘1’ AND ( (s.status = 1 AND p.products_id = s.products_id) OR (p2c.categories_id IN (32,1,4,5,6,7,8,9,16)) ) AND p.products_id = pd.products_id AND pd.language_id = 1 in D:\WAMP\www\zen2\includes\classes\db\mysql\query_factory.php on line 120



      1. yes, i will open a ticket but i suggest you try your modif on a fresh install of Zen 1.3.9h, will be way faster for you to check.


  3. This may actually be a bug in Zen Cart. If you use the keyword DISTINCT in your query, Zen Cart’s split_page_results.php class file will add DISTINCT * to the query which appears to be incompatible with some version of MySQL.

    I’ve posted a new version of the code above. Please try it and let me know if that resolves your issue.


  4. That SQL query is not part of the tutorial. I’m not sure where you or others are getting that error from, but it’s not from the code suggestion above. You must have other customizations that are being affected by the $listing_sql variable that is modified in this tutorial.


    1. That’s what i thought so I tried on a totally fresh install of same version no customizations, 1.3.9h, and I am getting same exact error.
      On the page it shows same message than Jack : WARNING: An Error occurred, please refresh the page and try again

      and in the logs, same Mysql error.


      1. Are you able to find where that query is coming from? I tried a search in the dev toolkit for OR (p2c.categories_id IN and found no results in ZC 1.5.1.



      2. I am using 1.3.9h, dont know for version 1.5 but the query is in your modification no?

        see last line

        $specials_query_raw = “SELECT DISTINCT(p.products_id), p.products_image, pd.products_name, p.master_categories_id
        FROM ” . TABLE_PRODUCTS . ” p
        LEFT JOIN ” . TABLE_SPECIALS . ” s ON (s.products_id = p.products_id)
        LEFT JOIN ” . TABLE_PRODUCTS_DESCRIPTION . ” pd ON (pd.products_id = p.products_id)
        LEFT JOIN ” . TABLE_PRODUCTS_TO_CATEGORIES . ” p2c ON (p2c.products_id = p.products_id)
        WHERE p.products_status = ‘1’
        AND ( (s.status = 1 AND p.products_id = s.products_id) OR (p2c.categories_id IN ($sale_categories_all)) )




  5. I’ve followed your instructions, but this doesn’t work on 1.5.1. Keep getting this error message on the page “WARNING: An Error occurred, please refresh the page and try again.” Any ideas? Thanks for the help.


  6. I have the EXACT same error as Brent in the comment above. I’ve looked high and low for the reason. I am using MySQL version 4.22 if that’s any help.

    By the way, you’ve done custom work for this site in the past.

    I’d appreciate any help you can offer.

    Thanks,
    John


  7. Hi,

    I appreciate you posting this, as it was exactly what I was looking for.

    However, I receive an SQL error when installing it:

    1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘*) as total FROM products p LEFT JOIN specials s ON (s’ at line 1
    in:
    [select count(distinct *) as total FROM products p LEFT JOIN specials s ON (s.products_id = p.products_id) LEFT JOIN products_description pd ON (pd.products_id = p.products_id) LEFT JOIN products_to_categories p2c ON (p2c.products_id = p.products_id) WHERE p.products_status = ‘1’ AND ( (s.status = 1 AND p.products_id = s.products_id) OR (p2c.categories_id IN (235,201)) ) AND p.products_id = pd.products_id AND pd.language_id = 1 ]

    I am unable to locate this error, and would like any insight you can offer on this.

    Thank you


  8. Don’t understand the comments, ie:

    Line 20: // remove trailing comma
    Line 23: // remove preceeding comma

    What exactly do we need to remove?

    Thanks for the mod!


  9. Thanks for the fix but what would I have to change to get the specials to show on the index main page in the center box? I have “Show Special Products on Main Page” set to “on” but with the fix the salemaker don’t show.


  10. Hello,

    sadly this doesnt work for me 🙁

    In the special category I do see that the sale entrys are counted (displaying the right amount of products) but the ones coming from the sale categorys are not actually listed with thumbnail etc.

    Did anyone have this problem as well?

    Is it due to the fact that i use a template?

    thanks
    Robert


    1. Yes. My template has the same SQL in my template file. Look for it and do the same replacement on includes/templates/YOUR_THEME/templates/tpl_specials_default.php


  11. hi Numinix…Thanks for the mod.. but i have 1 question before trying…
    after opening the php file.. i found out that it has another if function right before

    $specials_query_raw = “SELECT p.products_id, …………………………………….

    the if function is

    if (MAX_DISPLAY_SPECIAL_PRODUCTS > 0 ) {

    Is it the same as you mentioned and plant the new code given by you right below it?


  12. Numinix, please help…

    Having a bit of a problem, implemented it as per the instructions, however, when i go to my specials page… it still doesn’t list the sales.

    If you go to http://gbsa.tyrmida.co.za/shop/all-products

    you’ll see the first shoe top left (alma hi) is on special, and the shoe to the right of it (alma lo) is on sale.

    If you go to the specials page http://gbsa.tyrmida.co.za/shop/specials

    it only shows the alma hi.

    Please help, this is rather urgent

    Thanks.
    Shiff



  13. I followed your instruction to change the code, but I found there was a problem.

    In salesmaker, I put deduction of 15% only on products between $5.99 to $12.99, in the category “1st – 5th grade”. But in the special listing page, everything under the category “”1st – 5th grade” were there, including those items priced below $5.99. Therefore, in the listing page of “specials”, customers actually see lots of items “not on sale”. the website is: http://www.best4future.com/specials?page=1

    Could you please help me fix this problem? Thank you!!


    1. This is more complicated and not currently possible with this code. When we have the need to accomplish this for one of our clients, we’ll update this code.




Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Chat with us Contact Account Cart Search Cart Open Menu Arrow Link Arrow Chat Close Close Popup