Quote
My Order Table format is:
Order_ID, Item_ID, Item_Qty,Price, Total.......
That should be two tables, not one.
An "Order" table that summaries the whole thing, and an "Order Item" that holds the detail of each item within that Order.
create table Orders
( id int not null auto_increment
, total decimal( 9, 2 )
, primary key ( id )
);
create table Order_Items
( order_id int not null
, item_id int not null
, qty int not null
, price decimal(9.2) not null
, primary key ( order_id, item_id )
, foreign key order_id references Orders( id )
, foreign key item_id references Items( id ) /* Assumes an Items table that defines anything that can be ordered */
);
select *
from Orders ;
+----+-------+
| id | total |
+----+-------+
| 1 | 2.98 |
+----+-------+
select *
from Order_Items ;
+----------+---------+-----+-------+
| order_id | item_id | qty | price |
+----------+---------+-----+-------+
| 1 | 22 | 1 | 1.99 |
| 1 | 33 | 1 | 0.99 |
+----------+---------+-----+-------+
Regards, Phill W.