Perch Shop Customer Registration Not Saving Data

Jason · Feb 19th 2020

Hi, Has anybody had an issue where perch shop doesn't register the initial billing & shipping addressees? I am getting this on the Debug when trying to view them.

Invalid query: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'countryID' at row 1

I have a country field set on the registration form, and I still get the error even when trying the default customer_create.html template. Even weirder this was working the other day and I haven't touched (I Think!) the registration process. The database on perch3_shop_addresses is also now showing a lack of this field whereas before it was filling. Can anybody help me? Thanks!

hus_hmd · Feb 19th 2020

Hi Jason,

Can you share your form template?

Jason · Feb 19th 2020

Code

<perch:form id="register" method="post" app="perch_shop">
<h3 class="white">Your Details</h3>
<perch:label for="first_name">First name</perch:label>
<perch:input type="text" id="first_name" required="true" label="First name" />
<perch:error for="first_name" type="required"><p class="error">Please add your name</p></perch:error>
<perch:label for="last_name">Last name</perch:label>
<perch:input type="text" id="last_name" required="true" label="Last name" />
<perch:error for="last_name" type="required"><p class="error">Please add your name</p></perch:error>
<perch:label for="email">Email</perch:label>
<perch:input type="email" id="email" required="true" placeholder="you@company.com" helper="PerchMembers_Members::check_email" />
<perch:error for="email" type="required"><p class="error">Please add your email address</p></perch:error>
<perch:error for="email" type="helper"><p class="error">That email address is already in use</p></perch:error>
<perch:label for="telephone_number">Telephone Number</perch:label>
<perch:input type="text" id="telephone_number" required="true" label="Telephone Number" />
<perch:error for="telephone_number" type="required"><p class="error">Please add your contact number</p></perch:error>
<perch:label for="company_name">Company Name</perch:label>
<perch:input type="text" id="company_name" required="true" label="Company Name" />
<perch:error for="company_name" type="required"><p class="error">Please add your Company Name</p></perch:error>
<perch:label for="password">Create a password</perch:label>
<perch:input type="password" id="password" required="true" match-with="password2" />
<perch:error for="password" type="required"><p class="error">Please add a password</p></perch:error>
<perch:error for="password" type="match"><p class="error"></p>Passwords do not match</p></perch:error>
<perch:label for="password2">Type your password again</perch:label>
<perch:input type="password" id="password2" required="true" />
<perch:error for="password2" type="required"><p class="error">Please repeat your password</p></perch:error>
<perch:input type="hidden" id="company" label="Company"/>
<perch:input type="hidden" id="address_1" label="Address 1" />
<perch:input type="hidden" id="address_2" label="Address 2" />
<perch:input type="hidden" id="postcode" label="Postal code" />
<perch:input type="hidden" id="city" label="City" />
<perch:input type="hidden" id="county" label="County" />
<perch:input type="hidden" value="236" id="country" label="Country" />
<h3 class="white">Shipping Information</h3>
<p>Please enter the name & address you would like your orders sent to. You can also edit this later.</p>
<perch:label for="shipping_first_name">First name</perch:label>
<perch:input type="text" id="shipping_first_name" required label="First name" />
<perch:label for="shipping_last_name">Last name</perch:label>
<perch:input type="text" id="shipping_last_name" required label="Last name" />
<perch:label for="shipping_company">Company</perch:label>
<perch:input type="text" id="shipping_company" label="Company" />
<perch:label for="shipping_address_1">Address 1</perch:label>
<perch:input type="text" id="shipping_address_1" required label="Address 1" />
<perch:label for="shipping_address_2">Address 2</perch:label>
<perch:input type="text" id="shipping_address_2" label="Address 2" />
<perch:label for="shipping_city">Town / City</perch:label>
<perch:input type="text" id="shipping_city" label="Town / City" />
<perch:label for="shipping_county">State or County</perch:label>
<perch:input type="text" id="shipping_county" label="County" />
<perch:label for="shipping_postcode">Postal code</perch:label>
<perch:input type="text" id="shipping_postcode" label="Postal code" required />
<perch:label for="shipping_country">Country</perch:label>
<perch:input type="select" options="<perch:shop id="country_list" />" value="236" id="shipping_country" label="Country" />
<perch:input type="submit" value="Register" class="button white" />
<perch:success>
<h3 class="white">Thank You for Registering!</h3>
</perch:success>
</perch:form>

Display More

I've also tried the customer_create.html default template too and hit the same issues

hus_hmd · Feb 19th 2020

If add the below and submit the form, do you see the country and shipping country fields in the posted data?

PHP

// print to the debug message
PerchUtil::debug($_POST);
// or print to the page directly
echo '<pre>' . print_r($_POST, 1) . '</pre>';

Jason · Feb 19th 2020

Yes, this is what I get:

Code

Array
(
[first_name] => Jason
[last_name] => Surname
[email] => hello17@emailaddress.com
[telephone_number] => 1234567890
[company_name] => CompanyCo
[password] => hello17
[password2] => hello17
[company] =>
[address_1] =>
[address_2] =>
[postcode] =>
[city] =>
[county] =>
[country] => 236
[shipping_first_name] => Jason
[shipping_last_name] => Surname
[shipping_company] => CompanyCo
[shipping_address_1] => Address Street 1
[shipping_address_2] => Address Street 1
[shipping_city] => Townsvile
[shipping_county] => Norfolk
[shipping_postcode] => GH7822h
[shipping_country] => 236
[cms-form] => cmVnaXN0ZXI6cGVyY2hfc2hvcDovdGVtcGxhdGVzL3Nob3AvY2hlY2tvdXQvY3VzdG9tZXJfY3JlYXRlLmh0bWw6MTU4MjExNzcwMQ==

Display More

hus_hmd · Feb 19th 2020

I think you may need to set a value for address_1

Jason · Feb 19th 2020

Sorry, still no luck! Still doesn't pull through to the CMS

Code

Array
(
[first_name] => Jason
[last_name] => Surname
[email] => hello18@emailaddress.com
[telephone_number] => 5678
[company_name] => CoName
[password] => hello18
[password2] => hello18
[company] => test1
[address_1] => test2
[address_2] => test3
[postcode] => test4
[city] => test5
[county] => test6
[country] => 236
[shipping_first_name] => Jason
[shipping_last_name] => Surname
[shipping_company] => testCo
[shipping_address_1] => Address Line 1
[shipping_address_2] => Address Line 2
[shipping_city] => Town Name
[shipping_county] => Norfolk
[shipping_postcode] => KSIO322
[shipping_country] => 236
[cms-form] => cmVnaXN0ZXI6cGVyY2hfc2hvcDovdGVtcGxhdGVzL3Nob3AvY2hlY2tvdXQvY3VzdG9tZXJfY3JlYXRlLmh0bWw6MTU4MjExODg5MA==
)

Display More

This is the debug on the User's Account Page Details

Code

Debug Message - Perch 3.1.5
[1] SELECT * FROM perch3_members_sessions WHERE sessionID='0cb654960be23b775cf8e8665408a03ff26f7e45' AND sessionHttpFootprint='7c33a638afece83b813674b173793a4185573291' AND sessionExpires>'2020-02-19 13:29:24' LIMIT 1
User is logged in
[1] SELECT * FROM perch3_pages WHERE pagePath='/account/index.php' LIMIT 1
[1] SELECT * FROM perch3_shop_cart WHERE cartID=46
[1] SELECT * FROM perch3_shop_cart WHERE cartID=46
Using template: /templates/pages/attributes/default.html
Using sub-template: /templates/pages/attributes/seo.html
[1] SELECT * FROM perch3_shop_customers WHERE memberID=18
[1] SELECT * FROM perch3_shop_customers WHERE memberID='18' AND customerDeleted IS NULL LIMIT 1
[1] SELECT * FROM perch3_shop_customers WHERE customerEmail='hello18@emailaddress.com' AND customerDeleted IS NULL LIMIT 1
INSERT INTO perch3_shop_customers(customerDynamicFields,memberID,customerCreated) VALUES('{\"first_name\":\"Jason\",\"last_name\":\"Surname\",\"email\":\"hello18@emailaddress.com\"}','18','2020-02-19 13:29:24')
[1] SELECT * FROM perch3_shop_customers WHERE customerID='15' LIMIT 1
No ids to log.
UPDATE perch3_shop_customers SET customerDynamicFields='{\"first_name\":\"Jason\",\"last_name\":\"Surname\",\"email\":\"hello18@emailaddress.com\"}', memberID='18', customerCreated='2020-02-19 13:29:24', customerFirstName='Jason', customerLastName='Surname', customerEmail='hello18@emailaddress.com', customerUpdated='2020-02-19 13:29:24' WHERE customerID='15'
No ids to log.
[9] SELECT statusKey FROM perch3_shop_order_statuses WHERE statusDeleted IS NULL ORDER BY statusIndex ASC
[nil] SELECT main.* FROM perch3_shop_orders main WHERE 1=1 AND customerID='15' AND orderStatus IN ('paid', 'processing', 'cancelled', 'dispatched', 'returned', 'partial_refund', 'refunded') ORDER BY orderCreated ASC
[30] SELECT DISTINCT settingID, settingValue FROM perch3_settings WHERE userID=0
Using template: /templates/shop/orders/list.html
[1] SELECT * FROM perch3_shop_customers WHERE memberID=18
[nil] SELECT main.* FROM perch3_shop_addresses main WHERE 1=1 AND (customerID=15) AND addressDeleted IS NULL AND orderID IS NULL ORDER BY addressTitle ASC
Using template: /templates/shop/addresses/shipping.html
[250] SELECT country, countryID FROM perch3_shop_countries WHERE countryActive=1 ORDER BY country ASC
[1] SELECT * FROM perch3_shop_customers WHERE memberID=18
[1] SELECT * FROM perch3_shop_addresses WHERE customerID='15' AND orderID IS NULL AND countryID>0 AND addressSlug='default'
no address
[1] SELECT * FROM perch3_shop_addresses WHERE customerID='15' AND orderID IS NULL AND countryID>0 AND addressSlug='default'
INSERT INTO perch3_shop_addresses(addressDynamicFields,addressTitle,addressSlug,addressCreated) VALUES('{\"first_name\":\"Jason\",\"last_name\":\"Surname\",\"address_1\":false,\"address_2\":false,\"postcode\":false,\"country\":false,\"company\":false,\"city\":false,\"customer\":\"15\",\"phone\":false,\"county\":false,\"instructions\":false}','default','default','2020-02-19 13:29:24')
[1] SELECT * FROM perch3_shop_addresses WHERE addressID='177' LIMIT 1
No ids to log.
UPDATE perch3_shop_addresses SET addressDynamicFields='{\"first_name\":\"Jason\",\"last_name\":\"Surname\",\"address_1\":false,\"address_2\":false,\"postcode\":false,\"country\":false,\"company\":false,\"city\":false,\"customer\":\"15\",\"phone\":false,\"county\":false,\"instructions\":false}', addressTitle='default', addressSlug='default', addressCreated='2020-02-19 13:29:24', addressFirstName='Jason', addressLastName='Surname', addressCompany='', addressLine1='', customerID='15', countryID='', addressUpdated='2020-02-19 13:29:24' WHERE addressID='177'
Invalid query: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'countryID' at row 1
No ids to log.
[1] SELECT * FROM perch3_shop_countries WHERE countryID='' LIMIT 1
No country set for address (name)
[1] SELECT * FROM perch3_shop_addresses WHERE customerID='15' AND orderID IS NULL AND countryID>0 AND addressSlug='shipping'
no address
[1] SELECT * FROM perch3_shop_addresses WHERE customerID='15' AND orderID IS NULL AND countryID>0 AND addressSlug='default'
INSERT INTO perch3_shop_addresses(addressDynamicFields,addressTitle,addressSlug,addressCreated) VALUES('{\"first_name\":\"Jason\",\"last_name\":\"Surname\",\"address_1\":false,\"address_2\":false,\"postcode\":false,\"country\":false,\"company\":false,\"city\":false,\"customer\":\"15\",\"phone\":false,\"county\":false,\"instructions\":false}','shipping','shipping','2020-02-19 13:29:24')
[1] SELECT * FROM perch3_shop_addresses WHERE addressID='178' LIMIT 1
No ids to log.
UPDATE perch3_shop_addresses SET addressDynamicFields='{\"first_name\":\"Jason\",\"last_name\":\"Surname\",\"address_1\":false,\"address_2\":false,\"postcode\":false,\"country\":false,\"company\":false,\"city\":false,\"customer\":\"15\",\"phone\":false,\"county\":false,\"instructions\":false}', addressTitle='shipping', addressSlug='shipping', addressCreated='2020-02-19 13:29:24', addressFirstName='Jason', addressLastName='Surname', addressCompany='', addressLine1='', customerID='15', countryID='', addressUpdated='2020-02-19 13:29:24' WHERE addressID='178'
Invalid query: SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'countryID' at row 1
No ids to log.
[1] SELECT * FROM perch3_shop_countries WHERE countryID='' LIMIT 1
No country set for address (name)
Using template: /templates/shop/customers/customer_details.html
[3] SELECT regionKey, regionHTML FROM perch3_content_regions WHERE regionPage='/account/index.php' OR regionPage='*' ORDER BY regionPage DESC

Display More

This is the page

PHP

<?php
include('../perch/runtime.php');
perch_layout('header',[
"strict" => true,
]); ?>
<section id="account">
<?php perch_shop_orders() ?>
<div class="details">
<div class="full">
<h1>Your Information</h1>
</div>
<div class="full">
<div class="col">
<h2 class="blue" id="shipping">Shipping Address</h2>
<?php perch_shop_customer_addresses([
'template' => 'addresses/shipping.html',
]); ?>
</div>
<div class="col" id="details">
<h2 class="blue">Your Details</h2>
<?php perch_shop_customer_edit_form([
'template' => 'customers/customer_details.html',
]); ?>
</div>
<div class="col">
<h2 class="blue">Password</h2>
<p>•••••••••••</p>
<a class="button" href="/account/update-password">Change Password</a>
</div>
</div>
</div>
<div class="full">
<div class="proceed">
<a href="/account/logout" class="button">Log Out</a>
</div>
</div>
</section>
<?php perch_layout('footer');
?>,

Display More

hus_hmd · Feb 19th 2020

It does look like it is not adding the country ID to the address when creating it. I'm not sure why this would happen. Can you post the debug message upon a customer creation (and hence the initial addresses creations)? Perhaps this will help us figure out why!

Jason · Feb 19th 2020

Thanks hus_hmd , yes it's got me stumped...

Here is the debug code from the registration page after the form has been submitted.

Code

Debug Message - Perch 3.1.5
[1] SELECT * FROM perch3_pages WHERE pagePath='/register.php' LIMIT 1
[1] SELECT * FROM perch3_shop_cart WHERE cartID=46
[1] SELECT * FROM perch3_shop_cart WHERE cartID=46
[30] SELECT DISTINCT settingID, settingValue FROM perch3_settings WHERE userID=0
[0] SELECT COUNT(*) FROM perch3_members WHERE memberPassword IS NOT NULL AND memberEmail='email@email.com'
[0] SELECT COUNT(*) FROM perch3_members WHERE memberPassword IS NOT NULL AND memberEmail='email@email.com'
[1] SELECT * FROM perch3_members_forms WHERE formKey='register' LIMIT 1
INSERT INTO perch3_members(memberAuthType,memberEmail,memberPassword,memberStatus,memberCreated,memberProperties) VALUES('native','email@email.com','$P$BGtDCqPrSEh/bZ70t.luwtBBw6rHBA0','pending','2020-02-19 14:22:45','{\"first_name\":\"Name\",\"last_name\":\"Surnam\"}')
[1] SELECT * FROM perch3_members WHERE memberID='19' LIMIT 1
No ids to log.
Using email template: /srv/users/strident/apps/strident-motability/public/strident/addons/apps/perch_members/templates/members/emails/new_member_notification.html (html)
Building message with Perch template
[1] Using template: /addons/apps/perch_members/templates/members/emails/new_member_notification.html
Sent email: "A new member is waiting approval" to jasonfuller@me.com
UPDATE perch3_members SET memberAuthID='19' WHERE memberID='19'
No ids to log.
Using template: /templates/pages/attributes/default.html
Using sub-template: /templates/pages/attributes/seo.html
[250] SELECT country, countryID FROM perch3_shop_countries WHERE countryActive=1 ORDER BY country ASC
Using template: /templates/shop/checkout/customer_create.html
[3] SELECT regionKey, regionHTML FROM perch3_content_regions WHERE regionPage='/register.php' OR regionPage='*' ORDER BY regionPage DESC

Display More

hus_hmd · Feb 19th 2020

Do you by any chance have the "New members require approval" option enabled? You can find this in the control panel (Members > Forms).

Jason · Feb 19th 2020

Hi Hussain, yes I do & yes that's solved it. You're a star.

I'll guess I'll have to change my flow?

hus_hmd · Feb 19th 2020

Ah finally! A non-approved member can't be signed in as far as I'm aware and it looks like Perch Shop expects a signed-in member when creating a customer.

If you need to allow them to sign in, but restrict their access to certain things until they are approved you can add a tag like "pending" on sign up and approve them by removing the tag. Unfortunately you cannot send moderator notification for sign ups this way. See: Members: new member event