Discount Codes

Discount Code Error! - Discount Codes

Discount Code Error!

by gabbysplaza » Sun Nov 21, 2021 8:52 pm

Please help, my site is falling over when customers try to use discount codes I issued them. The error generated is as follows:

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 '")) == 1' at line 5

SELECT * FROM information_schema.columns WHERE table_schema='". DB_DATABASE . "' AND table_name='orders' AND column_name like 'customers_guest'")) == 1

Please help urgently as I'm geting flooded with complaints and start a promo tomorrow morning.

Thank you!
gabbysplaza
Posts: 37
Joined: Tue Oct 26, 2021 5:33 pm
Contact:

Re: Discount Code Error!

by raiwa » Mon Nov 22, 2021 7:04 am

You should provide more precise information.
Please read this:
Need Help?viewtopic.php?f=10&t=27

Support for free addons is given voluntary, we are no firemen.
It's your responsibility to check that everything works in your shop before using addons and starting campaigns.

I just checked in my test store (Phoenix 1.0.8.7 and Discount Codes 5.6.1, PHP 7.4) and everything works like expected.
As a hotfix workaround and if you are not using PWA Guest checkout, you could remove or comment out the code which produces the error:
In: includes/modules/order_totals/ot_discount.php line 111-141:

Code: Select all

            	// Support for PWA guest orders BEGIN
            	if (mysqli_num_rows($db->query(<<<'EOSQL'
SELECT *
  FROM information_schema.columns
  WHERE table_schema='". DB_DATABASE . "'
    AND table_name='orders'
    AND column_name like 'customers_guest'")) == 1
EOSQL
              ))) {
            	  $check_query_mail = $db->query(sprintf(<<<'EOSQL'
SELECT customers_email_address
  FROM customers
  WHERE customers_id = %s
EOSQL
        , (int)$customer_id));

            	  $check_mail = $check_query_mail->fetch_assoc();
            	  if (!empty($check_mail['customers_email_address'])) {
            	    $check_query_order_guest = $db->query(sprintf(<<<'EOSQL'
SELECT count(*) AS orders
  FROM orders
  WHERE customers_email_address = %s
    AND customers_guest = '1'");
EOSQL
        , $check_mail['customers_email_address']));

            	    $check_order_guest = $check_query_order_guest->fetch_assoc();
            	    $orders = $orders + $check_order_guest['orders'];
            	  }
            	}
            	// Support for PWA guest orders END
But be aware that if you get this error for code for which no one else has reported problems, you may get more problems because it may be due to an incompatible db version. Maybe you are running PHP8?
Public Phoenix Change Log Cheat Set on Google Sheets
https://docs.google.com/spreadsheets/d/ ... sp=sharing

Need Help?viewtopic.php?f=10&t=27
raiwa
PhoenixCart Developer
PhoenixCart Developer
Posts: 1184
Joined: Sat Dec 21, 2019 8:08 am
Contact:

Re: Discount Code Error!

by raiwa » Mon Nov 22, 2021 10:12 am

Just checked again and yes, there are some errors in the 2 queries. They appear only if using together with PWA Guest Checkout:
Change the above mentioned part to:

Code: Select all

            	// Support for PWA guest orders BEGIN
            	if (mysqli_num_rows($db->query(sprintf(<<<'EOSQL'
SELECT *
  FROM information_schema.columns
  WHERE table_schema = '%s'
    AND table_name='orders'
    AND column_name like 'customers_guest' == 1
EOSQL
                , DB_DATABASE));
            	  $check_query_mail = $db->query(sprintf(<<<'EOSQL'
SELECT customers_email_address
  FROM customers
  WHERE customers_id = %s
EOSQL
                , (int)$customer_id));

            	  $check_mail = $check_query_mail->fetch_assoc();
            	  if (!empty($check_mail['customers_email_address'])) {
            	    $check_query_order_guest = $db->query(sprintf(<<<'EOSQL'
SELECT count(*) AS orders
  FROM orders
  WHERE customers_email_address = %s
    AND customers_guest = '1');
EOSQL
        , $check_mail['customers_email_address']));

            	    $check_order_guest = $check_query_order_guest->fetch_assoc();
            	    $orders = $orders + $check_order_guest['orders'];
            	  }
            	}
            	// Support for PWA guest orders END
Please confirm and I'll update the package.
Public Phoenix Change Log Cheat Set on Google Sheets
https://docs.google.com/spreadsheets/d/ ... sp=sharing

Need Help?viewtopic.php?f=10&t=27
raiwa
PhoenixCart Developer
PhoenixCart Developer
Posts: 1184
Joined: Sat Dec 21, 2019 8:08 am
Contact:

Re: Discount Code Error!

by gabbysplaza » Mon Nov 22, 2021 10:32 am

raiwa wrote:Just checked again and yes, the last double quotes in 2 queries should be removed. You are probably running a lower PHP version (PHP 7.0 - 7.2). The higher PHP versions allow this.
Change the above mentioned part to:

Code: Select all

            	// Support for PWA guest orders BEGIN
            	if (mysqli_num_rows($db->query(<<<'EOSQL'
SELECT *
  FROM information_schema.columns
  WHERE table_schema='". DB_DATABASE . "'
    AND table_name='orders'
    AND column_name like 'customers_guest' == 1
EOSQL
              ))) {
            	  $check_query_mail = $db->query(sprintf(<<<'EOSQL'
SELECT customers_email_address
  FROM customers
  WHERE customers_id = %s
EOSQL
        , (int)$customer_id));

            	  $check_mail = $check_query_mail->fetch_assoc();
            	  if (!empty($check_mail['customers_email_address'])) {
            	    $check_query_order_guest = $db->query(sprintf(<<<'EOSQL'
SELECT count(*) AS orders
  FROM orders
  WHERE customers_email_address = %s
    AND customers_guest = '1');
EOSQL
        , $check_mail['customers_email_address']));

            	    $check_order_guest = $check_query_order_guest->fetch_assoc();
            	    $orders = $orders + $check_order_guest['orders'];
            	  }
            	}
            	// Support for PWA guest orders END
Please confirm and I'll update the package.
Thanks for that update, it appears to have resolved my issue.
gabbysplaza
Posts: 37
Joined: Tue Oct 26, 2021 5:33 pm
Contact:

Re: Discount Code Error!

by raiwa » Mon Nov 22, 2021 11:00 am

Version 5.6.2 with the above fixes uploaded.
Public Phoenix Change Log Cheat Set on Google Sheets
https://docs.google.com/spreadsheets/d/ ... sp=sharing

Need Help?viewtopic.php?f=10&t=27
raiwa
PhoenixCart Developer
PhoenixCart Developer
Posts: 1184
Joined: Sat Dec 21, 2019 8:08 am
Contact: