Coupon error with restrictions set

[LissaE]

I am getting the following error with coupons when there is a category set to allow and then a product within that category also set to allow, when attempting to use the coupon customers get a blank page with the following message

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 'AND coupon_restrict = 'Y' AND coupon_id = 53 LIMIT 1' at line 3
in:
[SELECT count(*) AS total FROM coupon_restrict AND coupon_restrict = 'Y' AND coupon_id = 53 LIMIT 1] 

...the error goes away if I remove the category restriction and just keep the product restriction....how do I fix this?

[Ajeh]

First if you have an Allow on the Category why do you need an Allow on the Product in that Category?

I just made a Discount Coupon that is setup as:
TOP DENY
Category 53 ALLOW

Product in Category 53 ALLOW

and I am not getting an error ...

I added Products from Category 53 and did a checkout without a problem ...

I added Products from Category 53 and the Product that is also Allowed and did a checkout without a problem ...

I added Products from Category 53 and the Product that is also Allowed and from another Category and did a checkout without a problem ...

So far I am unable to reproduce the error ...

Where are you seeing this error?

NOTE: you may wish to check the Known Bug Fixes and apply them:
http://www.zen-cart.com/forum/showthread.php?t=82619

And now is a good time to ensure that you have all of the Secure Patches and Updates:
http://www.zen-cart.com/forum/showthread.php?t=131115

[LissaE]

I have a commercial module addon that gives my designers access to their products, sales and coupons etc through a seperate folder so they don't access the main admin folder and when adding a coupon it is automatically set to allow their master category so the coupon is restricted and not valid on the whole store lol

Many times we gives out coupons that we only want to allow the customers to use on a single product, which is where both those allow restrictions come in.

I could be wrong, but I believe this error is only happening when the coupon is restricted to a single use (not just per customer, but actual one-time use), I have yet to see it happen if the usage is unrestricted or restricted to 2 or more (cross fingers lol) and as soon as I remove the top allow for the category the error doesn't happen any more.

I have all security patches and bugfixes applied....as far as I am aware I didn't miss any, but it could happen LOL

[Ajeh]

If you only want the Discount Coupon for 1 Product, why not just set to:

TOP DENY

Product wanted ALLOW

Now it is only good on 1 Product ...

[LissaE]

Linda, I do go in and do that after I discover it, but as I said up there lol the mod I have that allows the designers to create their own coupons (without accessing the main admin or anyone else's area/info) sets the category restrictions....otherwise all coupons they create could be used on the whole store and not just their category.

If there's no fix, I will just have to make sure I check for new coupons created regularly lol

[Ajeh]

So far ... I haven't reproduced the error ...

I will keep fooling with it and see if I can ...

[LissaE]

No worries Ajeh, and thanx....I like to bring challenging moments LOL!

[LissaE]

Ajeh, this issue has been resolved, after several errors I had my programmer track down the problem for me...turned out it was most likely "user error" with me making a mistake when I applied the bugfixes LOL!

includes/functions/functions_general.php

$sql = "SELECT count(*) AS total
            FROM " . TABLE_COUPON_RESTRICT . "
            AND coupon_restrict = 'Y'
            AND coupon_id = " . (int)$coupon_id . " LIMIT 1"; 

around line 847 thru 850
changed to

$sql = "SELECT count(*) AS total
            FROM " . TABLE_COUPON_RESTRICT . "
            WHERE category_id = -1
            AND coupon_restrict = 'Y'
            AND coupon_id = " . (int)$coupon_id . " LIMIT 1"; 

Thanx for looking into it for me

[Ajeh]

Thanks for the update on what the problem was ...

[semaxd]

ok so this is the closest thing I have found to my problem.

I just installed the coupon module and when I click on coupon admin i get this;
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 '-0, 0' at line 1
in:
[select coupon_id, coupon_code, coupon_amount, coupon_type, coupon_start_date,coupon_expire_date,uses_per_user,uses_per_coupon,restrict_to_p roducts, restrict_to_categories, date_created,date_modified, coupon_active, coupon_zone_restriction from zen_coupons where coupon_active='Y' and coupon_type != 'G' limit -0, 0]
If you were entering information, press the BACK button in your browser and re-check the information you had entered to be sure you left no blank fields

I went to the file LissaE listed above for my site and it doesnt have that line of code.
(I am presently using: v1.3.9e)
the site has been up fore a year, we just decided to install the coupon module.
http://littlepeoplecustoms.com/toolbox/index.php

any help would be great thanks
jim