Perch Shop Emails Issue

James Harrison · Feb 28th 2019

Hello there,

I am trying to create an email that is sent out when an order is placed within Perch Shop under Shop -> Emails. However, whenever I try to submit the form on that page, I get an error message that says: "Sorry, that update was not successful". Is there something that needs to be done to make this section work? A screenshot is attached for reference as well as my Diagnostics Summary.

Thanks in advance!

PHP

Perch: 3.1.3, PHP: 7.1.15-0ubuntu0.17.10.1, MySQL: mysqlnd 5.0.12-dev - 20150407 - $Id: 38fea24f2847fa7519001be390c98ae0acafe387 $, with PDO
Server OS: Linux, fpm-fcgi
Installed apps: content (3.1.3), assets (3.1.3), categories (3.1.3), perch_forms (1.12), perch_shop_orders (1.2.6), perch_shop_products (1.2.6), perch_shop (1.2.6), perch_members (1.6.4)
App runtimes: <?php $apps_list = array( 'content', 'categories', 'perch_members', 'perch_forms', 'perch_shop', );
PERCH_LOGINPATH: /admin
PERCH_PATH: /var/www/vhosts/live/lyndenhouse.net/public/admin
PERCH_CORE: /var/www/vhosts/live/lyndenhouse.net/public/admin/core
PERCH_RESFILEPATH: /var/www/vhosts/live/lyndenhouse.net/public/admin/resources
Image manipulation: GD Imagick
PHP limits: Max upload 300M, Max POST 300M, Memory: 128M, Total max file upload: 128M
F1: 3b606135b33e6a102526838f4152a807
Resource folder writeable: Yes
HTTP_HOST: www.lyndenhouse.net
DOCUMENT_ROOT: /var/www/vhosts/live/lyndenhouse.net/public
REQUEST_URI: /admin/core/settings/diagnostics/
SCRIPT_NAME: /admin/core/settings/diagnostics/index.php

Display More

drewm · Mar 1st 2019

If you turn debug on and submit the form again, what does it output?

James Harrison · Mar 1st 2019

Thanks Drew, I did that and found there is a missing column in the db on line 9:

Code

Debug Message - Perch 3.1.3
[1] SELECT u.*, r.* FROM perch2_users u, perch2_user_roles r WHERE u.roleID=r.roleID AND u.userEnabled=1 AND u.userID=1 AND u.userHash='26b7b661a343fe53994474131a7c20b4' LIMIT 1
UPDATE perch2_users SET userHash='dc564e669713695fd5a33c7443124862' WHERE userID='1'
[39] SELECT settingID, settingValue, userID FROM perch2_settings WHERE userID=1 OR userID=0 ORDER BY userID ASC
[11] SELECT itemValue FROM perch2_menu_items WHERE itemType='app'
Using template: /addons/apps/perch_shop/templates/shop/emails/email.html
[1] SELECT * FROM perch2_shop_order_statuses WHERE statusID='1' AND statusDeleted IS NULL LIMIT 1
INSERT INTO perch2_shop_emails(emailStatus,emailTemplate,emailFor,emailRecipient,emailDynamicFields,emailCreated) VALUES('1','order_dispatched.html','customer',NULL,'{\"name\":\"Test\",\"subject\":\"test\",\"sender_name\":\"test\",\"sender_email\":\"test\",\"enabled\":\"0\"}','2019-03-01 17:41:59')
Invalid query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'emailStatus' in 'field list'
[1] SELECT mi.*, p.privKey FROM perch2_menu_items mi LEFT JOIN perch2_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=0 ORDER BY itemOrder ASC LIMIT 0, 1
[6] SELECT mi.*, p.privKey FROM perch2_menu_items mi LEFT JOIN perch2_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=1 ORDER BY mi.itemOrder ASC
[1] SELECT itemTitle FROM perch2_menu_items WHERE itemType='app' AND itemValue='perch_shop' LIMIT 1
adding: Shipping
adding: Promotions
adding: Tax
adding: Emails
adding: Currencies
adding: Countries
adding: Statuses
[1] SELECT mi.*, p.privKey FROM perch2_menu_items mi LEFT JOIN perch2_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=0 ORDER BY itemOrder ASC LIMIT 1, 10
[2] SELECT mi.*, p.privKey FROM perch2_menu_items mi LEFT JOIN perch2_user_privileges p ON mi.privID=p.privID WHERE mi.itemActive=1 AND mi.parentID=2 ORDER BY mi.itemOrder ASC
File: /addons/apps/perch_shop/modes/email.edit.post.php
[9] SELECT * FROM perch2_shop_order_statuses WHERE 1=1 AND statusDeleted IS NULL ORDER BY statusIndex ASC
Mode: email.edit
Queries: 12
Memory: 2.3301

Display More

This project started originally with a beta version of Shop, could there have been a sql update that wasn't made along the way, with all the updates to the more final versions of Shop?

Any idea what I need to do to resolve the error?

Thanks!

drewm · Mar 5th 2019

Here's how the table should look:

SQL

CREATE TABLE IF NOT EXISTS `perch3_shop_emails` (
`emailID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`emailTitle` varchar(255) NOT NULL DEFAULT '',
`emailSlug` varchar(255) NOT NULL DEFAULT '',
`emailFor` enum('customer','customer_bcc','admin') NOT NULL DEFAULT 'customer',
`emailRecipient` varchar(255) DEFAULT NULL,
`emailStatus` varchar(255) NOT NULL DEFAULT '',
`emailTemplate` varchar(255) NOT NULL DEFAULT 'order_paid.html',
`emailActive` varchar(255) NOT NULL DEFAULT '1',
`emailDynamicFields` mediumtext,
`emailCreated` datetime NOT NULL DEFAULT '2016-01-01 00:00:00',
`emailUpdated` datetime NOT NULL DEFAULT '2016-01-01 00:00:00',
PRIMARY KEY (`emailID`)
) CHARSET=utf8;

Display More

James Harrison · Mar 18th 2019

Thank you for that Drew! Running that bit of SQL restored email functionality. I appreciate it!

drewm Mar 1st 2019