Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
Gbenga

Returning value from Stored Precedure

Recommended Posts

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
 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...