Jump to content
bad_lemming

[Contribution] - USPS Methods

Recommended Posts

BTW-- It's mySQL 4.0.27 I'm running.

 

 

OK-- Don't want to be someone asking for something for nothing here...

 

$100 to the person that solves this for me (making USPS Methods work with the 5/14/07 updates) today. This is a production site, and right now I can't take orders from international customers... Domestic works, International shows no shipping methods available, most likely due to this SQL issue, but therre could be something else.

 

$100 up for grabs, paypal, check, however you wish. Help me out please.


Jerry a.k.a. 'FoundSoul'

Share this post


Link to post
Share on other sites
OK-- Don't want to be someone asking for something for nothing here...

 

$100 to the person that solves this for me (making USPS Methods work with the 5/14/07 updates) today. This is a production site, and right now I can't take orders from international customers... Domestic works, International shows no shipping methods available, most likely due to this SQL issue, but therre could be something else.

 

$100 up for grabs, paypal, check, however you wish. Help me out please.

 

 

 

Quick update- I may have just nailed this. I used phpmyadmin to make the same change that SQL statement was making, the the second SQL statement ran just fine, and initial tests seem to be working. I'll do some more testing and update here if it worked....


Jerry a.k.a. 'FoundSoul'

Share this post


Link to post
Share on other sites
Quick update- I may have just nailed this. I used phpmyadmin to make the same change that SQL statement was making, the the second SQL statement ran just fine, and initial tests seem to be working. I'll do some more testing and update here if it worked....

 

 

 

For anyone else running into this-- Open phpmyadmin, select the configuration table on the left, check the 'set_function' field on the right side and click the 'change' icon over to the left of it (looks like a pencil). Set the 'TYPE' to 'TEXT', blank out the 'Length/Value' box so that it's empty, and leave the 'Null' and 'Default' settings to 'NULL'. All other settings leave blank. Then click SAVE to make this change.

 

Now go and apply the second SQL query in the documentation-- that one should work fine.

 

Then from the admin, disable the USPS Methods module, and then re-enable it, and the updated USPS International methods should be available....

 

 

I need to do some more testing (it's trial by fire time now)... but my initial testing shows this is working fine....


Jerry a.k.a. 'FoundSoul'

Share this post


Link to post
Share on other sites
For anyone else running into this-- Open phpmyadmin, select the configuration table on the left, check the 'set_function' field on the right side and click the 'change' icon over to the left of it (looks like a pencil). Set the 'TYPE' to 'TEXT', blank out the 'Length/Value' box so that it's empty, and leave the 'Null' and 'Default' settings to 'NULL'. All other settings leave blank. Then click SAVE to make this change.

 

Now go and apply the second SQL query in the documentation-- that one should work fine.

 

Then from the admin, disable the USPS Methods module, and then re-enable it, and the updated USPS International methods should be available....

I need to do some more testing (it's trial by fire time now)... but my initial testing shows this is working fine....

 

 

Thank God someone posted this. The USPS changes have crushed my international customers until I get this fixed.

I'm using PHP 4.4.4. After I follow your instructions for the first SQL change, I tried running the second query. This is the message I get...

 

Warning: Cannot modify header information - headers already sent by (output started at /usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/header_meta_style.inc.php:27) in /usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/common.lib.php on line 1154

 

 

Any suggestions?

Edited by JoeWoodworker

Evil will always triumph over good...

Because good is dumb.

- D.H.

Share this post


Link to post
Share on other sites

Thanks FoundSoul - mucho! Don't know about $100, but, if it was proper to offer it hereabouts, I'd gladly give you a discount at my store!

 

Still have to research the flat-rate box and other peculiarities of the new rules. For the moment, I expect I'll eat extra shipping costs on the handful of higher priced items I have that also might be affected by new dimensional weight stuff. In most cases, I have international customers query me first anyway for shipping quotes for such pieces (it's a peculiarity of the kinds of items I sell that shipping is usually exorbitant by any means for the very oversized and heavy pieces).

 

Anyway, the fix worked fine for me. I hesitate to offer any advice to Joe, but you might want to ask your web-host about phpmyadmin configuration peculiarities, assuming the host configured and provided phpmyadmin and mysql for you.

Share this post


Link to post
Share on other sites

Hello, all. I want to apologize if this has come up before in this string, but I have absolutely no idea what search terms to use to find this. If this has been covered before, I will be appreciative if someone can point me in the right direction.

 

I am using this contribution, but for the moment only for Domestic Express mail. I would love to use this for Priority Flat rate boxes and envelopes, both domestic and international. However, I would like the system to automatically determine which should be offered based on the number of items in a customer's shopping cart.

 

I sell t-shirts on my site. Two t-shirts can easily fit in a single Priority/Express flat-rate envelope (domestic or international). Any more than that, and it's easier to put them into the Priority flat-rate boxes.

 

What I'd like to have happen is for osCommerce (or this contrib) to look at the number of items in a customer's cart and make a decision on which shipping option to offer. If it's more than two items, then it should offer the Priority flat-rate box, and not even show the envelope. If it's only one or two items, then the system should only show the Priority flat-rate envelope.

 

Does this make sense? Does anyone know if there's a way to make this work with this contribution? Am I totally insane? Right now, I'm using a multi-zone flat-rate contribution that I need to put way too much time and thought into in order to make sure I have the right shipping charges set. Any help will be greatly appreciated.


Brian (Schadeboy) Schade

CIO and Webmaster

Twilight Teez, LLC

 

"Camping is nature's way of promoting the hotel industry."

-Dave Barry

Share this post


Link to post
Share on other sites

This is killing me. I cannot get this to work. When a foreign customer tries to check out, this is listed under USPS:

 

An error occured with the USPS shipping calculations.

If you prefer to use USPS as your shipping method, please contact the store owner.

 

I've done everything I've read.

 

I opened phpmyadmin, selected the configuration table (the one entitled configuration) on the left, checked the 'set_function' field on the right side and clicked the 'change' icon over to the left of it. I set the 'TYPE' to 'TEXT', blanked out the 'Length/Value' box so that it's empty, and left the 'Null' and 'Default' settings to 'NULL'. Then I saved. It seemed to go fine.

 

Then I applied the other update my clicking "SQL" at the top and copying it into the "Run SQL query/queries on database" box and clicking "Go."

 

I removed usps from my shipping modules and then reinstalled it. Set the options I wanted. But it still doesn't work.

 

Any help would be very much appreciated.

 

Thanks,

Joe

Share this post


Link to post
Share on other sites
This is killing me. I cannot get this to work. When a foreign customer tries to check out, this is listed under USPS:

 

An error occured with the USPS shipping calculations.

If you prefer to use USPS as your shipping method, please contact the store owner.

 

I've done everything I've read.

 

I opened phpmyadmin, selected the configuration table (the one entitled configuration) on the left, checked the 'set_function' field on the right side and clicked the 'change' icon over to the left of it. I set the 'TYPE' to 'TEXT', blanked out the 'Length/Value' box so that it's empty, and left the 'Null' and 'Default' settings to 'NULL'. Then I saved. It seemed to go fine.

 

Then I applied the other update my clicking "SQL" at the top and copying it into the "Run SQL query/queries on database" box and clicking "Go."

 

I removed usps from my shipping modules and then reinstalled it. Set the options I wanted. But it still doesn't work.

 

Any help would be very much appreciated.

 

Thanks,

Joe

 

Hmmm... other than uninstalling and re-installing the whole thing, step by step, beginning with your last back-up (at least for the configuration table), you might want to check your tables: Go to PHPADMIN, select the database, check all the tables, and run analyze. If any, especially the configuration table, come up with warnings, then repair them.

 

It may just have been a coincidence, but I discovered a lot of problems - apparently new ones - in the period just after I converted according to the above suggested method. I had several tables crash, and I also ran into a max_resources issue that I had to solve.

Share this post


Link to post
Share on other sites
Hmmm... other than uninstalling and re-installing the whole thing, step by step, beginning with your last back-up (at least for the configuration table), you might want to check your tables: Go to PHPADMIN, select the database, check all the tables, and run analyze. If any, especially the configuration table, come up with warnings, then repair them.

 

It may just have been a coincidence, but I discovered a lot of problems - apparently new ones - in the period just after I converted according to the above suggested method. I had several tables crash, and I also ran into a max_resources issue that I had to solve.

 

Thanks for the advice. I just checked the tables, and they were all fine. So I guess I'm going to go the whole uninstalling/reinstalling route.

 

Joe

Share this post


Link to post
Share on other sites
What I'd like to have happen is for osCommerce (or this contrib) to look at the number of items in a customer's cart and make a decision on which shipping option to offer. If it's more than two items, then it should offer the Priority flat-rate box, and not even show the envelope. If it's only one or two items, then the system should only show the Priority flat-rate envelope.

 

Look for how the count_contents() function is used in checkout_shipping, and/or how $total_weight is used in most of the shipping modules. If you know how to write simple if/then/else statements and how to declare variables, you should be able to hack up your own extremely simplified version of the zone, table, or usps modules (I think I'd start with the table), leading to 1 of 3 modes for domestic shipping: envelope, box, or request quote (for very large orders). You don't really need to request quotes from the USPS, as in the USPS Methods module, and you don't need to worry about zones and such, you just need to set up alternatives for either number of items or total weight (which in you case should be same difference).

Edited by kymar

Share this post


Link to post
Share on other sites
Look for how the count_contents() function is used in checkout_shipping, and/or how $total_weight is used in most of the shipping modules. If you know how to write simple if/then/else statements and how to declare variables, you should be able to hack up your own extremely simplified version of the zone, table, or usps modules (I think I'd start with the table), leading to 1 of 3 modes for domestic shipping: envelope, box, or request quote (for very large orders). You don't really need to request quotes from the USPS, as in the USPS Methods module, and you don't need to worry about zones and such, you just need to set up alternatives for either number of items or total weight (which in you case should be same difference).

 

Kymar - Thank you very much for this suggestion. I had figured I would probably need to customize this myself, but I wanted to ask around to see if anyone else had come up with something similar.

 

Our primary shipping method is USPS Priority. We ship both domestic and international. As you probably know by now, the shipping rates have changed, and the ones for international shipping have been changed quite a bit. We have found that the flat-rate Priority Mail International box, which we need to use from time to time, is far too expensive to offer our customers. So we offer Priority Mail International boxes based on weight. But like I said earlier, if a customer orders only one or two shirts, we don't need to use the box, and the flat rate Priority Mail International envelope is very reasonable and works for us.

 

If using the PMI box option, we have to go by weight, and that is where things get tricky. I'd much prefer to simply have USPS calculate that cost for me, which is why my question dealt with this contribution. If I can make it work the way I want, I think I'll really solve a lot of headaches.

 

I will look at your suggestion here. I knew it would be a simply if/then/else statement. I am familiar with general programming terms, and I'm in the process of learning PHP right now, so this will be an excellent object lesson for me. Like I said, though, I first wanted to see if someone had already invented the wheel before I tried.


Brian (Schadeboy) Schade

CIO and Webmaster

Twilight Teez, LLC

 

"Camping is nature's way of promoting the hotel industry."

-Dave Barry

Share this post


Link to post
Share on other sites

I finally got this to work! Hooray!

 

Thanks to everyone who took the time to respond and offer advice!

 

Joe

Share this post


Link to post
Share on other sites
Kymar - Thank you very much for this suggestion. I had figured I would probably need to customize this myself, but I wanted to ask around to see if anyone else had come up with something similar.

 

Our primary shipping method is USPS Priority. We ship both domestic and international. As you probably know by now, the shipping rates have changed, and the ones for international shipping have been changed quite a bit. We have found that the flat-rate Priority Mail International box, which we need to use from time to time, is far too expensive to offer our customers. So we offer Priority Mail International boxes based on weight. But like I said earlier, if a customer orders only one or two shirts, we don't need to use the box, and the flat rate Priority Mail International envelope is very reasonable and works for us.

 

If using the PMI box option, we have to go by weight, and that is where things get tricky. I'd much prefer to simply have USPS calculate that cost for me, which is why my question dealt with this contribution. If I can make it work the way I want, I think I'll really solve a lot of headaches.

 

I will look at your suggestion here. I knew it would be a simply if/then/else statement. I am familiar with general programming terms, and I'm in the process of learning PHP right now, so this will be an excellent object lesson for me. Like I said, though, I first wanted to see if someone had already invented the wheel before I tried.

 

Brian,

You can now use the Priority Mail O-1092 box for PMI by weight. The O-FRB2 box is 11-7/8" x 13-5/8" x 3-3/8", while the O-1092 is 12-1/8" x 13-3/8" x 2-3/4". You can probably fit a few shirts into it.

 

--Glen

Share this post


Link to post
Share on other sites
Brian,

You can now use the Priority Mail O-1092 box for PMI by weight. The O-FRB2 box is 11-7/8" x 13-5/8" x 3-3/8", while the O-1092 is 12-1/8" x 13-3/8" x 2-3/4". You can probably fit a few shirts into it.

 

--Glen

 

Thanks, Glen. I'll check into that. We have found that the new Priority Mail International flat-rate fees are4 exorbitantly high. Far more than we feel is fair to charge our International customers. If we go by weight, then we get better pricing.


Brian (Schadeboy) Schade

CIO and Webmaster

Twilight Teez, LLC

 

"Camping is nature's way of promoting the hotel industry."

-Dave Barry

Share this post


Link to post
Share on other sites

Thank all of you working this out for the benefit of us all.

 

I am using version 2.8d as it was from January. In order to upgrade for the recent USPS changes, will following the instructions last posted by kbucher on May 9 be sufficient? or will I be missing any steps along the way?

 

thank you,

Leslie

Share this post


Link to post
Share on other sites

well, I tried it before waiting to hear back and now I see this error at the top of my shipping modules page:

 

Parse error: syntax error, unexpected T_GLOBAL, expecting ')' in /home/kaligear/public_html/catalog/admin/modules.php(216) : eval()'d code on line 1

 

Also, I am unable to select which international shipping options I can offer. Does anyone have any suggestions?

thank you,

Leslie

Edited by kaligear

Share this post


Link to post
Share on other sites
well, I tried it before waiting to hear back and now I see this error at the top of my shipping modules page:

 

Parse error: syntax error, unexpected T_GLOBAL, expecting ')' in /home/kaligear/public_html/catalog/admin/modules.php(216) : eval()'d code on line 1

 

Also, I am unable to select which international shipping options I can offer. Does anyone have any suggestions?

thank you,

Leslie

 

 

Never mind everyone. I just misunderstood the directions. I thought I only had to run one of the database commands, but you must run them both if you are updating. Funny how you have to read everyone else's posts several times before you realize you have the same problems. At least for someone as slow as me, that's what it takes. :huh:

 

best regards all,

Leslie

Share this post


Link to post
Share on other sites

May I ask an extremely stupid question.

 

I've never worked on mysql. Haven't a clue how to change it, update it, or what ever it is needed to make the shipping update. I've read all the information I can find on the new shipping update. But don't know how to do this mysql thing.

 

Could someone please give exact details on how to do this.

 

I know I have a mysql, phpmyadmin 2.7.0-pl2 section, how do I go about running the queries?

 

Any help is appreciated.

Share this post


Link to post
Share on other sites
May I ask an extremely stupid question.

 

I've never worked on mysql. Haven't a clue how to change it, update it, or what ever it is needed to make the shipping update. I've read all the information I can find on the new shipping update. But don't know how to do this mysql thing.

 

Could someone please give exact details on how to do this.

 

I know I have a mysql, phpmyadmin 2.7.0-pl2 section, how do I go about running the queries?

 

Any help is appreciated.

 

 

:sweating: Finally got up the courage to hit that mysql button in the top left corner, and a box opened, I pasted the info there and hit go... it worked... yay - I was so worried that I was doing something wrong.

 

:* Thank you to all the posters here who have helped us through this latest hurdle, especially to Steve Hoffman for posting how he manually changed the first portion on the mysql update for those of us with a 4.x.x mysql database. (http://forums.oscommerce.com/index.php?s=&showtopic=146950&view=findpost&p=1076114) and to Heather, Glen, kbucher, and grapesmuggler, you all are my current hero's!

 

 

:D My international shipping is now working.

Share this post


Link to post
Share on other sites

I'm sorry if this has been covered before, but my search attempts on this subject are coming up futile. I'm either told that my search criteria is less than three letters or using illegal terms (which the aren't) or that there are no results. But I can't imagine that this hasn't come up i this thread before, so I'm going to ask it.

 

Can this contribution support APO/FPO military addresses? If so, how do I set that up?

 

Thanks!


Brian (Schadeboy) Schade

CIO and Webmaster

Twilight Teez, LLC

 

"Camping is nature's way of promoting the hotel industry."

-Dave Barry

Share this post


Link to post
Share on other sites
I'm sorry if this has been covered before, but my search attempts on this subject are coming up futile. I'm either told that my search criteria is less than three letters or using illegal terms (which the aren't) or that there are no results. But I can't imagine that this hasn't come up i this thread before, so I'm going to ask it.

 

Can this contribution support APO/FPO military addresses? If so, how do I set that up?

 

Thanks!

 

Brian,

It should. Just make sure that you have the five armed forces "state" codes available in your Zones table. APO addresses have ZIP codes like every other US address.

 

--Glen

Share this post


Link to post
Share on other sites

ok heres my problem, i have done everything the instructions say to do and when i try to run the first SQL update ( ALTER TABLE `configuration` CHANGE `set_function` `set_function` VARCHAR( 555 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL; ) it gives me this error:

 

SQL-query :

 

ALTER TABLE `configuration` CHANGE `set_function` `set_function` VARCHAR( 555 ) CHARACTER SETlatin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL

 

MySQL said:

 

Too big column length for column 'set_function' (max = 255). Use BLOB instead

 

 

so i chanfged the VARCHAR( 555 ) to VARCHAR( 255 ) and this is the error i get:

 

SQL-query :

 

ALTER TABLE `configuration` CHANGE `set_function` `set_function` VARCHAR( 255 ) CHARACTER SETlatin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL

 

MySQL said:

 

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'COLLATE latin1_swedish_ci NULL DEFAULT NULL' at line 1

 

even went as far as changing the VARCHAR( 555 ) to TEXT( 555 ) and still get the following error:

 

SQL-query :

 

ALTER TABLE `configuration` CHANGE `set_function` `set_function` TEXT( 555 ) CHARACTER SETlatin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL

 

MySQL said:

 

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '( 555 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEF

 

HELP!!! any help would be GREATLY appreciated.

Share this post


Link to post
Share on other sites
Brian,

 

 

The APO address is entered as follows:

 

CMR 427 Box 1943 is entered as the Street name and number

 

APO is entered as the city

 

AE is entered as the state (and osC recognizes it as Armed Forces Europe)

 

09630 is the ZIP.

 

osC displays the address as

 

Brian Schade

CMR 427 Box 1943

APO, AE 09630

 

I expect that this is formatted correctly. I really have no idea what to do from here. I also use a Zones-based shipping calculator, which appears to be working properly. So I do have something in place, but I'd really like to offer the USPS Methods to my APO/FPO customers for Express shipping if they desire to use that.

 

Thanks again!

Edited by Schadeboy

Brian (Schadeboy) Schade

CIO and Webmaster

Twilight Teez, LLC

 

"Camping is nature's way of promoting the hotel industry."

-Dave Barry

Share this post


Link to post
Share on other sites
ok heres my problem, i have done everything the instructions say to do and when i try to run the first SQL update ( ALTER TABLE `configuration` CHANGE `set_function` `set_function` VARCHAR( 555 ) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL; ) it gives me this error:

 

HELP!!! any help would be GREATLY appreciated.

 

Your version of mySQL is below v5.0.3. That is the first version that expands VARCHAR beyond 255 bytes. Do this instead:

 

ALTER TABLE `configuration` CHANGE `set_function` `set_function` TEXT

 

It has the same effect.

 

--Glen

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×