Can I extend the length of the state field in Virtuemart

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

I just checked that field and its got varchar(32)

I think its got to do with the form maybe?

Even though it has varchar(32)  Even in the table it shows as only 2 characters
zw.png

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?

I can if necessary, but why is only 2 chars showing in the table in the database also?

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.

If that's the case, the inputted text is being modified somewhere in the code when the form is submitted.

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.

Two editors that have code scanners - Textpad and Notepad++.  They both have a "find in files" function.

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.....

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>";
}

Select allOpen in new window

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

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.

I cannot find anything else in the tables relating to the state

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;

Select allOpen in new window

that forum post dealt with it thanks

Nice, glad to hear it  :)