What causes THIS error?

I see this a bunch of times, and can't figure out what causes it. The IP address is always registered to PayPal.

[02-Dec-2023 13:47:12 America/Los_Angeles] Request URI: /store/ipn_main_handler.php, IP address: 173.0.81.65, Language id 1
#1  trigger_error() called at [/home/domain/public_html/store/includes/classes/db/mysql/query_factory.php:667]
#2  queryFactory->show_error() called at [/home/domain/public_html/store/includes/classes/db/mysql/query_factory.php:634]
#3  queryFactory->set_error() called at [/home/domain/public_html/store/includes/classes/db/mysql/query_factory.php:275]
#4  queryFactory->Execute() called at [/home/domain/public_html/store/includes/modules/shipping/freeoptions.php:104]
#5  freeoptions->update_status() called at [/home/domain/public_html/store/includes/modules/shipping/freeoptions.php:85]
#6  freeoptions->__construct() called at [/home/domain/public_html/store/includes/classes/shipping.php:91]
#7  shipping->__construct() called at [/home/domain/public_html/store/ipn_main_handler.php:318]
--> 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 'ORDER BY zone_id' at line 5 :: SELECT zone_id
                   FROM zen_zones_to_geo_zones
                  WHERE geo_zone_id = 2
                    AND zone_country_id = 
                  ORDER BY zone_id ==> (as called by) /home/domain/public_html/store/includes/modules/shipping/freeoptions.php on line 104 <== in /home/domain/public_html/store/includes/classes/db/mysql/query_factory.php on line 667.

I might see WHY this is happening, but not sure.

We have a FREE SHIPPING zone. Inside of that zone, we defined two geographical areas to offer free shipping:

• Puerto Rico
• United States

However, the value for ZONE_ID is NULL for the United States:

Screenshot: https://i.postimg.cc/65WHVj7Q/screenshot-979.png

Could that be causing the error in the logs?

When we pull up the definitions for that zone, the only thing I can see is that "United States" doesn't allow us to select "All Zones". Just says "Please Select" and only offers individual states to choose from. But we want ALL the states to have the free shipping.

Screenshot: https://i.postimg.cc/8cB0pxj2/screenshot-978.png

Or maybe it means someone is checking out from a country that we don't allow? I guess what throws me for a loop is that the IP is registered to PayPal, so I don't know why this error is being flagged. The part of the code that is executing the SQL statement is:

SELECT zone_id
FROM " . TABLE_ZONES_TO_GEO_ZONES . "
WHERE geo_zone_id = " . (int)MODULE_SHIPPING_FREEOPTIONS_ZONE . "
AND zone_country_id = " . $order->delivery['country']['id'] . "
ORDER BY zone_id"

So I'm guessing that something incorrect is being used for the Country ID?

I think the problem is that $order->delivery['country']['id'] is EMPTY (blank).

Is there any reason why the ipn_main_handler.php would be logging an IP address from PayPal and the $order->delivery['country']['id'] is blank?

I am afraid this might be causing customers to not place orders due to some misconfiguration.

Jeff_Mash:

I might see WHY this is happening, but not sure.

We have a FREE SHIPPING zone. Inside of that zone, we defined two geographical areas to offer free shipping:

• Puerto Rico
• United States

However, the value for ZONE_ID is NULL for the United States:

Screenshot: https://i.postimg.cc/65WHVj7Q/screenshot-979.png

Could that be causing the error in the logs?

When we pull up the definitions for that zone, the only thing I can see is that "United States" doesn't allow us to select "All Zones". Just says "Please Select" and only offers individual states to choose from. But we want ALL the states to have the free shipping.

Screenshot: https://i.postimg.cc/8cB0pxj2/screenshot-978.png
That NULL country_id for the United States is absolutely what's causing that invalid SQL to be generated.

The "All Zones" selection, albeit not extremely intuitive, is an alias of the "Please Select" selection. That is, choose "Please Select" to choose all zones for a specific country.

It seems to me, that the issue may get resolved if the sql query value for the country id were cast to an integer. At least in that way the query would have proper formatting. It doesn't mean that the query would pull the proper data. Such a change would be:

SELECT zone_id
FROM " . TABLE_ZONES_TO_GEO_ZONES . "
WHERE geo_zone_id = " . (int)MODULE_SHIPPING_FREEOPTIONS_ZONE . "
AND zone_country_id = " . (int)$order->delivery['country']['id'] . "
ORDER BY zone_id"

mc12345678:

It seems to me, that the issue may get resolved if the sql query value for the country id were cast to an integer. At least in that way the query would have proper formatting. It doesn't mean that the query would pull the proper data. Such a change would be:

SELECT zone_id
FROM " . TABLE_ZONES_TO_GEO_ZONES . "
WHERE geo_zone_id = " . (int)MODULE_SHIPPING_FREEOPTIONS_ZONE . "
AND zone_country_id = " . (int)$order->delivery['country']['id'] . "
ORDER BY zone_id"

While that would provide a *work-around* for the issue, that's not a correction since that value *should be* set so we still need to determine the ***root-cause*** of the issue.

lat9:

While that would provide a work-around for the issue, that's not a correction since that value should be set so we still need to determine the root-cause of the issue.

Sure, allows sales to properly continue while other research continues about the fact that a default selection results in what had been reported as any of such values like a NULL, an empty quote or a value of zero such as false not otherwise forced to be displayed as a zero.

mc12345678:

Sure, allows sales to properly continue while other research continues about the fact that a default selection results in what had been reported as any of such values like a NULL, an empty quote or a value of zero such as false not otherwise forced to be displayed as a zero.
Again, the keyword is work-around.

I really appreciate BOTH your feedback on this. :)

I just typecast that line by putting (int) in front of the $order->delivery['country']['id'] variable.

Cindy, you mentioned that "Please Select" is an alias of the "All Zones." I do have that set up, even though the database has NULL in the zone_id.

Here, let me see if I can illustrate my settings to help pinpoint this issue.

Under Modules -> Shipping -> Free Shipping options, I have my "Shipping Zone" set to "Free Shipping Areas."

Under Zones Definitions, I created a "Free Shipping Areas" zone. That zone only has two entries:

• Puerto Rico (All Zones)
• United States (All Zone)

Screenshot: https://i.postimg.cc/QtCfSWnz/screenshot-980.png

If I click to EDIT the Puerto Rico entry, I can see that "All Zones" is an option for the zone: https://i.postimg.cc/j5y4MQ4Z/screenshot-981.png

If I click to EDIT the United States entry, it only says "Please Select": https://i.postimg.cc/vBXc84YG/screenshot-982.png

This "Free Shipping Areas" zone has an ID of 2.

When I go to the database and run a query (SELECT * FROM zen_zones_to_geo_zones WHERE geo_zone_id = 2; ), this is what I see: https://i.postimg.cc/RVwqqpVP/screenshot-983.png

As you can see, the zone_id for the United States is NULL. If that is causing the issue, should I just change it to "0" like the Puerto Rico entry?

Again, I should add, every time I see this error in the logs, the IP address always seems to be registered to PayPal.

Here are the two I've recently seen:

• 66.211.170.66
• 173.0.81.140

Just mentioning that in case it helps shed light on the error.

Jeff_Mash:

...
When I go to the database and run a query (SELECT * FROM zen_zones_to_geo_zones WHERE geo_zone_id = 2; ), this is what I see: https://i.postimg.cc/RVwqqpVP/screenshot-983.png

As you can see, the zone_id for the United States is NULL. If that is causing the issue, should I just change it to "0" like the Puerto Rico entry?
Jeff, you've got that bass-ackwards; the zone_id for the US (country id 223) is 0 (for all) while the one for Puerto Rico is NULL (since it doesn't have any defined states/provinces, aka zones).

lat9:

Jeff, you've got that bass-ackwards; the zone_id for the US (country id 223) is 0 (for all) while the one for Puerto Rico is NULL (since it doesn't have any defined states/provinces, aka zones).

LOL - I'm really good at walking bass-ackwards! Thanks for clearing that up!

I guess we wait and see if typecasting the variable as an (int) will help remove the error.

My main concern was that somehow, these errors indicated customers who were trying to checkout and not able to. We get complaints all the time about having trouble checking out, and I couldn't figure out if these errors were related.

The fact that all the IP's are related to PayPal made me wonder if this was a checkout related issue, and if I could be losing sales.

Jeff_Mash:

I really appreciate BOTH your feedback on this. :)

I just typecast that line by putting (int) in front of the $order->delivery['country']['id'] variable.

Cindy, you mentioned that "Please Select" is an alias of the "All Zones." I do have that set up, even though the database has NULL in the zone_id.

Here, let me see if I can illustrate my settings to help pinpoint this issue.

Under Modules -> Shipping -> Free Shipping options, I have my "Shipping Zone" set to "Free Shipping Areas."

Under Zones Definitions, I created a "Free Shipping Areas" zone. That zone only has two entries:

• Puerto Rico (All Zones)
• United States (All Zone)

Screenshot: https://i.postimg.cc/QtCfSWnz/screenshot-980.png

If I click to EDIT the Puerto Rico entry, I can see that "All Zones" is an option for the zone: https://i.postimg.cc/j5y4MQ4Z/screenshot-981.png

If I click to EDIT the United States entry, it only says "Please Select": https://i.postimg.cc/vBXc84YG/screenshot-982.png

This "Free Shipping Areas" zone has an ID of 2.

When I go to the database and run a query (SELECT * FROM zen_zones_to_geo_zones WHERE geo_zone_id = 2; ), this is what I see: https://i.postimg.cc/RVwqqpVP/screenshot-983.png

As you can see, the zone_id for the United States is NULL. If that is causing the issue, should I just change it to "0" like the Puerto Rico entry?

lat9:

Jeff, you've got that bass-ackwards; the zone_id for the US (country id 223) is 0 (for all) while the one for Puerto Rico is NULL (since it doesn't have any defined states/provinces, aka zones).

Jeff_Mash:

LOL - I'm really good at walking bass-ackwards! Thanks for clearing that up!

I guess we wait and see if typecasting the variable as an (int) will help remove the error.

My main concern was that somehow, these errors indicated customers who were trying to checkout and not able to. We get complaints all the time about having trouble checking out, and I couldn't figure out if these errors were related.

The fact that all the IP's are related to PayPal made me wonder if this was a checkout related issue, and if I could be losing sales.

Note that while looking at that window with united states selected and the drop down selection of "please select", selecting the update button will change that database value from 0 to null and that it would not return to a zero basically until the record was deleted or hard pressed to be a zero.

ZC 2.1
PHP 8.3
customer_tax_exempt_2.0.2
edit_orders-5.0.2
paypal4-1.2.1
sales_report-4.0.2
sitemapxml-4.0.4
usps-USPS_2024_08_25_K11l
zen_Image-Handler-5.3.5
zen_TyPackageTracker-5.0.1

Did a fix for this ever come about?
I received this same error today along with another one right at the same time.

First this error log:

[23-Sep-2025 18:35:22 America/Boise] Request URI: /index.php?main_page=shopping_cart, IP address: ***.***.***.***, Language id 1
#0 [internal function]: zen_debug_error_handler()
#1 /public_html/includes/classes/db/mysql/query_factory.php(734): trigger_error()
#2 /public_html/includes/classes/db/mysql/query_factory.php(679): queryFactory->show_error()
#3 /public_html/includes/classes/db/mysql/query_factory.php(307): queryFactory->set_error()
#4 /public_html/includes/modules/shipping/freeoptions.php(45): queryFactory->Execute()
#5 /public_html/includes/modules/shipping/freeoptions.php(30): freeoptions->update_status()
#6 /public_html/includes/classes/shipping.php(112): freeoptions->__construct()
#7 /public_html/includes/classes/shipping.php(55): shipping->initialize_modules()
#8 /public_html/includes/modules/shipping_estimator.php(160): shipping->__construct()
#9 /public_html/includes/templates/wsworx/templates/tpl_shopping_cart_default.php(204): require('...')
#10 /public_html/includes/templates/wsworx/common/tpl_main_page.php(181): require('...')
#11 /public_html/index.php(94): require('...')
--> PHP Fatal error: FATAL MySQL 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 'ORDER BY zone_id' at line 5 :: SELECT zone_id
                   FROM zones_to_geo_zones
                  WHERE geo_zone_id = 3
                    AND zone_country_id = 
                  ORDER BY zone_id ==> (as called by) /public_html/includes/modules/shipping/freeoptions.php on line 45 <== in /public_html/includes/classes/db/mysql/query_factory.php on line 734.

Then this warning came up with the exact same time creation:

[23-Sep-2025 18:35:22 America/Boise] Request URI: /index.php?main_page=shopping_cart, IP address: ***.***.***.**, Language id 1
#0 /public_html/includes/modules/shipping/freeoptions.php(49): zen_debug_error_handler()
#1 /public_html/includes/modules/shipping/freeoptions.php(30): freeoptions->update_status()
#2 /public_html/includes/classes/shipping.php(112): freeoptions->__construct()
#3 /public_html/includes/classes/shipping.php(55): shipping->initialize_modules()
#4 /public_html/includes/modules/shipping_estimator.php(160): shipping->__construct()
#5 /public_html/includes/templates/wsworx/templates/tpl_shopping_cart_default.php(204): require('...')
#6 /public_html/includes/templates/wsworx/common/tpl_main_page.php(181): require('...')
#7 /public_html/index.php(94): require('...')
--> PHP Warning: Undefined array key "country" in /public_html/includes/modules/shipping/freeoptions.php on line 49.

[23-Sep-2025 18:35:22 America/Boise] Request URI: /index.php?main_page=shopping_cart, IP address: ***.***.***.**, Language id 1
#0 /public_html/includes/modules/shipping/freeoptions.php(49): zen_debug_error_handler()
#1 /public_html/includes/modules/shipping/freeoptions.php(30): freeoptions->update_status()
#2 /public_html/includes/classes/shipping.php(112): freeoptions->__construct()
#3 /public_html/includes/classes/shipping.php(55): shipping->initialize_modules()
#4 /public_html/includes/modules/shipping_estimator.php(160): shipping->__construct()
#5 /public_html/includes/templates/wsworx/templates/tpl_shopping_cart_default.php(204): require('...')
#6 /public_html/includes/templates/wsworx/common/tpl_main_page.php(181): require('...')
#7 /public_html/index.php(94): require('...')
--> PHP Warning: Trying to access array offset on null in /public_html/includes/modules/shipping/freeoptions.php on line 49.

Thanks for the report, as the issue could still arise in the current repository.

Change line 49 of /includes/modules/shipping/freeoptions.php, adding the highlighted code fragments:

                   AND zone_country_id = " .[B] (int)([/B]$order->delivery['country']['id'][B] ?? -1) [/B]. "

lat9:

Thanks for the report, as the issue could still arise in the current repository.

Change line 49 of /includes/modules/shipping/freeoptions.php, adding the highlighted code fragments:

               AND zone_country_id = " .[B] (int)([/B]$order->delivery['country']['id'][B] ?? -1) [/B]. "

Thank You!