database state field not populating

[Ardoon]

Hi All,

I recently installed and setup version 1.3.9h with everything running great, except I now found that my data base "state" field is empty on all test customers. I can insert a state in the data base field manually and it works fine. I also turned off the state drop down in Admin but, it didn't work when I entered another test customer registration. The state does show when I edit an account. I am totally at a loss for the cause of this problem.

I haven't had time to check the invoices and packing slips to see if they contain the state information.

My site can be seen at This Link

Thanks in advance for any help I can get on this problem.

[DrByte]

There's no "problem". What you describe is by design:
entry_state is only populated if the person's address doesn't have a valid entry_zone. It's one OR the other, not both. Zones are set based on country+state matches, and that linkage is important for tax and shipping reasons, amongst others. There's NO need for entry_state to ever contain a value for any american addresses, nor for any other country for which states/provinces/regions are defined in your store.

[schoolboy]

Where are you located? USA, NZ, AU ?

"Native" zencart comes with US States, and a few other countries... but does not have the UK counties for example, nor many of the states/provinces for other countries.

These need to be inserted into the database - either manually, of via a SQL patch.

As you do not provide a contact us page on your website, I am not sure if your currency is US, NZ, AU, or any of the other 20 countries that use that currency symbol.

(In the UK, running an eCommerce site without providing a physical address and phone number is in contravention of the law,)

[Ardoon]

Thanks for your help DrByte and schoolboy,

I'm located in Wisconsin, USA. I'm still working on some of my pages including the "Contact Us" page and will have it posted when the site goes live. I spotted the issue when trying to setup reviews to show the customers first name, city, and state.

It works great when I enter a state manually into the database.

Is there a way to convert the entry_zone back to state so I can have it show up in reviews? The entry_zone must be converting when customers want to update their account information.

Here's the code I inserted and changed in the /includes/modules/pages/product_reviews_write/header_php file:

// customer entry query added
$customer_city_query = "SELECT entry_city FROM " . TABLE_ADDRESS_BOOK . " WHERE customers_id = :customersID LIMIT 1";
$customer_city_query = $db->bindVars($customer_city_query, ':customersID', $_SESSION['customer_id'], 'integer');
$customer_city = $db->Execute($customer_city_query);

// customer entry query added
$customer_state_query = "SELECT entry_state FROM " . TABLE_ADDRESS_BOOK . " WHERE customers_id = :customersID LIMIT 1";
$customer_state_query = $db->bindVars($customer_state_query, ':customersID', $_SESSION['customer_id'], 'integer');
$customer_state = $db->Execute($customer_state_query);

$sql = $db->bindVars($sql, ':customersName', $customer->fields['customers_firstname'] . ' from ' . $customer_city->fields['entry_city'] . ', ' . $customer_state->fields['entry_state'], 'string');

I will settle for my reviews to show the customers first name and city if this is not possible to do, but I would sure like them to show the state also.

Again Thanks so much for taking the time to respond!
Ardoon

[DrByte]

// customer entry query added
$customer_city_query = "SELECT entry_city FROM " . TABLE_ADDRESS_BOOK . " WHERE customers_id = :customersID LIMIT 1";
$customer_city_query = $db->bindVars($customer_city_query, ':customersID', $_SESSION['customer_id'], 'integer');
$customer_city = $db->Execute($customer_city_query);

// customer entry query added
$customer_state_query = "SELECT entry_state FROM " . TABLE_ADDRESS_BOOK . " WHERE customers_id = :customersID LIMIT 1";
$customer_state_query = $db->bindVars($customer_state_query, ':customersID', $_SESSION['customer_id'], 'integer');
$customer_state = $db->Execute($customer_state_query);

To handle zone lookups, try changing that to something like this:

// customer entry query added
$customer_address_query = "SELECT entry_city, entry_zone_id, entry_country_id, entry_state FROM " . TABLE_ADDRESS_BOOK . " WHERE customers_id = :customersID LIMIT 1";
$customer_address_query = $db->bindVars($customer_address_query, ':customersID', $_SESSION['customer_id'], 'integer');
$result = $db->Execute($customer_address_query);
$customer_city = $result->fields['entry_city'];
$customer_state = zen_get_zone_name($result->fields['entry_country_id'], $result->fields['entry_zone_id'], $result->fields['entry_state']);

and

$sql = $db->bindVars($sql, ':customersName', $customer->fields['customers_firstname'] . ' from ' . $customer_city . ', ' . $customer_state, 'string');

[Ardoon]

Thanks DrByte,

Your code worked great and I can now move on to complete other work on the site. I actually ended up setting the "review by" to first name and state.

I am very greatful to all the postees on the Zen Cart forum for all the long hours spent helping others. A great big, Thank You!, to all.

Below is the code I used for "reviews by", "first name, city, and state" and "first name and state only":



First Name, City, and State:

In: /includes/modules/pages/product_reviews_write/header_php

Add (around line 46): for first name, city and state

// customer entry query added
$customer_address_query = "SELECT entry_city, entry_zone_id, entry_country_id, entry_state FROM " . TABLE_ADDRESS_BOOK . " WHERE customers_id = :customersID LIMIT 1";
$customer_address_query = $db->bindVars($customer_address_query, ':customersID', $_SESSION['customer_id'], 'integer');
$result = $db->Execute($customer_address_query);
$customer_city = $result->fields['entry_city'];
$customer_state = zen_get_zone_name($result->fields['entry_country_id'], $result->fields['entry_zone_id'], $result->fields['entry_state']);

Replace this code (around line 86): for first name, city and state

$sql = $db->bindVars($sql, ':customersName', $customer->fields['customers_firstname'] . ' ' . $customer->fields['customer_lastname'], 'string');

with:

$sql = $db->bindVars($sql, ':customersName', $customer->fields['customers_firstname'] . ' from ' . $customer_city . ', ' . $customer_state, 'string');

In: /includes/templates/YOUR_TEMPLATE/templates/tpl_product_reviews_write_default.php

Replace this code (around line 41): for write review page text

<h3 id="reviewsWriteReviewer" class=""><?php echo SUB_TITLE_FROM, zen_output_string_protected($customer->fields['customers_firstname'] . ' ' . $customer->fields['customers_lastname']); ?></h3>

with:

&nbsp;&nbsp;&nbsp;&nbsp;<h3 id="reviewsWriteReviewer" class="">&nbsp;&nbsp;&nbsp;&nbsp;<?php echo SUB_TITLE_FROM, zen_output_string_protected($customer->fields['customers_firstname'] . ' from ' . $customer_city . ', ' . $customer_state, 'string'); ?></h3>

First Name and State:

In: /includes/modules/pages/product_reviews_write/header_php

Add (around line 46): for first name and state

// customer entry query added
$customer_address_query = "SELECT entry_city, entry_zone_id, entry_country_id, entry_state FROM " . TABLE_ADDRESS_BOOK . " WHERE customers_id = :customersID LIMIT 1";
$customer_address_query = $db->bindVars($customer_address_query, ':customersID', $_SESSION['customer_id'], 'integer');
$result = $db->Execute($customer_address_query);
$customer_city = $result->fields['entry_city'];
$customer_state = zen_get_zone_name($result->fields['entry_country_id'], $result->fields['entry_zone_id'], $result->fields['entry_state']);

Replace this code (around line 86): for first name and state

$sql = $db->bindVars($sql, ':customersName', $customer->fields['customers_firstname'] . ' ' . $customer->fields['customer_lastname'], 'string');

with:

$sql = $db->bindVars($sql, ':customersName', $customer->fields['customers_firstname'] . ' from ' . $customer_state, 'string');

In: /includes/templates/YOUR_TEMPLATE/templates/tpl_product_reviews_write_default.php

Replace this code (around line 41): for write review page text

<h3 id="reviewsWriteReviewer" class=""><?php echo SUB_TITLE_FROM, zen_output_string_protected($customer->fields['customers_firstname'] . ' ' . $customer->fields['customers_lastname']); ?></h3>

with:

&nbsp;&nbsp;&nbsp;&nbsp;<h3 id="reviewsWriteReviewer" class="">&nbsp;&nbsp;&nbsp;&nbsp;<?php echo SUB_TITLE_FROM, zen_output_string_protected($customer->fields['customers_firstname'] . ' from ' . $customer_state, 'string'); ?></h3>

I hope this didn't get too confusing!

Again, the first section is for restricting the "review by" to "First Name, City, and State".

The second section is for restricting the "review by" to "First Name and State".

Hope this helps others that want to do something similar.

All credit goes to DrByte and too many others on the forum to list!

Thank You All,
Ardoon

[devyani]

Where are you located? USA, NZ, AU ?

"Native" zencart comes with US States, and a few other countries... but does not have the UK counties for example, nor many of the states/provinces for other countries.

These need to be inserted into the database - either manually, of via a SQL patch.

As you do not provide a contact us page on your website, I am not sure if your currency is US, NZ, AU, or any of the other 20 countries that use that currency symbol.

(In the UK, running an eCommerce site without providing a physical address and phone number is in contravention of the law,)

Sorry to sneak in this thread, but how do you insert states manually ?