Solved

Can I extend the length of the state field in Virtuemart

Posted on 2010-11-24
18
721 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:patsmitty
Comment Utility
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
Comment Utility
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
Comment Utility
Even though it has varchar(32)  Even in the table it shows as only 2 characters
zw.png
0
 
LVL 11

Expert Comment

by:patsmitty
Comment Utility
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
Comment Utility
I can if necessary, but why is only 2 chars showing in the table in the database also?
0
 
LVL 11

Expert Comment

by:patsmitty
Comment Utility
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:patsmitty
Comment Utility
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
Comment Utility
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
Comment Utility
Two editors that have code scanners - Textpad and Notepad++.  They both have a "find in files" function.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 11

Author Comment

by:Amanda Watson
Comment Utility
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
Comment Utility
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
Comment Utility
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:
patsmitty earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
I cannot find anything else in the tables relating to the state
0
 
LVL 11

Expert Comment

by:patsmitty
Comment Utility
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
Comment Utility
that forum post dealt with it thanks
0
 
LVL 11

Expert Comment

by:patsmitty
Comment Utility
Nice, glad to hear it  :)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

10 Experts available now in Live!

Get 1:1 Help Now