PHP

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
<?php

// dummy session data

$userID = 1; 

$cart = array(
  array("product_id" => 1, "qty" => 2, "item_id" => 0, "price" => 0, "subtotal" => 0), 
  array("product_id" => 2, "qty" => 4, "item_id" => 0, "price" => 0, "subtotal" => 0),  
  array("product_id" => 3, "qty" => 6, "item_id" => 0, "price" => 0, "subtotal" => 0));

$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);

try{
  $conn->autocommit(FALSE); // start transaction

  // create the order 

  $sql = sprintf("call insert_order(%d)", $userID);

  $result = $conn->query($sql);
  $row = $result->fetch_array();
  $result->close();

  $orderID = $row["order_id"]; //  new order_id returned by sproc
  $conn->next_result();  

  // loop your cart and insert order items

  foreach($cart as $k => $v){

    $sql = sprintf("call insert_order_item(%d,%d,%d)", $orderID, $v["product_id"],$v["qty"]);

    $result = $conn->query($sql);
    $row = $result->fetch_array();
    $result->close();

    $cart[$k]["item_id"] = $row["item_id"]; // save data returned by sproc incase you need it ??
    $cart[$k]["price"] = $row["price"];
    $cart[$k]["subtotal"] = $row["subtotal"];

    $conn->next_result();  
  }
  $conn->commit(); //all OK so commit order/order items...

  echo sprintf("your order no. is %s<br/>", $orderID);

  $total = 0;
  foreach($cart as $k => $v){
    $total += $v["subtotal"];
    echo sprintf("item_id=%s, product_id=%s, price=%s, qty=%s, subtotal=%s<br/>", 
      $v["item_id"],$v["product_id"],$v["price"],$v["qty"],$v["subtotal"]);
  }
  echo sprintf("order total = %s<br/>", $total);

}
catch(exception $ex){
  //handle errros and rollback
  $conn->rollback();
  echo sprintf("order error - %s<br/>", $ex->getMessage()); 
}

$conn->close();
?>

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
-- TABLES

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varbinary(32) unique not null
)
engine=innodb;

drop table if exists products;
create table products
(
product_id smallint unsigned not null auto_increment primary key,
name varchar(255) unique not null,
price decimal(10,2) not null default 0
)
engine=innodb;

drop table if exists orders;
create table orders
(
order_id int unsigned not null auto_increment primary key,
user_id int unsigned not null,
order_date datetime not null,
total decimal(10,2) not null default 0,
key (user_id)
)
engine=innodb;

drop table if exists order_items;
create table order_items
(
item_id int unsigned not null auto_increment primary key,
order_id int unsigned not null,
product_id smallint unsigned not null,
qty smallint unsigned not null default 0,
price decimal(10,2) not null default 0,
subtotal decimal(10,2) not null default 0,
key (order_id),
key (product_id)
)
engine=innodb;

-- STORED PROCEDURES

drop procedure if exists insert_order;

delimiter #

create procedure insert_order
(
in p_user_id int unsigned
)
proc_main:begin

declare v_order_id int unsigned default 0;

  insert into orders (user_id, order_date, total) values (p_user_id, now(), 0);

  set v_order_id = last_insert_id();

  -- do more things with v_order_id ??

  select v_order_id as order_id;

end proc_main #

delimiter ;

drop procedure if exists insert_order_item;

delimiter #

create procedure insert_order_item
(
in p_order_id int unsigned,
in p_product_id smallint unsigned,
in p_qty smallint unsigned
)
proc_main:begin

declare v_item_id int unsigned default 0;
declare v_price decimal(10,2) default 0;
declare v_subtotal decimal(10,2) default 0;

  select price into v_price from products where product_id = p_product_id;

  set v_subtotal =  v_price * p_qty;

  insert into order_items (order_id, product_id, qty, price, subtotal) values 
    (p_order_id, p_product_id, p_qty, v_price, v_subtotal);

  set v_item_id = last_insert_id();

  -- do more things with v_item_id ??

  update orders set total = total + v_subtotal where order_id = p_order_id;

  select p_order_id as order_id, v_item_id as item_id, 
    v_price as price, v_subtotal as subtotal;

end proc_main #

delimiter ;

-- TEST DATA

insert into users (username) values ('f00'),('bar'),('alpha'),('beta'),('gamma');
insert into products (name, price) values ('product 1', 9.99),('product 2',12.34),('product 3',32.50),('product 4',1.99);