Calculate shipping rate according the items weight using PHP

Question

What I have in MySQL table

|seller |product |weight |shipping|
|---------------------------------|
|Jacob  |Mobile  | 500g  |        |
|John   |Laptop  | 3500g |        |
|Kiki   |Charger | 1000g |        |
|Dani   |Keyboard| 1500g |        |

In my shipping-calculator.php page I have this code

<?php
 if($pro_weight <= 500){
    $shipping_price = 10;
 }else if($pro_weight <= 1000){
    $shipping_price = 15;
 }else if($pro_weight <= 2000){
    $shipping_price = 25;
 }else if($pro_weight <= 3000){
    $shipping_price = 35;
 }else if($pro_weight <= 4000){
    $shipping_price = 45;
 }else if($pro_weight <= 5000){
    $shipping_price = 55;
 }else if($pro_weight <= 6000){
    $shipping_price = 62.5;
 }else if($pro_weight <= 7000){
    $shipping_price = 70;
 }else if($pro_weight <= 8000){
    $shipping_price = 77.5;
 }else if($pro_weight <= 9000){
    $shipping_price = 95;
 }else if($pro_weight <= 10000){
    $shipping_price = 100;
 }else{
   $shipping_price = 'Sorry We can\'t deliver over 10 KGs';
  }
 ?>

What I want

If one customer orders x2 items from Jacob seller, x1 item from John, and x3 from Dani, then this customer now ordered 6 items with total 9,000 gram weight. For total shipping of 9kg is easy, it will be 95 USD. But there are different items from different sellers so I want to calculate for each seller's ordered weight, price, and then add them all to show the buyer and to show his/her own total ordered weight for each seller?

How can I achieve this goal? Thank you for any advice you assist me.

Answer 1

Rather than hard-coding anything in PHP the freigh/shipping charges ought to be within a database table as mentioned by others. A hastily drafted example db schema and query should show how this might be done and utilised - though there are no doubt plenty of refinements that could be made.

Given a rough schema as follows:

create table `seller-products` (
    `id` int(10) unsigned not null auto_increment,
    `seller` varchar(50) not null,
    `product` varchar(50) not null,
    `weight` int(11) not null default '0',
    primary key (`id`)
)
engine=innodb
auto_increment=5;


create table `buyer-products` (
    `id` int(10) unsigned not null auto_increment,
    `buyer` varchar(50) not null,
    `seller` int(10) unsigned not null,
    `qty` int(10) unsigned not null,
    primary key (`id`),
    index `seller` (`seller`)
)
engine=innodb
auto_increment=3;


create table `seller-freight` (
    `id` int(10) unsigned not null auto_increment,
    `low` int(10) unsigned not null,
    `high` int(10) unsigned not null,
    `charge` decimal(8,2) unsigned not null,
    primary key (`id`)
)
engine=innodb
auto_increment=13;

With some dummy data to emulate some of the question data:

insert into `seller-products` (`id`, `seller`, `product`, `weight`) values
    (1, 'jacob', 'mobile', 500),
    (2, 'john', 'laptop', 3500),
    (3, 'kiki', 'charger', 1000),
    (4, 'dani', 'keyboard', 1500);
    
insert into `buyer-products` (`id`, `buyer`, `seller`, `qty`) values
    (1, 'susan', 1, 3),
    (2, 'charlie', 2, 5),
    (3, 'lisa', 3, 3);

With a sort of matrix lookup table that sets a price if the weight is between low and high values.

insert into `seller-freight` (`id`, `low`, `high`, `charge`) values
    (1, 0, 500, 10.00),
    (2, 501, 1000, 15.00),
    (3, 1001, 2000, 25.00),
    (4, 2001, 3000, 35.00),
    (5, 3001, 4000, 45.00),
    (6, 4001, 5000, 55.00),
    (7, 5001, 6000, 62.50),
    (8, 6001, 7000, 70.00),
    (9, 7001, 8000, 77.50),
    (10, 8001, 9000, 95.00),
    (11, 9001, 10000, 100.00),
    (12, 10001, 1000000000, 0.00);




mysql> select * from `seller-products`;
+----+--------+----------+--------+
| id | seller | product  | weight |
+----+--------+----------+--------+
|  1 | Jacob  | Mobile   |    500 |
|  2 | John   | Laptop   |   3500 |
|  3 | Kiki   | Charger  |   1000 |
|  4 | Dani   | Keyboard |   1500 |
+----+--------+----------+--------+

mysql> select * from `buyer-products`;
+----+---------+--------+-----+
| id | buyer   | seller | qty |
+----+---------+--------+-----+
|  1 | Susan   |      1 |   3 |
|  2 | Charlie |      2 |   5 |
|  3 | Lisa    |      3 |   3 |
+----+---------+--------+-----+

mysql> select * from `seller-freight`;
+----+-------+------------+-----------+
| id | low   | high       | charge    |
+----+-------+------------+-----------+
|  1 |     0 |        500 |     10.00 |
|  2 |   501 |       1000 |     15.00 |
|  3 |  1001 |       2000 |     25.00 |
|  4 |  2001 |       3000 |     35.00 |
|  5 |  3001 |       4000 |     45.00 |
|  6 |  4001 |       5000 |     55.00 |
|  7 |  5001 |       6000 |     62.50 |
|  8 |  6001 |       7000 |     70.00 |
|  9 |  7001 |       8000 |     77.50 |
| 10 |  8001 |       9000 |     95.00 |
| 11 |  9001 |      10000 |    100.00 |
| 12 | 10001 | 1000000000 |      0.00 |
+----+-------+------------+-----------+

A quick query that draws from all tables to calculate the price of shipping per customer:

select 
    sp.`product`,
    sp.`seller`,
    bp.`buyer`,
    bp.`qty`,
    sp.`weight` as `item-weight`,
    ( sp.weight * bp.qty ) as `gross-weight`,
    case
        when ( sp.weight * bp.qty ) > 10000 then 'Error: Over limit'
        else
            ( select `charge` from `seller-freight` where sp.weight * bp.qty between `low` and `high` )
    end as `charge`
from `buyer-products` bp
join `seller-products` sp on sp.id=bp.`seller`;





+---------+--------+---------+-----+-------------+--------------+-------------------+
| product | seller | buyer   | qty | item-weight | gross-weight | charge            |
+---------+--------+---------+-----+-------------+--------------+-------------------+
| Mobile  | Jacob  | Susan   |   3 |         500 |         1500 | 25.00             |
| Laptop  | John   | Charlie |   5 |        3500 |        17500 | Error: Over limit |
| Charger | Kiki   | Lisa    |   3 |        1000 |         3000 | 35.00             |
+---------+--------+---------+-----+-------------+--------------+-------------------+

To accomplish the grand-total type of figure one method might be to use a runtime variable that gets incremented with some value. In this case you want to show the total charges accrued by a customer so you might do something like this perhaps:

set @buyer='susan';
set @gt=0;
select 
    sp.`product`,
    sp.`seller`,
    bp.`buyer`,
    bp.`qty`,
    sp.`weight` as `item-weight`,
    ( sp.weight * bp.qty ) as `gross-weight`,
    case
        when ( sp.weight * bp.qty ) > 10000 then 'Error: Over limit'
        else
            ( select `charge` from `seller-freight` where sp.weight * bp.qty between `low` and `high` ) 
    end as `charge`,
    @gt:=@gt + (
        case
            when ( sp.weight * bp.qty ) > 10000 then 0
            else
                ( select `charge` from `seller-freight` where sp.weight * bp.qty between `low` and `high` ) 
        end
    ) as `total-charges`
from `buyer-products` bp
join `seller-products` sp on sp.id=bp.`seller`
where bp.`buyer`=@buyer;

And, having added a few more rows of dummy data the above query yields a result like this:

+----------+--------+-------+-----+-------------+--------------+--------+---------------+
| product  | seller | buyer | qty | item-weight | gross-weight | charge | total-charges |
+----------+--------+-------+-----+-------------+--------------+--------+---------------+
| Mobile   | Jacob  | Susan |   3 |         500 |         1500 | 25.00  |         25.00 |
| Laptop   | John   | Susan |   1 |        3500 |         3500 | 45.00  |         70.00 |
| Charger  | Kiki   | Susan |   1 |        1000 |         1000 | 15.00  |         85.00 |
| Keyboard | Dani   | Susan |   2 |        1500 |         3000 | 35.00  |        120.00 |
+----------+--------+-------+-----+-------------+--------------+--------+---------------+

Clearly Susan is up to no good with 3 mobiles

Answer 2

Do you just want aggregation?

select sum(case when seller = 'Jacob' then 2 * weight
                when seller = 'John' then 1 * weight
                when seller = 'Dani' then 3 * weight
                else 0
           end)
from t;

That said, it seems like you should have some sort of basket where these quantities are stored in the database. In fact, instead of hard-coding the shipping price in PHP code, that should also be in a table.