# SQL to Calculate Shipping Costs

I need a SQL query and any PHP script necessary for taking the shopping cart total, number of items and combined weight of items, then queries the "ship_charge_ist" table to determine which rules should be applied based on the cart parameters. The query should then return the total of all shipping charges.

See attachment for complete details including database schema (MySQL).

This SQL query needs to be able to take into consideration multiple rules and return the sum of all rules that match. I would like the majority of logic/calculations to be done in SQL, then anything else needs to be written in PHP.

This is my first attempt at something like this, so if you are an expert in this field, please let me know and also provide any feedback or suggestions you feel will make this a better solution. The goal of this is to provide a very flexible and fast shipping mechanism for a custom cart implementation.

Thank you,

Spencer

## Deliverables

I need someone to write a SQL query and any PHP script necessary for taking the shopping cart total, number of items and combined weight of items, then queries the "ship_charge_ist" table to determine which rules should be applied based on the cart parameters. The query should then return the total of all shipping charges.

See attachment for complete details including database schema (MySQL).

Incoming Variables from the shopping cart:

============================================

\$cart_ttl_weight = 10;? ? // Combined weight total of all items in cart (lbs)

\$cart_ttl_items = 3;? ? // Combined quantity of items in cart

\$cart_ttl_amount = 100.00;? // Sum of all items in cart

ship_charge_ist (db table)

============================

id

weight_min? ? = Minimum weight in cart to apply this rule

weight_max? ? = Maximum weight in cart to apply this rule

cart_total_min? ? = Minimum dollar amount that must be in cart in order to apply this rule

cart_total_max? ? = Maximum dollar amount that must be in cart in order to apply this rule

amount_flat? ? = If this rule is met, than return this "flat rate" dollar amount as the "final shipping charge"

amount_per_item? ? = If this rule is met, than return this "flat rate per item" (must multiply by total items quantity) as the "final shipping charge"

amount_percentage? = If this rule is met, than return "percentage value" based on total dollar amount in cart as the "final shipping charge"

amount_per_lb? ? = If this rule is met, than return "flat rate" (must multiply by sum of all items weight) as the "final shipping charge"

This is the basic formular that should be considered:

\$amount_flat + ( \$cart_ttl_weight * \$amount_per_lb ) + ( \$cart_ttl_items * \$amount_per_item ) + ( \$cart_ttl_amount * \$amount_percentage / 100 )

--

-- Table structure for table `ship_charge_ist`

--

/*

CREATE TABLE `ship_charge_ist` (

? `id` int(11) NOT NULL auto_increment,

? `weight_min` decimal(13,2) NOT NULL default '0.00',

? `weight_max` decimal(13,2) NOT NULL default '999999.99',

? `cart_total_min` decimal(13,2) NOT NULL default '0.00',

? `cart_total_max` decimal(13,2) NOT NULL default '999999.99',

? `amount_flat` decimal(13,2) NOT NULL default '0.00',

? `amount_per_item` decimal(13,2) NOT NULL default '0.00',

? `amount_percentage` decimal(13,2) NOT NULL default '0.00',

? `amount_per_lb` decimal(13,2) NOT NULL default '0.00',

? PRIMARY KEY? (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

*/

--

-- Dumping data for table `ship_charge_ist`

--

INSERT INTO `ship_charge_ist` (`id`, `weight_min`, `weight_max`, `cart_total_min`, `cart_total_max`, `amount_flat`, `amount_per_item`, `amount_percentage`, `amount_per_lb`) VALUES

(1, 0.00, 999999.99, 0.00, 45.00, 5.00, 0.00, 0.00, 0.00),

(2, 100.00, 999999.99, 0.00, 999999.99, 0.00, 0.00, 10.00, 0.00);

NOTE FOR EXAMPLES:

? Record ID#1 means any cart with less than \$45 is charged a flat \$5 shipping fee.

? Record ID#2 means any cart with weight greater than 100 lbs would be an additional 10% of \$cart_ttl_amount

This SQL query needs to be able to take into consideration multiple rules and return the sum of all rules that match. I would like the majority of logic/calculations to be done in SQL, then anything else needs to be written in PHP.

This is my first attempt at something like this, so if you are an expert in this field, please let me know and also provide any feedback or suggestions you feel will make this a better solution. The goal of this is to provide a very flexible and fast shipping mechanism for a custom cart implementation.

Thank you,

Spencer

( 21 reviews ) Portland, United States

Project ID: #3449790

## Awarded to:

teacheronecom

See private message.

\$21.25 USD in 2 days
(13 Reviews)
5.4

## 10 freelancers are bidding on average \$20 for this job

shihan033010

See private message.

\$12.75 USD in 2 days
(319 Reviews)
6.8
phpmysqlaja

See private message.

\$25.5 USD in 2 days
(25 Reviews)
3.9
yweichen

See private message.

\$21.25 USD in 2 days
(20 Reviews)
3.6
JeremyCollins

See private message.

\$25.5 USD in 2 days
(14 Reviews)
3.8

See private message.

\$12.75 USD in 2 days
(5 Reviews)
3.0
matrix07sl

See private message.

\$21.25 USD in 2 days
(4 Reviews)
1.2
farhanahuq

See private message.

\$25.5 USD in 2 days
(0 Reviews)
0.0
jinalshahvw

See private message.

\$17 USD in 2 days
(1 Review)
0.0
tanathoskissro

See private message.

\$17 USD in 2 days
(2 Reviews)
0.0