Can I extend the length of the state field in Virtuemart

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
LVL 11
Amanda WatsonWeb DeveloperAsked:
Who is Participating?
 
Bruce SmithSoftware Engineer IICommented:
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
 
Bruce SmithSoftware Engineer IICommented:
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
 
Amanda WatsonWeb DeveloperAuthor Commented:
I just checked that field and its got varchar(32)

I think its got to do with the form maybe?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Amanda WatsonWeb DeveloperAuthor Commented:
Even though it has varchar(32)  Even in the table it shows as only 2 characters
zw.png
0
 
Bruce SmithSoftware Engineer IICommented:
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
 
Amanda WatsonWeb DeveloperAuthor Commented:
I can if necessary, but why is only 2 chars showing in the table in the database also?
0
 
Bruce SmithSoftware Engineer IICommented:
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
 
Bruce SmithSoftware Engineer IICommented:
If that's the case, the inputted text is being modified somewhere in the code when the form is submitted.
0
 
Ray PaseurCommented:
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
 
Ray PaseurCommented:
Two editors that have code scanners - Textpad and Notepad++.  They both have a "find in files" function.
0
 
Amanda WatsonWeb DeveloperAuthor Commented:
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
 
Amanda WatsonWeb DeveloperAuthor Commented:
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
 
Amanda WatsonWeb DeveloperAuthor Commented:
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
 
Ray PaseurCommented:
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
 
Amanda WatsonWeb DeveloperAuthor Commented:
I cannot find anything else in the tables relating to the state
0
 
Bruce SmithSoftware Engineer IICommented:
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
 
Amanda WatsonWeb DeveloperAuthor Commented:
that forum post dealt with it thanks
0
 
Bruce SmithSoftware Engineer IICommented:
Nice, glad to hear it  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.