Last Updated on Apr 15, 2014 by Jeff Lew
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:
13 14 15 16 17 18 19 20 21 22 23 24 | $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:
13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | // 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!
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
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
Can you please open a ticket at https://www.numinix.com/support/. We’ll have to take a look at your files and see what is happening here. Once we have that resolved we’ll post an update to this tutorial or explanation if a mistake is being made on your end.
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.
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.
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.
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.
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.Just tested on ZC 1.5.1 and did not encounter the MySQL error.
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)) )
Hi
Using 1.3.9h I too have the same MYSQl error than Brent and John…. Is there a solution for this?
Thanks
Ditto, would really love this to work on 1.5.1. Thanks!!
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.
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
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
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!
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.
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
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
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?
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
perfect thank you for this tip – exactly what I needed and worked out of the box!
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!!
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.
Great mod. only one small problem… there is a quote mark in your code above after the word languageID” Just wanted to let you know. Thanks, it still saved plenty of time.
Thank you for pointing that out 🙂