Solved

Can I extend the length of the state field in Virtuemart

Posted on 2010-11-24
18
723 Views
Last Modified: 2012-05-10
Hi,
I would like to extend the length of the state field for virtuemart customers.
Currently it only allows 2 chars so if you enter NSW in your address it shows up as
      
Full Name:       1231 1231
Address:       1232
        123123, NS 2480
Australia


Can someone help me work this one out?
TA
0
Comment
Question by:Amanda Watson
  • 8
  • 7
  • 3
18 Comments
 
LVL 11

Expert Comment

by:Bruce Smith
ID: 34210693
Yes you can! But you have to have some experience with myphpadmin. You need to access your joomla database via myphpadmin (or comparable mysql manager) and find the customer table and change the field from varchar(2) to something like varchar(3). The tables for Virtuemart will be in the Joomla mysql database and will begin with "_vm_" (without the quotes).

Take note that if there are any fields referencing the one you change (aka foreign keys), you will need to modify those as well.

cheers
0
 
LVL 11

Author Comment

by:Amanda Watson
ID: 34210781
I just checked that field and its got varchar(32)

I think its got to do with the form maybe?
0
 
LVL 11

Author Comment

by:Amanda Watson
ID: 34210785
Even though it has varchar(32)  Even in the table it shows as only 2 characters
zw.png
0
 
LVL 11

Expert Comment

by:Bruce Smith
ID: 34210807
The state field is varchar(32)? Then you need to see what the form is doing. When the form is submitted, what is the action? You need to go there to assess what is happening with the inputted text from the state field from the form. Can you post the code of the form and the action file?
0
 
LVL 11

Author Comment

by:Amanda Watson
ID: 34210834
I can if necessary, but why is only 2 chars showing in the table in the database also?
0
 
LVL 11

Expert Comment

by:Bruce Smith
ID: 34210857
Try manually inserting a new test customer from phpmyadmin and inserting more than two characters for the state and see if it all shows up.
0
 
LVL 11

Expert Comment

by:Bruce Smith
ID: 34210866
If that's the case, the inputted text is being modified somewhere in the code when the form is submitted.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34213111
You might want to scan the code for the term "substr" since this is the kind of thing programmers sometimes write when they "normalize" external input.

$state = substr(strtoupper(trim($_POST["state"])), 2);

It would be easy enough to change that 2 to a different number - anything up to 32 would be OK.  Above 32, MySQL will silently truncate.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34213115
Two editors that have code scanners - Textpad and Notepad++.  They both have a "find in files" function.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 11

Author Comment

by:Amanda Watson
ID: 34244738
Hi Ray, I scanned the files for this and couldn't find what you meant

I did find only this

'state' => $db->f("state"),
'state_name' => $state_name,

which was part of an array.....

0
 
LVL 11

Author Comment

by:Amanda Watson
ID: 34244742
Actually here is the whole shipping code

Is this line of interest

$q = "SELECT * FROM #__{vm}_state WHERE country_id=".$country_id." AND state_2_code='".$db->f("state")."'";

maybe I need to change that 2 to 3?


// Begin with Shipping Address

if(!ps_checkout::noShipToNecessary()) {



	$db->query("SELECT * FROM #__{vm}_user_info WHERE user_info_id='".strip_tags($_REQUEST['ship_to_info_id'])."'");

	$db->next_record();



	echo '<tr><td valign="top"><strong>'.$VM_LANG->_('PHPSHOP_ADD_SHIPTO_2') . ":</strong></td>";

	echo '<td>';

	$dbs = new ps_DB();

	$q = "SELECT * FROM #__{vm}_country WHERE country_3_code='".$db->f("country")."'";

	$dbs->query($q);

	$country_id = $dbs->f("country_id");

	$q = "SELECT * FROM #__{vm}_state WHERE country_id=".$country_id." AND state_2_code='".$db->f("state")."'";

	$dbs->query($q);

	$state_name = $dbs->f("state_name");

	echo vmFormatAddress( array('name' => $db->f("first_name")." ".$db->f("last_name"),

        								'address_1' => $db->f("address_1"),

        								'address_2' => $db->f("address_2"),

        								'state' => $db->f("state"),

        								'state_name' => $state_name,

        								'zip' => $db->f("zip"),

        								'city' => $db->f("city"),

        								'country' => $db->f('country')

        							), true );

	

	echo "</td></tr>";

}

Open in new window

0
 
LVL 11

Author Comment

by:Amanda Watson
ID: 34244791
I also just checked another table in the database and found this

jos_vm_state and saw that one field - state was only 2 chars so I change it to 3 however it still shows as 2 chars..

see image
zwstate.jpg
0
 
LVL 11

Accepted Solution

by:
Bruce Smith earned 500 total points
ID: 34244919
Okay, so I looked around and found a possible solution to your dilemma! In reply #5 from "Partic" on the following page is some sql code that you can run directly from your PHPMyAdmin. It worked for several people so hopefully it's what you're looking for: http://forum.virtuemart.net/index.php?topic=45108.msg160268#msg160268

cheers
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34244950
You may want to check all the tables for "state" information.  If it is put into a two character column, MySQL will silently truncate the contents of the data field.  So it follows that if it is copied out of that column, you will only get back two characters.
0
 
LVL 11

Author Comment

by:Amanda Watson
ID: 34245443
I cannot find anything else in the tables relating to the state
0
 
LVL 11

Expert Comment

by:Bruce Smith
ID: 34245463
Try running these SQL lines from the PHPMyAdmin as it has worked for other users with the Australian State abbreviations.

# SQL To fix Australian State Abbreviations in Virtuemart
# Posted 4 Jan 2008 to the VM forum

# Change Table Structure for State Character State Code from 2 to 3 characters
ALTER TABLE `jos_vm_state` CHANGE `state_2_code` `state_2_code` VARCHAR(3) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;

# Update values for Australian State abbreviations
UPDATE `jos_vm_state` SET `state_3_code` = 'ACT',`state_2_code` = 'ACT' WHERE `jos_vm_state`.`state_id` =69 LIMIT 1 ;
UPDATE `jos_vm_state` SET `state_3_code` = 'NSW',`state_2_code` = 'NSW' WHERE `jos_vm_state`.`state_id` =70 LIMIT 1 ;
UPDATE `jos_vm_state` SET `state_3_code` = 'NT',`state_2_code` = 'NT' WHERE `jos_vm_state`.`state_id` =71 LIMIT 1 ;
UPDATE `jos_vm_state` SET `state_3_code` = 'QLD',`state_2_code` = 'QLD' WHERE `jos_vm_state`.`state_id` =72 LIMIT 1 ;
UPDATE `jos_vm_state` SET `state_3_code` = 'SA',`state_2_code` = 'SA' WHERE `jos_vm_state`.`state_id` =73 LIMIT 1 ;
UPDATE `jos_vm_state` SET `state_3_code` = 'TAS',`state_2_code` = 'TAS' WHERE `jos_vm_state`.`state_id` =74 LIMIT 1 ;
UPDATE `jos_vm_state` SET `state_3_code` = 'VIC',`state_2_code` = 'VIC' WHERE `jos_vm_state`.`state_id` =75 LIMIT 1 ;
UPDATE `jos_vm_state` SET `state_3_code` = 'WA',`state_2_code` = 'WA' WHERE `jos_vm_state`.`state_id` =76 LIMIT 1 ;

# Display corrected values
SELECT * FROM `jos_vm_state` WHERE `country_id` =13;

Open in new window

0
 
LVL 11

Author Closing Comment

by:Amanda Watson
ID: 34245478
that forum post dealt with it thanks
0
 
LVL 11

Expert Comment

by:Bruce Smith
ID: 34245493
Nice, glad to hear it  :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

E-commerce is quite a gambling world, and you should never entrust your business to a lucky chance. In order to outrun your competitors in a race to attract as many customers as possible, you need to have a well thought-out strategy under your belt.…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now