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

Combine Two Queries?

Recommended Posts

Hi, Larry

 

In page 294. I try to break down the add_order procedure into separated piece since my shared host doesn't support routine. 

 

The problem lies in the last two statement. I believe the subtotal is a temporary column to store  the result of quantity * price_per and then deal with the UPDATE statement that comes after. So, what's the best way to rewrite those two statements as normal sql query?

 

I personally try to combine those two queries since they should work as one: 

$query = "UPDATE orders SET total=((SELECT SUM(quantity*price_per) INTO subtotal FROM order_contents WHERE order_id=$oid) + $shipping ) WHERE id=$oid";
 

Do you think this gonna work? 

Share this post


Link to post
Share on other sites

Are you getting error messages here? If so, please share them. By the looks of it, you should replace "INTO" with "AS" in your subquery, but I don't I don't know if that's your problem. It's most likely a syntax error though.

 

You must obviously have data in both orders and order_contents for this to work.

$query = "UPDATE orders SET 
   total = (SELECT SUM(quantity*price_per) AS subtotal FROM order_contents WHERE order_id = {$oid} + $shipping) 
   WHERE id = {$oid}
";

Hope that helps you out. If not, we need some more info from you. :)

Share this post


Link to post
Share on other sites

Hi, Antonio 

 

After reading your reply, I tested the script. As you said, the sql is wrong and it returns a message : Error Code: 1327. Undeclared variable: subtotal.

 

So I adapter your method, replace the INTO with AS, and the script is fully functional now. Thank you very much for pointing out. 

Share this post


Link to post
Share on other sites

and here is the statement block that comes from the add_order procedure. It works to me, so post it as reference

if (empty($billing_errors)) {
        $cc_exp = sprintf('%02d%d', $_POST['cc_exp_month'], $_POST['cc_exp_year']);
        
        // check for existing order ID in the session
        // to prevent double orders
        if (isset($_SESSION['order_id'])) {
            $order_id = $_SESSION['order_id'];
            $order_total = $_SESSION['order_total'];
        } else {
           
            // if no existing order ID, get the last four digitals of cc number
            $cc_last_four = substr($cc_number, -4);
            
            // store the order:
            
            $shipping = $_SESSION['shipping'];
           
            // Insert into orders table
            $query = "INSERT INTO orders (customer_id, shipping, credit_card_number,order_date)
                 VALUES ({$_SESSION['customer_id']}, $shipping, $cc_last_four, NOW())";
            $result = mysqli_query($dbc, $query);
            if (mysqli_affected_rows($dbc) == 1) {
                $oid = mysqli_insert_id($dbc);
                // Insert into order_contents
                $query = "
                INSERT INTO order_contents (order_id, product_type, product_id, quantity, price_per) 
                SELECT $oid, c.product_type, c.product_id, c.quantity, IFNULL(sales.price, ncp.price) 
                FROM carts AS c 
                INNER JOIN non_coffee_products AS ncp ON c.product_id=ncp.id 
                LEFT OUTER JOIN sales ON (sales.product_id=ncp.id AND sales.product_type='goodies' 
                AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL))) 
                WHERE c.product_type=\"goodies\" AND c.user_session_id='$uid' 
                UNION 
                SELECT $oid, c.product_type, c.product_id, c.quantity, IFNULL(sales.price, sc.price) 
                FROM carts AS c 
                INNER JOIN specific_coffees AS sc ON c.product_id=sc.id 
                LEFT OUTER JOIN sales ON (sales.product_id=sc.id AND sales.product_type='coffee' 
                AND ((NOW() BETWEEN sales.start_date AND sales.end_date) OR (NOW() > sales.start_date AND sales.end_date IS NULL))) 
                WHERE c.product_type=\"coffee\" AND c.user_session_id='$uid';
                ";
                
                $result = mysqli_query($dbc, $query);
                if (mysqli_affected_rows($dbc) == 1) {
                    // Insert subtotal
                    $query = "UPDATE orders 
                              SET total=((SELECT SUM(quantity*price_per) AS subtotal FROM order_contents WHERE order_id=$oid) + $shipping) 
                              WHERE id=$oid";
                    $result = mysqli_query($dbc, $query); 
                    
                    if ($result) {
                        // get the value of total amount and order id
                        $query = "SELECT total FROM orders WHERE id=$oid";
                        $result = mysqli_query($dbc, $query);
                        list($order_total) = mysqli_fetch_array($result);
                        
                        $_SESSION['order_total'] = $order_total;
                        $_SESSION['order_id'] = $oid;
                        
                    } else { // could not retrieve the order ID and total
                        unset($cc_number, $cc_cvv, $_POST['cc_number'], $_POST['cc_cvv']);
                        trigger_error('Your order could not be processed due to a system error. We apologize for the inconvenience.');
                    }
                }
            } else { // The add_order() procedure failed

                unset($cc_number, $cc_cvv, $_POST['cc_number'], $_POST['cc_cvv']);
                trigger_error('Your order could not be processed due to a system error. We apologize for the inconvenience.');
            }
            
        }
        
    }

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...