Forum

Thread tagged as: Problem, Shop

Promo Deletes Product(s) in Cart at Checkout

sooo ... if I use no promo code, everything works as normal ...

if I add a promo code (5% off), then AFTER checkout it deletes the products ... you the customer pays for the items, but all the products are deleted from the cart so nothing shows up!

when viewing the confirmation page with payment, everything appears correctly ...

when I look in the database under perch3_shop_order_items all I see is shipping and no product ... it's like its literally being deleted from the database

this is the debug on the success page ...

Debug Message - Perch 3.0.10
[1] SELECT * FROM perch3_members_sessions WHERE sessionID='76515018eb349d49c07a9c0efbeb9782e80ddca8' AND sessionHttpFootprint='1e5c8adc7811abc667632b38899990ad78c0ea2c' AND sessionExpires>'2017-10-12 23:18:50' LIMIT 1
User is logged in
[1] SELECT * FROM perch3_pages WHERE pagePath='/hta.php' LIMIT 1
[1] SELECT * FROM perch3_shop_cart WHERE cartID=8
[1] SELECT * FROM perch3_shop_cart WHERE cartID=8
[0] SELECT setID FROM perch3_category_sets WHERE setSlug='' LIMIT 1
[4] SELECT main.* FROM perch3_categories main WHERE 1=1 ORDER BY catTreePosition ASC
[4] Using template: /templates/categories/menu-mobile.html
[0] SELECT setID FROM perch3_category_sets WHERE setSlug='' LIMIT 1
[4] SELECT main.* FROM perch3_categories main WHERE 1=1 ORDER BY catTreePosition ASC
[4] Using template: /templates/categories/menu-navbar.html
[1] SELECT * FROM perch3_shop_orders WHERE orderID=94 AND orderDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_customers WHERE memberID=12
[9] SELECT statusKey FROM perch3_shop_order_statuses WHERE statusDeleted IS NULL ORDER BY statusIndex ASC
[1] SELECT * FROM perch3_shop_orders WHERE customerID=12 AND orderID=94 AND orderStatus IN ('paid', 'processing', 'cancelled', 'dispatched', 'returned', 'partial_refund', 'refunded')
[1] SELECT * FROM perch3_shop_order_items WHERE orderID='94' ORDER BY itemID ASC
[1] SELECT * FROM perch3_shop_order_statuses WHERE statusKey='paid' AND statusDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_currencies WHERE currencyID=59 LIMIT 1
[1] SELECT * FROM perch3_shop_shippings WHERE shippingID=3 AND shippingDeleted IS NULL LIMIT 1
[31] SELECT DISTINCT settingID, settingValue FROM perch3_settings WHERE userID=0
[1] SELECT * FROM perch3_shop_currencies WHERE currencyActive=1 AND currencyID=59
[1] SELECT promoID FROM perch3_shop_order_promotions WHERE orderID=94
[1] SELECT * FROM perch3_shop_promotions WHERE promoID=2 LIMIT 1
[1] Using template: /templates/shop/orders/order.html
[1] SELECT * FROM perch3_shop_customers WHERE memberID=12
[2] SELECT main.* FROM perch3_shop_addresses main WHERE 1=1 AND (customerID=12) AND addressDeleted IS NULL AND orderID IS NULL ORDER BY addressTitle ASC
[2] Using template: /templates/shop/addresses/list.html
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
DELETE FROM perch3_shop_cart WHERE cartID='8'
------------------------------ Calculating cart ------------------------------
[1] SELECT * FROM perch3_shop_cart WHERE cartID=0
[1] SELECT * FROM perch3_shop_cart WHERE cartID=0
[1] SELECT * FROM perch3_shop_tax_locations WHERE locationID=0 AND locationDeleted IS NULL LIMIT 1
[1] SELECT locationID FROM perch3_shop_tax_locations WHERE locationIsHome=1 LIMIT 1
[1] SELECT * FROM perch3_shop_tax_locations WHERE locationID=1 AND locationDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_currencies WHERE currencyID=0 LIMIT 1
[1] SELECT * FROM perch3_shop_currencies WHERE currencyActive=1 AND currencyID=59
[1] SELECT COUNT(productID) AS product_count, SUM(itemQty) AS item_count FROM perch3_shop_cart_items WHERE cartID=0
Tax mode: inc
[nil] SELECT * FROM perch3_shop_cart_items WHERE cartID=0 ORDER BY itemID ASC
[1] SELECT * FROM perch3_members WHERE memberID='12' LIMIT 1
Using template: /templates/members/forms/set_password.html

Perch: 3.0.10
Production mode: Production (100)
Installed apps: content (3.0.10), assets (3.0.10), categories (3.0.10), perch_forms (1.9.1), perch_shop_orders (1.2.5), perch_shop_products (1.2.5), perch_shop (1.2.5), perch_members (1.6.2)
DB driver: PDO
DB tables: custom_most_popular (0), perch3_categories (4), perch3_category_counts (0), perch3_category_sets (1), perch3_content_index (52), perch3_content_items (19), perch3_content_regions (6), perch3_forms (1), perch3_forms_responses (0), perch3_members (5), perch3_members_forms (1), perch3_members_member_tags (0), perch3_members_sessions (3), perch3_members_tags (0), perch3_menu_items (13), perch3_navigation (0), perch3_navigation_pages (0), perch3_page_templates (1), perch3_pages (6), perch3_resource_log (1783), perch3_resource_tags (0), perch3_resources (1491), perch3_resources_to_tags (0), perch3_settings (31), perch3_shop_addresses (184), perch3_shop_admin_index (160), perch3_shop_brands (1), perch3_shop_cart (0), perch3_shop_cart_data (4), perch3_shop_cart_items (4), perch3_shop_countries (50), perch3_shop_currencies (152), perch3_shop_customers (12), perch3_shop_emails (1), perch3_shop_index (12978), perch3_shop_option_values (107), perch3_shop_options (1), perch3_shop_order_items (9), perch3_shop_order_promotions (3), perch3_shop_order_statuses (9), perch3_shop_orders (6), perch3_shop_orders_meta (1), perch3_shop_product_files (0), perch3_shop_product_option_values (121), perch3_shop_product_options (86), perch3_shop_product_tags (0), perch3_shop_products (224), perch3_shop_promotions (2), perch3_shop_sales (0), perch3_shop_search (203), perch3_shop_shipping_zone_countries (50), perch3_shop_shipping_zones (4), perch3_shop_shippings (5), perch3_shop_tax_exhibits (191), perch3_shop_tax_group_rates (56), perch3_shop_tax_groups (2), perch3_shop_tax_locations (28), perch3_shop_tax_rates (40), perch3_shop_variants (135), perch3_user_passwords (0), perch3_user_privileges (72), perch3_user_role_privileges (2), perch3_user_roles (2), perch3_users (2)
Users: 2
App runtimes:
<?php
    $apps_list = [
        'content',
        'categories',
        'perch_members',
        'perch_shop',
        'perch_forms',
    ];
Editor plug-ins:
H1: 8f4d668e1c91ebaf59bc1287cd7dc318
L1: 97d1f766ac7b08a09f98dff79590a26b
F1: 3b606135b33e6a102526838f4152a807
headerColour: #04ffb6
content_singlePageEdit: 1
helpURL:
siteURL: /
hideBranding: 1
content_collapseList: 1
lang: en-gb
installedAt: 3.0.8
update_3.0.8: done
latest_version:
on_sale_version: 3.0.10
perch_shop_update: 1.2.5
headerScheme: light
perch_members_login_page: /accounts/login.php
perch_shop_price_tax_mode: inc
perch_shop_product_url: /product/{slug}
perch_shop_default_currency: 59
perch_shop_reporting_currency: 59
dashboard: 1
hide_pwd_reset: 0
keyboardShortcuts: 0
content_hideNonEditableRegions: 0
content_frontend_edit: 0
content_skip_region_list: 1
logoPath: /perch/resources/logo-2.png
perch_members_update: 1.4
update_3.0.9: done
assets_restrict_buckets: 0
perch_shop_site_url: https://www.becalistyle.com
update_3.0.10: done
perch_shop_invoice_number_format: Invoice-%d
PERCH_DEVELOPMENT: 10
PERCH_STAGING: 50
PERCH_PRODUCTION: 100
PERCH_DB_SERVER: localhost
PERCH_DB_USERNAME: stuntdawg
PERCH_DB_DATABASE: becalistyle
PERCH_DB_PREFIX: perch3_
PERCH_TZ: UTC
PERCH_EMAIL_FROM: blake.myers@xenoplexus.com
PERCH_EMAIL_FROM_NAME: BeCali Orders
PERCH_LOGINPATH: /perch
PERCH_PATH: /Users/bmyers/Sites/becalistyle/perch
PERCH_CORE: /Users/bmyers/Sites/becalistyle/perch/core
PERCH_RESFILEPATH: /Users/bmyers/Sites/becalistyle/perch/resources
PERCH_RESPATH: /perch/resources
PERCH_HTML5: 1
PERCH_DEBUG: 1
PERCH_EMAIL_METHOD: smtp
PERCH_EMAIL_HOST: smtp.postmarkapp.com
PERCH_EMAIL_AUTH: 1
PERCH_EMAIL_PORT: 587
PERCH_EMAIL_USERNAME: xxxxxx
PERCH_TEMPLATE_FILTERS: 1
PERCH_RUNWAY:
PERCH_ERROR_MODE: DIE
PERCH_DATE_LONG: %d %B %Y
PERCH_DATE_SHORT: %d %b %Y
PERCH_TIME_SHORT: %H:%M
PERCH_TIME_LONG: %H:%M:%S
PERCH_RUNWAY_ROUTED:
PERCH_STRONG_PASSWORDS:
PERCH_ASSET_VERSION: 23f8a1427c17ade109a3
PERCH_PREVIEW_ARG: preview
PERCH_TEMPLATE_PATH: /Users/bmyers/Sites/becalistyle/perch/templates
PERCH_DEFAULT_DOC: index.php
PERCH_DEFAULT_EXT: .php
PERCH_PRODUCTION_MODE: 100
PERCH_XHTML_MARKUP:
PERCH_RWD: 1
PERCH_HTML_ENTITIES:
PERCH_SSL:
PERCH_STRIPSLASHES:
PERCH_PROGRESSIVE_FLUSH: 1
PERCH_PARANOID:
PERCH_FORCE_SECURE_COOKIES:
PERCH_DEFAULT_BUCKET: default
PERCH_TRANSLATION_ASSIST:
PERCH_PASSWORD_MIN_LENGTH: 6
PERCH_MAX_FAILED_LOGINS: 10
PERCH_AUTH_LOCKOUT_DURATION: 1 HOUR
PERCH_VERIFY_UPLOADS:
PERCH_PRIV_ASSIST:
PERCH_CUSTOM_EDITOR_CONFIGS:
PERCH_ENABLE_EXIF: 1
PERCH_AUTH_PLUGIN:
PERCH_DB_CHARSET: utf8
PERCH_DB_PORT:
PERCH_DB_SOCKET:
PERCH_SHOP_VERSION: 1.2.5
PERCH_APPS_EDITOR_PLUGIN: markitup
PERCH_APPS_EDITOR_MARKUP_LANGUAGE: markdown
Hosting settings

PHP: 7.1.8
Zend: 3.1.0
OS: Darwin
SAPI: apache2handler
Safe mode: not detected
MySQL client: mysqlnd 5.0.12-dev - 20150407 - $Id: b396954eeb2d1d9ed7902b8bae237b287f21ad9e $
MySQL server: 5.6.31
Free disk space: 768.02 GB
Extensions: Core, date, libxml, openssl, pcre, sqlite3, zlib, bcmath, bz2, calendar, ctype, curl, dba, dom, hash, fileinfo, filter, ftp, gd, gettext, SPL, iconv, json, ldap, mbstring, session, standard, odbc, mysqlnd, PDO, pdo_mysql, PDO_ODBC, pdo_sqlite, Phar, posix, Reflection, mysqli, shmop, SimpleXML, soap, sockets, exif, sysvmsg, sysvsem, sysvshm, tokenizer, wddx, xml, xmlreader, xmlrpc, xmlwriter, xsl, zip, apache2handler, geoip, imagick, mcrypt
GD: Yes
ImageMagick: Yes
PHP max upload size: 128M
PHP max form post size: 128M
PHP memory limit: 128M
Total max uploadable file size: 128M
Resource folder writeable: Yes
Session timeout: 24 minutes
Native JSON: Yes
Filter functions: Yes
Transliteration functions: No
HTTP_HOST: becalistyle.local
HTTP_ACCEPT_ENCODING: br, gzip, deflate
HTTP_COOKIE: PHPSESSID=t6dqu5im3n7htbdrimftgo7cq8; cmsa=1; p_m=76515018eb349d49c07a9c0efbeb9782e80ddca8
HTTP_ACCEPT: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
HTTP_USER_AGENT: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_13) AppleWebKit/604.1.38 (KHTML, like Gecko) Version/11.0 Safari/604.1.38
HTTP_ACCEPT_LANGUAGE: en-us
HTTP_REFERER: https://becalistyle.local/perch/core/settings/diagnostics/
HTTP_DNT: 1
HTTP_X_FORWARDED_PROTO: https
HTTP_X_FORWARDED_PORT: 443
HTTP_X_FORWARDED_FOR: 127.0.0.1
HTTP_X_FORWARDED_HOST: becalistyle.local
HTTP_X_FORWARDED_SERVER: becalistyle.local
HTTP_CONNECTION: Keep-Alive
PATH: /usr/bin:/bin:/usr/sbin:/sbin
SERVER_SIGNATURE: <address>Apache Server at becalistyle.local Port 443</address>
SERVER_SOFTWARE: Apache
SERVER_NAME: becalistyle.local
SERVER_ADDR: 127.0.0.1
SERVER_PORT: 443
REMOTE_ADDR: 127.0.0.1
DOCUMENT_ROOT: /Users/bmyers/Sites/becalistyle
REQUEST_SCHEME: https
CONTEXT_DOCUMENT_ROOT: /Users/bmyers/Sites/becalistyle
SERVER_ADMIN: admin@example.com
SCRIPT_FILENAME: /Users/bmyers/Sites/becalistyle/perch/core/settings/diagnostics/index.php
REMOTE_PORT: 52971
GATEWAY_INTERFACE: CGI/1.1
SERVER_PROTOCOL: HTTP/1.1
REQUEST_METHOD: GET
QUERY_STRING: extended
REQUEST_URI: /perch/core/settings/diagnostics/?extended
SCRIPT_NAME: /perch/core/settings/diagnostics/index.php
PHP_SELF: /perch/core/settings/diagnostics/index.php
REQUEST_TIME_FLOAT: 1507843323.152
REQUEST_TIME: 1507843323
Blake Myers

Blake Myers 0 points

  • 3 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

3 years ago

Do you get any MySQL errors on the insert?
Blake Myers

Blake Myers 0 points

3 years ago

None that were viewable on screen ... I believe it’s happening at checkout() which is called via AJAX ... but it’s returning the appropriate JSON response to continue to the next step (I hold redirects).

I will check my php/mysql logs.
Blake Myers

Blake Myers 0 points

3 years ago

here's what I found ... I removed checkout from AJAX and had it run with a debugger from a regular page.

I've narrowed it down to this ...

it looks like it wants to insert the shipping but not the product into the database

UPDATE perch3_shop_orders SET orderBillingAddress='212', orderShippingAddress='213', orderUpdated='2017-10-13 14:09:26' WHERE orderID='96'
No ids to log.
INSERT INTO perch3_shop_order_items(itemType,orderID,productID,itemPrice,itemTax,itemTotal,itemQty,itemTaxRate,itemDiscount,itemTaxDiscount) VALUES('product','96','219','6590','0','6590','1',0,329,5,0)
Invalid query: SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1
INSERT INTO perch3_shop_order_items(itemType,orderID,shippingID,itemPrice,itemTax,itemTotal,itemQty,itemTaxRate,itemDiscount,itemTaxDiscount) VALUES('shipping','96','3','1258','0','1258',1,0,'0','0')
[1] SELECT * FROM perch3_shop_order_items WHERE itemID='187' LIMIT 1
No ids to log.
UPDATE perch3_shop_order_items SET itemType='shipping', orderID='96', shippingID='3', itemPrice='1258', itemTax='0', itemTotal='1258', itemQty=1, itemTaxRate=0, itemDiscount='0', itemTaxDiscount='0' WHERE itemID='187'
No ids to log.
Blake Myers

Blake Myers 0 points

3 years ago

here's the whole log for better context ....

Debug Message - Perch 3.0.10
[1] SELECT * FROM perch3_members_sessions WHERE sessionID='8ec347c7129821c0f32c123f6b58ad6e0bd38365' AND sessionHttpFootprint='1e5c8adc7811abc667632b38899990ad78c0ea2c' AND sessionExpires>'2017-10-13 14:09:26' LIMIT 1
User is logged in
[1] SELECT * FROM perch3_pages WHERE pagePath='/hta.php' LIMIT 1
[1] SELECT * FROM perch3_shop_cart WHERE cartID=12
[1] SELECT * FROM perch3_shop_cart WHERE cartID=12
Holding redirects
Checking out with manual
Member ID: 11
[1] SELECT * FROM perch3_shop_customers WHERE memberID=11
[1] SELECT billingAddress, shippingAddress FROM perch3_shop_cart WHERE billingAddress IS NOT NULL AND cartID=12
[1] SELECT * FROM perch3_shop_addresses WHERE customerID='11' AND orderID IS NULL AND countryID>0 AND addressSlug='default'
[1] SELECT * FROM perch3_shop_addresses WHERE customerID='11' AND orderID IS NULL AND countryID>0 AND addressSlug='shipping'
------------------------------ Calculating cart ------------------------------
[1] SELECT * FROM perch3_shop_cart WHERE cartID=12
[1] SELECT * FROM perch3_shop_cart WHERE cartID=12
[1] SELECT * FROM perch3_shop_tax_locations WHERE locationID=7 AND locationDeleted IS NULL LIMIT 1
[1] SELECT locationID FROM perch3_shop_tax_locations WHERE locationIsHome=1 LIMIT 1
[1] SELECT * FROM perch3_shop_tax_locations WHERE locationID=1 AND locationDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_customers WHERE memberID=11
[1] SELECT * FROM perch3_shop_currencies WHERE currencyID=59 LIMIT 1
[1] SELECT COUNT(productID) AS product_count, SUM(itemQty) AS item_count FROM perch3_shop_cart_items WHERE cartID=12
[31] SELECT DISTINCT settingID, settingValue FROM perch3_settings WHERE userID=0
Tax mode: inc
[1] SELECT * FROM perch3_shop_cart_items WHERE cartID=12 ORDER BY itemID ASC
[1] SELECT * FROM perch3_shop_products WHERE productID=219 AND productDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_products WHERE productID=218 AND productDeleted IS NULL LIMIT 1
[1] SELECT productID, productVariantDesc, stock_level FROM perch3_shop_products WHERE parentID=218 AND productDeleted IS NULL ORDER BY productOrder ASC
[1] SELECT o.* FROM perch3_shop_options o, perch3_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=218 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
[59] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch3_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
[1] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch3_shop_product_option_values pov, perch3_shop_options o, perch3_shop_option_values ov WHERE pov.productID='218' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
[1] SELECT * FROM perch3_shop_products WHERE productID=218 AND productDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_tax_groups WHERE groupID=2 AND groupDeleted IS NULL LIMIT 1
[1] SELECT r.rateValue FROM perch3_shop_tax_group_rates gr, perch3_shop_tax_rates r WHERE gr.rateID=r.rateID AND gr.groupID=2 AND gr.locationID=1
[1] SELECT * FROM perch3_shop_tax_groups WHERE groupID=2 AND groupDeleted IS NULL LIMIT 1
[1] SELECT r.rateValue FROM perch3_shop_tax_group_rates gr, perch3_shop_tax_rates r WHERE gr.rateID=r.rateID AND gr.groupID=2 AND gr.locationID=1
[1] SELECT * FROM perch3_shop_products WHERE productID=218 AND productDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_products WHERE productID=218 AND productDeleted IS NULL LIMIT 1
[1] SELECT productID, productVariantDesc, stock_level FROM perch3_shop_products WHERE parentID=218 AND productDeleted IS NULL ORDER BY productOrder ASC
[1] SELECT o.* FROM perch3_shop_options o, perch3_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=218 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
[59] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch3_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
[1] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch3_shop_product_option_values pov, perch3_shop_options o, perch3_shop_option_values ov WHERE pov.productID='218' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
[1] SELECT * FROM perch3_shop_products WHERE productID=218 AND productDeleted IS NULL LIMIT 1
[1] SELECT productID, productVariantDesc, stock_level FROM perch3_shop_products WHERE parentID=218 AND productDeleted IS NULL ORDER BY productOrder ASC
[1] SELECT o.* FROM perch3_shop_options o, perch3_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=218 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
[59] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch3_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
[1] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch3_shop_product_option_values pov, perch3_shop_options o, perch3_shop_option_values ov WHERE pov.productID='218' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
[1] SELECT * FROM perch3_shop_products WHERE productID=218 AND productDeleted IS NULL LIMIT 1
[1] SELECT productID, productVariantDesc, stock_level FROM perch3_shop_products WHERE parentID=218 AND productDeleted IS NULL ORDER BY productOrder ASC
[1] SELECT o.* FROM perch3_shop_options o, perch3_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=218 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
[59] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch3_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
[1] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch3_shop_product_option_values pov, perch3_shop_options o, perch3_shop_option_values ov WHERE pov.productID='218' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
[1] SELECT * FROM perch3_shop_products WHERE productID=218 AND productDeleted IS NULL LIMIT 1
[1] SELECT productID, productVariantDesc, stock_level FROM perch3_shop_products WHERE parentID=218 AND productDeleted IS NULL ORDER BY productOrder ASC
[1] SELECT o.* FROM perch3_shop_options o, perch3_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=218 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
[59] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch3_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
[1] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch3_shop_product_option_values pov, perch3_shop_options o, perch3_shop_option_values ov WHERE pov.productID='218' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
[1] SELECT * FROM perch3_shop_products WHERE productID=218 AND productDeleted IS NULL LIMIT 1
[1] SELECT productID, productVariantDesc, stock_level FROM perch3_shop_products WHERE parentID=218 AND productDeleted IS NULL ORDER BY productOrder ASC
[1] SELECT o.* FROM perch3_shop_options o, perch3_shop_product_options po WHERE o.optionID=po.optionID AND po.productID=218 AND o.optionDeleted IS NULL ORDER BY optionPrecendence ASC
[59] SELECT valueID AS id, valueTitle AS title, valueSKUCode AS skucode FROM perch3_shop_option_values WHERE optionID=1 AND valueDeleted IS NULL ORDER BY valueOrder ASC
[1] SELECT pov.prodoptID, pov.optionID, pov.valueID, o.optionTitle, ov.* FROM perch3_shop_product_option_values pov, perch3_shop_options o, perch3_shop_option_values ov WHERE pov.productID='218' AND pov.optionID=o.optionID AND pov.valueID=ov.valueID AND o.optionDeleted IS NULL AND ov.valueDeleted IS NULL AND o.optionID='1' ORDER BY o.optionPrecendence ASC, ov.valueOrder ASC
[1] SELECT billingAddress, shippingAddress FROM perch3_shop_cart WHERE billingAddress IS NOT NULL AND cartID=12
[1] SELECT z.* FROM perch3_shop_shipping_zones z LEFT JOIN perch3_shop_shipping_zone_countries c ON z.zoneID=c.zoneID WHERE z.zoneActive=1 AND z.zoneDeleted IS NULL AND (c.countryID=99 OR zoneIsDefault=1) ORDER BY zoneIsDefault ASC LIMIT 1
[3] SELECT * FROM perch3_shop_shippings WHERE shippingDeleted IS NULL ORDER BY shippingOrder ASC
[4] SELECT * FROM perch3_shop_shipping_zones WHERE zoneDeleted IS NULL AND zoneActive=1 AND zoneID IN (2, 3, 4, 5)
[35] SELECT rateID AS id, rateTitle AS title, rateValue AS rate, locationID FROM perch3_shop_tax_rates WHERE rateDeleted IS NULL ORDER BY rateValue DESC
[4] SELECT * FROM perch3_shop_shipping_zones WHERE zoneDeleted IS NULL AND zoneActive=1 AND zoneID IN (2, 3, 4, 5)
[4] SELECT * FROM perch3_shop_shipping_zones WHERE zoneDeleted IS NULL AND zoneActive=1 AND zoneID IN (2, 3, 4, 5)
Qualifying shipping method ‘DHL Express’
Rejecting shipping method ‘DHL Express’ because: cart weight is less than min shipping weight
[1] SELECT * FROM perch3_shop_shippings WHERE shippingID='3' AND shippingDeleted IS NULL LIMIT 1
Using shipping zone: Zone 0
[1] SELECT * FROM perch3_shop_tax_groups WHERE groupID=2 AND groupDeleted IS NULL LIMIT 1
[1] SELECT r.rateValue FROM perch3_shop_tax_group_rates gr, perch3_shop_tax_rates r WHERE gr.rateID=r.rateID AND gr.groupID=2 AND gr.locationID=1
[1] SELECT * FROM perch3_shop_promotions WHERE promoFrom<='2017-10-13 12:09:00' AND promoTo>'2017-10-13 12:09:00' AND promoActive=1 AND promoDeleted IS NULL ORDER BY promoOrder ASC
Promo (Rep John) qualifies!
Percentage discount promo
[1] SELECT * FROM perch3_shop_cart WHERE cartID=12
[1] SELECT * FROM perch3_shop_cart WHERE cartID=12
INSERT INTO perch3_shop_orders(orderStatus,orderGateway,orderTotal,currencyID,orderItemsSubtotal,orderItemsTax,orderItemsTotal,orderShippingSubtotal,orderShippingDiscounts,orderShippingTax,orderShippingTaxDiscounts,orderShippingTotal,orderDiscountsTotal,orderTaxDiscountsTotal,orderSubtotal,orderTaxTotal,orderItemsRefunded,orderTaxRefunded,orderShippingRefunded,orderTotalRefunded,orderTaxID,orderShippingWeight,orderCreated,orderPricing,orderDynamicFields,customerID,shippingID,orderShippingTaxRate,orderBillingAddress,orderShippingAddress) VALUES('created','manual','7519','59','6590','0',6590,'1258','0','0','0','1258','330','0',7518,'0',0,0,0,0,NULL,'140','2017-10-13 12:09:26','standard','{\"TrackingNo\":\"N\\/A\"}','11','3',0,'196','197')
[1] SELECT * FROM perch3_shop_orders WHERE orderID='96' LIMIT 1
No ids to log.
UPDATE perch3_shop_orders SET orderStatus='created', orderGateway='manual', orderTotal='7519', currencyID='59', orderItemsSubtotal='6590', orderItemsTax='0', orderItemsTotal=6590, orderShippingSubtotal='1258', orderShippingDiscounts='0', orderShippingTax='0', orderShippingTaxDiscounts='0', orderShippingTotal='1258', orderDiscountsTotal='330', orderTaxDiscountsTotal='0', orderSubtotal=7518, orderTaxTotal='0', orderItemsRefunded=0, orderTaxRefunded=0, orderShippingRefunded=0, orderTotalRefunded=0, orderTaxID=NULL, orderShippingWeight='140', orderCreated='2017-10-13 12:09:26', orderPricing='standard', orderDynamicFields='{\"TrackingNo\":\"N\\/A\"}', customerID='11', shippingID='3', orderShippingTaxRate=0, orderBillingAddress='196', orderShippingAddress='197', orderUpdated='2017-10-13 14:09:26' WHERE orderID='96'
No ids to log.
[1] SELECT * FROM perch3_shop_addresses WHERE addressID=196
INSERT INTO perch3_shop_addresses(addressTitle,addressSlug,addressFirstName,addressLastName,addressCompany,addressLine1,addressDynamicFields,addressCreated,addressUpdated,addressDeleted,customerID,countryID,regionID,orderID) VALUES('default','default','Blake','Myers','','Na Mlejnku 18','{\"first_name\":\"Blake\",\"last_name\":\"Myers\",\"address_1\":\"Na Mlejnku 18\",\"address_2\":\"\",\"postcode\":\"14700\",\"country\":\"58\",\"company\":\"\",\"city\":\"Praha 4\",\"phone\":\"+420 734 755 511\",\"county\":\"\",\"customer\":\"11\",\"addressID\":\"196\",\"r\":\"\"}','2017-10-12 20:02:03','2017-10-13 06:45:12',NULL,'11','58',NULL,96)
[1] SELECT * FROM perch3_shop_addresses WHERE addressID=197
INSERT INTO perch3_shop_addresses(addressTitle,addressSlug,addressFirstName,addressLastName,addressCompany,addressLine1,addressDynamicFields,addressCreated,addressUpdated,addressDeleted,customerID,countryID,regionID,orderID) VALUES('shipping','shipping','Blake','Myers','','Na Mlejnku 18','{\"first_name\":\"Blake\",\"last_name\":\"Myers\",\"address_1\":\"Na Mlejnku 18\",\"address_2\":\"\",\"postcode\":\"14700\",\"country\":\"58\",\"company\":\"\",\"city\":\"Praha 4\",\"phone\":\"+420 734 755 511\",\"county\":\"\",\"customer\":\"11\",\"addressID\":\"197\",\"r\":\"\"}','2017-10-12 20:02:03','2017-10-13 06:44:02',NULL,'11','58',NULL,96)
UPDATE perch3_shop_orders SET orderBillingAddress='212', orderShippingAddress='213', orderUpdated='2017-10-13 14:09:26' WHERE orderID='96'
No ids to log.
INSERT INTO perch3_shop_order_items(itemType,orderID,productID,itemPrice,itemTax,itemTotal,itemQty,itemTaxRate,itemDiscount,itemTaxDiscount) VALUES('product','96','219','6590','0','6590','1',0,329,5,0)
Invalid query: SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1
INSERT INTO perch3_shop_order_items(itemType,orderID,shippingID,itemPrice,itemTax,itemTotal,itemQty,itemTaxRate,itemDiscount,itemTaxDiscount) VALUES('shipping','96','3','1258','0','1258',1,0,'0','0')
[1] SELECT * FROM perch3_shop_order_items WHERE itemID='187' LIMIT 1
No ids to log.
UPDATE perch3_shop_order_items SET itemType='shipping', orderID='96', shippingID='3', itemPrice='1258', itemTax='0', itemTotal='1258', itemQty=1, itemTaxRate=0, itemDiscount='0', itemTaxDiscount='0' WHERE itemID='187'
No ids to log.
INSERT INTO perch3_shop_order_promotions(orderID,promoID,customerID) VALUES('96','2','11')
UPDATE perch3_shop_cart_data SET orderID='96' WHERE cartID='12'
INSERT INTO perch3_shop_tax_exhibits(orderID,exhibitType,exhibitDetail,exhibitSource,locationID,countryID,exhibitDate) VALUES(96,'IP_ADDRESS','127.0.0.1','Environment',NULL,NULL,'2017-10-13 12:09:26')
[1] SELECT * FROM perch3_shop_tax_exhibits WHERE exhibitID='195' LIMIT 1
No ids to log.
UPDATE perch3_shop_tax_exhibits SET orderID=96, exhibitType='IP_ADDRESS', exhibitDetail='127.0.0.1', exhibitSource='Environment', locationID=NULL, countryID=NULL, exhibitDate='2017-10-13 12:09:26' WHERE exhibitID='195'
No ids to log.
[1] SELECT * FROM perch3_shop_order_statuses WHERE statusKey='created' AND statusActive=1 AND statusDeleted IS NULL
[nil] SELECT * FROM perch3_shop_emails WHERE emailStatus=1 AND emailActive=1
[1] SELECT * FROM perch3_shop_currencies WHERE currencyID='59' LIMIT 1
[1] SELECT * FROM perch3_shop_customers WHERE customerID='11' AND customerDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_addresses WHERE addressID=213 AND addressDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_addresses WHERE addressID=212 AND addressDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
UPDATE perch3_shop_orders SET orderGatewayRef=NULL, orderUpdated='2017-10-13 14:09:26' WHERE orderID='96'
No ids to log.
Payment successful
UPDATE perch3_shop_orders_meta SET metaValue=last_insert_id(metaValue+1) WHERE id='last_invoice_number'
[1] SELECT last_insert_id()
UPDATE perch3_shop_orders SET orderInvoiceNumber='Invoice-40', orderUpdated='2017-10-13 14:09:26' WHERE orderID='96'
No ids to log.
Setting order status to paid
UPDATE perch3_shop_orders SET orderDynamicFields='{\"TrackingNo\":\"N\\/A\",\"status\":\"paid\"}', orderStatus='paid', orderUpdated='2017-10-13 14:09:26' WHERE orderID='96'
No ids to log.
[1] SELECT * FROM perch3_shop_addresses WHERE addressID='212' AND addressDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_tax_locations WHERE locationDeleted IS NULL AND ((countryID=58 AND regionID IS NULL) OR locationIsDefault=1) ORDER BY locationIsDefault ASC LIMIT 1
Looking up missing property address_1
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
Looking up missing property address_2
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
Looking up missing property city
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
Looking up missing property county
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
Looking up missing property postcode
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
INSERT INTO perch3_shop_tax_exhibits(orderID,exhibitType,exhibitDetail,exhibitSource,locationID,countryID,exhibitDate) VALUES(96,'BILL_ADDRESS','Na Mlejnku 18, Praha 4, 14700, Czech Republic','Customer','7','58','2017-10-13 12:09:26')
[1] SELECT * FROM perch3_shop_tax_exhibits WHERE exhibitID='196' LIMIT 1
No ids to log.
UPDATE perch3_shop_tax_exhibits SET orderID=96, exhibitType='BILL_ADDRESS', exhibitDetail='Na Mlejnku 18, Praha 4, 14700, Czech Republic', exhibitSource='Customer', locationID='7', countryID='58', exhibitDate='2017-10-13 12:09:26' WHERE exhibitID='196'
No ids to log.
[1] SELECT * FROM perch3_shop_addresses WHERE addressID='213' AND addressDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_tax_locations WHERE locationDeleted IS NULL AND ((countryID=58 AND regionID IS NULL) OR locationIsDefault=1) ORDER BY locationIsDefault ASC LIMIT 1
Looking up missing property address_1
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
Looking up missing property address_2
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
Looking up missing property city
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
Looking up missing property county
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
Looking up missing property postcode
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
INSERT INTO perch3_shop_tax_exhibits(orderID,exhibitType,exhibitDetail,exhibitSource,locationID,countryID,exhibitDate) VALUES(96,'SHIP_ADDRESS','Na Mlejnku 18, Praha 4, 14700, Czech Republic','Customer','7','58','2017-10-13 12:09:26')
[1] SELECT * FROM perch3_shop_tax_exhibits WHERE exhibitID='197' LIMIT 1
No ids to log.
UPDATE perch3_shop_tax_exhibits SET orderID=96, exhibitType='SHIP_ADDRESS', exhibitDetail='Na Mlejnku 18, Praha 4, 14700, Czech Republic', exhibitSource='Customer', locationID='7', countryID='58', exhibitDate='2017-10-13 12:09:26' WHERE exhibitID='197'
No ids to log.
[1] SELECT * FROM perch3_shop_order_statuses WHERE statusKey='paid' AND statusActive=1 AND statusDeleted IS NULL
[1] SELECT * FROM perch3_shop_emails WHERE emailStatus=3 AND emailActive=1
Sending customer email
[1] SELECT * FROM perch3_shop_customers WHERE customerID='11' AND customerDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_members WHERE memberID='11' LIMIT 1
Using email template: /Users/bmyers/Sites/becalistyle/perch/templates/shop/emails/order_paid.html (html)
[1] SELECT * FROM perch3_shop_order_statuses WHERE statusKey='paid' AND statusDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_addresses WHERE addressID=213 AND addressDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
[1] SELECT * FROM perch3_shop_addresses WHERE addressID=212 AND addressDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_countries WHERE countryID='58' LIMIT 1
[1] SELECT * FROM perch3_shop_order_items WHERE orderID='96' ORDER BY itemID ASC
[1] SELECT * FROM perch3_shop_order_statuses WHERE statusKey='paid' AND statusDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_currencies WHERE currencyID=59 LIMIT 1
[1] SELECT * FROM perch3_shop_shippings WHERE shippingID=3 AND shippingDeleted IS NULL LIMIT 1
[1] SELECT promoID FROM perch3_shop_order_promotions WHERE orderID=96
[1] SELECT * FROM perch3_shop_promotions WHERE promoID=2 LIMIT 1
Looking up missing property sender_name
Looking up missing property sender_email
Building message with Perch template
[1] Using template: /templates/shop/emails/order_paid.html
Using sub-template: /templates/shop/emails/email.html
Using sub-template: /templates/shop/emails/_email_header.html
Using sub-template: /templates/shop/emails/_email_footer.html
[31] SELECT DISTINCT settingID, settingValue FROM perch3_settings WHERE userID=0
Sent email: "Thank you for your order" to stuntdawg@icloud.com
[nil] SELECT p.*, oi.itemQty FROM perch3_shop_order_items oi, perch3_shop_products p WHERE oi.productID = p.productID AND oi.orderID='96'
[1] SELECT * FROM perch3_shop_customers WHERE customerID='11' AND customerDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_currencies WHERE currencyActive=1 AND currencyID=59
UPDATE perch3_shop_orders SET orderExchangeRate=1, orderUpdated='2017-10-13 14:09:27' WHERE orderID='96'
No ids to log.
Redirect held: https://becalistyle.local/checkout/success.php
Blake Myers

Blake Myers 0 points

3 years ago

okay ... I got it figured out ... here's the issue ... I have a currency set to having . separate thousands and , separate the decimal ... the promo is for 5% ... so when it calculates the 5% the number is 329,5 ... well, since perch is not placing that number in single quotes (or at least changing the , back to a .) the insert thinks the , in the 329,5 is marking another column.

so, either perch needs to make sure that it is placing the values in single quotes AND overriding the decimal separator of , to a . ... I could hack perch core to fix it, but I'm afraid that I'd only mess it up more. I'll leave that up to the experts.

if I use a flat amount it works. I tried switching the currency back to a . based separator but for some reason it still tried to calculate it with a ,

for now, I'll instruct my client to only use flat amounts until this is resolved.

ON A SIDE note ... I also have the currency set to 0 decimal places ... so I think perch should also be rounding this number up from 329.5 to 330 to match what should be expected in according to the currency. that would also resolve the issue.
Drew McLellan

Drew McLellan 2638 points
Perch Support

3 years ago

I have a currency set to having . separate thousands and , separate the decimal

Where do you have that set?
Blake Myers

Blake Myers 0 points

3 years ago

Under Shop > Currencies > Hungarian

I find that this page has many settings that mess up Perch ...

Perch doesn’t play well with the Zero Decimal setting either ... sometimes it honors it, sometimes it doesn’t.

It gave me errors with PayPal (via Braintree) too as PayPal won’t accept Hungarian Forints with any decimal place ... rejects the charge
Blake Myers

Blake Myers 0 points

3 years ago

I found the issue ... setlocale(LC_ALL, 'hu_HU');

it was doing this:

    $int = '329.50';
    echo floatval($int);

which would result in 329,50.

after I removed it, it returned 329.50

I discovered it because after I returned the Currency settings BACK to using . instead of , I was still getting that error ... after I removed this everything processed appropriately!

I'm sorry if I stressed you out!