What causes THIS error?

[Jeff_Mash]

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.

[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

[Jeff_Mash]

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?

[Jeff_Mash]

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.

[lat9]

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.

[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"

[lat9]

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.

[mc12345678]

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.

[lat9]

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.

[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?