I have this stored procedure that has 2 parameters, the first one is the cartId(which is the IN) and I have the newOrderId(which is the OUT). Now,
I know that the inCartId is the cart id which will come from the form or whatever means the cart is been access but I don't understand the OUT
newOrderId since that is what MySQL will return.
Now, in the PHP calling this Stored procedure, it will look like this.
$sql = 'CALL create_order(:cart_id, "What will be here")// The cart_id is what PHP will pass to MySQL, but what will be passed
to the 2nd argument since does not before hand what MySQL will return.
CREATE PROCEDURE create_order(
IN inCartId int,
OUT newOrderId int
)
BEGIN
-- Insert a new record into orders and obtain the new order ID
INSERT INTO orders (created_on) VALUES (NOW());
-- Obtain the new Order ID
SELECT LAST_INSERT_ID() INTO newOrderId;
-- Insert order details in order_detail table
INSERT INTO order_detail (
order_id, product_id, attributes, product_name, quantity, unit_cost
)
SELECT
orderId,
p.id,
sc.attributes,
p.name,
sc.quantity,
COALESCE( NULLIF( p.discounted_price, 0 ), p.price ) AS unit_cost
FROM
shopping_cart sc
INNER JOIN products p ON sc.product_id = p.id
WHERE
sc.cart_id = inCartId
AND
sc.buy_now;
-- Save the order's total amount
UPDATE
orders
SET
total_amount = (
SELECT
SUM( unit_cost * quantity )
FROM
order_detail
WHERE
order_id = orderId
)
WHERE
id = orderId;
-- Clear the shopping cart
CALL shopping_cart_empty(inCartId);
END
