Combine Two Queries?

[100yen]

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? 

[Antonio Conte]

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.

[100yen]

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. 

[100yen]

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.');
            }
            
        }
        
    }