Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Zip based taxes Myql Query help?


bastille

Recommended Posts

Hi all

I am trying to alter a modifcation so that it returns a tax rate based on zip code.

(new york state has different rates based on counties)

I have created a table, ziptax, and in it have a column with the zip and the corresponding tax rate.

 

I want to modify this contribution that returns a hard -coded rate to return a rate based on matching the customer's zip with the table above.. thus, I modified the code

 

$customer_zip_query = tep_db_query("select entry_postcode, entry_zone_id from " . TABLE_ADDRESS_BOOK . " where customers_id =  '" . $customer_id . "'");

 

$address_query = tep_db_fetch_array($customer_zip_query);

$cust_zip_code = $address_query['entry_postcode'];

if ($cust_zip_code == '35542' || $cust_zip_code == '36551' || $cust_zip_code == '36555' || $cust_zip_code == '36559' || $cust_zip_code == '36561' || $cust_zip_code == '36562' || $cust_zip_code == '36564' || $cust_zip_code == '36567' || $cust_zip_code == '36577' || $cust_zip_code == '36580' || $cust_zip_code == '36526' || $cust_zip_code == '36532' || $cust_zip_code == '36533' || $cust_zip_code == '36535' || $cust_zip_code == '36536' || $cust_zip_code == '36547' || $cust_zip_code == '36549') {

  $zip_in_county = true;

        } else {

  $zip_in_county = false;

  }

 

if (($zip_in_county == 'true') && ($address_query['entry_zone_id'] == '1')) {

    return 6.0000;

}

elseif (($zip_in_county == 'false') && ($address_query['entry_zone_id'] == '1')) {

    return 4.0000;

}

 

to this::

 

 

$taxquery = tep_db_query("select zip_tax_rate from  " . TABLE_ZIPTAX . " where zip_code =  '" . $address_query['entry_postcode'] . "'");

$tax_query = tep_db_fetch_array($taxquery);

$taxrate=$tax_query['zip_tax_rate'];

if (($zip_in_county == 'true') && ($address_query['entry_zone_id'] == '43')) {

 

    //return 10.0000;

  return $taxrate;

 

}

 

but its coming up empty :( Any ideas?

Link to comment
Share on other sites

Ok i got this to work

 

But its getting the zip informatoin from the customer's default address rather than the shipping address (which is what i need to calcualte tax) does anyone have any idea which field i would use in a query for that??

Link to comment
Share on other sites

......so when i change the user's default address to NOT be ny, i get this message:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in

/home/www/***********/catalog/includes/functions/database.php on line 10

 

 

on this page:

/catalog/checkout_confirmation.php?

 

 

....any ideas??

Link to comment
Share on other sites

IMHO you could things more simple. Now you get errors if the zip code is not in the table: $taxquery is empty (you could code for that too of course) so $tax_query = tep_db_fetch_array($taxquery) is not valid.

 

Can't you extend the code of the original contribution not to something like the following?

 

$address_query = tep_db_fetch_array($customer_zip_query);

$ny_county_1 = array('35542', '36551', '36555', '36559');
$ny_county_2 = array('36561', '36562', '36564', '36567');
$ny_county_3 = array('36577', '36580', '36526', '36532');

$cust_zip_code = $address_query['entry_postcode'];

if (in_array( $cust_zip_code, $ny_county_1) && ($address_query['entry_zone_id'] == '43')) { 
? ? return 10.0000; 
} elseif (in_array( $cust_zip_code, $ny_county_2) && ($address_query['entry_zone_id'] == '43')) { 
? ? return 10.5000; 
} elseif (in_array( $cust_zip_code, $ny_county_3) && ($address_query['entry_zone_id'] == '43')) {
? ? return 8.5200;
} else {
? ? return 0;
}

Link to comment
Share on other sites

IMHO you could things more simple. Now you get errors if the zip code is not in the table: $taxquery is empty (you could code for that too of course) so $tax_query = tep_db_fetch_array($taxquery) is not valid.

 

Can't you extend the code of the original contribution not to something like the following?

 

$address_query = tep_db_fetch_array($customer_zip_query);

$ny_county_1 = array('35542', '36551', '36555', '36559');
$ny_county_2 = array('36561', '36562', '36564', '36567');
$ny_county_3 = array('36577', '36580', '36526', '36532');

$cust_zip_code = $address_query['entry_postcode'];

if (in_array( $cust_zip_code, $ny_county_1) && ($address_query['entry_zone_id'] == '43')) { 
   return 10.0000; 
} elseif (in_array( $cust_zip_code, $ny_county_2) && ($address_query['entry_zone_id'] == '43')) { 
   return 10.5000; 
} elseif (in_array( $cust_zip_code, $ny_county_3) && ($address_query['entry_zone_id'] == '43')) {
   return 8.5200;
} else {
   return 0;
}

 

hi, thanks for responding. yeah i do get that error if the zip isn't there, or if the state is not ny.

 

my thinking runs along these lines :

its easier to manage the zips and tax rates in a mysql table as opposed to hard coded since new york is forvere changing and raising taxes (several counties are set to increase in less than a month's time.

 

it looks like your solution would definately work however - but i think the original contrib that i am modifiying goes off the customer's default zip (post) code as opposed to the shipping - which is what should be used to calc. taxes. but i can't seem to pin down what field that is...

Link to comment
Share on other sites

its easier to manage the zips and tax rates in a mysql table as opposed to hard coded since new york is forvere changing and raising taxes (several counties are set to increase in less than a month's time.
Exactly why you don't want a mysql table where you have to edit each and every taxrate for each and every zipcode... In the "hard coded variant" you could even hard code dates and then if the date changes to the date on which the new tax rate is introduced you can relax because 1 minute after 12 the new tax rate is applied.
but i can't seem to pin down what field that is...
In checkout_confirmation.php the class $order is called, and from the code it looks $order->delivery['delivery_postcode'] might be the one you are looking for. Give it a try I would say.
Link to comment
Share on other sites

I tried switching it to that query method an still getting that error :'(

 

 

$tax_address_query = tep_db_query("select ab.entry_country_id, ab.entry_zone_id, ab.entry_postcode from " . TABLE_ADDRESS_BOOK . " ab left join " . TABLE_ZONES . " z on (ab.entry_zone_id = z.zone_id) where ab.customers_id = '" . (int)$customer_id . "' and ab.address_book_id = '" . (int)($this->content_type == 'virtual' ? $billto :$sendto) . "'");

$tax_address = tep_db_fetch_array($tax_address_query);

$ny_county_1 = array('10011', '36551', '36555', '36559');
$ny_county_2 = array('36561', '36562', '36564', '36567');
$ny_county_3 = array('36577', '36580', '36526', '36532');

$cust_zip_code = $tax_address['entry_postcode'];

if (in_array( $cust_zip_code, $ny_county_1) && ($tax_address['entry_zone_id'] == '43')) {
   return 10.0000;
} elseif (in_array( $cust_zip_code, $ny_county_2) && ($tax_address['entry_zone_id'] == '43')) {
   return 10.5000;
} elseif (in_array( $cust_zip_code, $ny_county_3) && ($tax_address['entry_zone_id'] == '43')) {
   return 8.5200;
}

elseif (($zip_in_county == 'false') && ($address_query['entry_zone_id'] == '43')) {

 return 0;

}

elseif ( ($country_id == -1) && ($zone_id == -1) ) {

     if (!tep_session_is_registered('customer_id')) {

       $country_id = STORE_COUNTRY;

       $zone_id = STORE_ZONE;

     } else {

       $country_id = $customer_country_id;

       $zone_id = $customer_zone_id;

     }

   }

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in

 

I assume this query isn't working because I put the zip code of the test user (10011) in it.

 

I am baffled. I believe i have the tax address query correct- i copied out of orders....

Edited by bastille
Link to comment
Share on other sites

  • 2 weeks later...
I tried switching it to that query method an still getting that error  :'(

$tax_address_query = tep_db_query("select ab.entry_country_id, ab.entry_zone_id, ab.entry_postcode from " . TABLE_ADDRESS_BOOK . " ab left join " . TABLE_ZONES . " z on (ab.entry_zone_id = z.zone_id) where ab.customers_id = '" . (int)$customer_id . "' and ab.address_book_id = '" . (int)($this->content_type == 'virtual' ? $billto :$sendto) . "'");

$tax_address = tep_db_fetch_array($tax_address_query);

$ny_county_1 = array('10011', '36551', '36555', '36559');
$ny_county_2 = array('36561', '36562', '36564', '36567');
$ny_county_3 = array('36577', '36580', '36526', '36532');

$cust_zip_code = $tax_address['entry_postcode'];

if (in_array( $cust_zip_code, $ny_county_1) && ($tax_address['entry_zone_id'] == '43')) {
? ?return 10.0000;
} elseif (in_array( $cust_zip_code, $ny_county_2) && ($tax_address['entry_zone_id'] == '43')) {
? ?return 10.5000;
} elseif (in_array( $cust_zip_code, $ny_county_3) && ($tax_address['entry_zone_id'] == '43')) {
? ?return 8.5200;
}

elseif (($zip_in_county == 'false') && ($address_query['entry_zone_id'] == '43')) {

?return 0;

}

elseif ( ($country_id == -1) && ($zone_id == -1) ) {

? ? ?if (!tep_session_is_registered('customer_id')) {

? ? ? ?$country_id = STORE_COUNTRY;

? ? ? ?$zone_id = STORE_ZONE;

? ? ?} else {

? ? ? ?$country_id = $customer_country_id;

? ? ? ?$zone_id = $customer_zone_id;

? ? ?}

? ?}

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in

 

I assume this query isn't working because I put the zip code of the test user (10011) in it.

 

I am baffled. I believe i have the tax address query correct- i copied out of orders....

 

What coding did you decide to go with this. I have no programming experience and want to use the zip code method also. I would appreciate being able to use the code you develop on my site. Thank You.

Link to comment
Share on other sites

What coding did you decide to go with this.  I have no programming experience and want to use the zip code method also.  I would appreciate being able to use the code you develop on my site.  Thank You.

 

Hello

with the help of some very kind OScommerce posters I have been putting something together as still have a couple of problems as soon as they are fixed I will post here or as a contribution.

 

For now, I just have the counties as states (awkward but it works :) )

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...