Perch Shop - Checkout gives error after moving database

  • Hello,

    I imported data into my database, from another webshop that I have. Since importing this data, when checking out I get the following message in the debug:

    1. 0,1035 0,0003 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','mollie','118.85','44','109.04','9.81',118,85,'0.00','0.00','0.00','0.00','0.00','0.00','0.00',109,04,'9.81',0,0,0,0,NULL,'0.00','2019-10-13 11:54:23','standard','{\"terms_agreed\":\"true\"}','93','4',9,'659','660')
    2. 0,1037 0,0002 Invalid query: SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1

    What does this mean?


  • There are 30 columns listed in the query, however there are 32 values trying to be inserted. So for some reason there are some extra values been added to the order.

    I've not used the Mollie gateway before, so it could be related to that. Would you be able to show us your checkout template and page?

  • I think for some reason some of the values should be strings, for example: 109,04 should be '109,04'. I see two values like this, so then the total values would be 30.

    After some checking, I saw that orderItemsTotal and orderSubtotal are not written as strings in the values. How could I change this so they will be sent to the database as strings?

  • From looking at the Perch Shop order code the two offending fields seem to be

    1. 'orderItemsTotal' => $cart_data['total_items'] + $cart_data['total_items_tax'],
    2. 'orderSubtotal' => $cart_data['total_items_with_shipping'] - $cart_data['total_discounts'],

    So it looks like it could be a type coercion error. Can you check your cart for the fields above?

  • Sort of grasping at straws now, but if you got to the PerchShop_Orders.class.php file and look for the create_from_cart function.

    If you could add the following

    1. PerchUtil::debug(PerchUtil::json_safe_encode($cart_data));
    2. $data = [
    3. 'orderStatus' => 'created',
    4. //... rest of data
    5. 'orderShippingAddress' => $ShippingAddress->id(),
    6. ];
    7. PerchUtil::debug(PerchUtil::json_safe_encode($data));

    This will hopefully output something into the debug that might hint to where things are going wrong

  • I beautified the most important part:

    orderItemsTotal and orderSubtotal do have decimals in place, but are not strings.

  • Here you go:

  • First 10.000 char of debug:

  • If you go to yoursite.local/admin/core/settings/diagnostics/?extended it should have a list of your perch settings and your environment. It may be that you're missing some php extensions. If your site is publicly accessible you'll need to hide your db username/passwords e.t.c.