SQL problem with adding the shipping value to vat and price

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

ashebrian
Forum Contributor
Posts: 103
Joined: Sat Feb 02, 2008 8:01 pm

SQL problem with adding the shipping value to vat and price

Post by ashebrian »

I have a problem with getting the total amountin mysql for my php codes. I am able to get as far as adding all of them BUT with one tiny problem. My shipping cost is not charged to everyone. If the price is over 300 quid then its free otherwise is 10 quid. I have tried to use case's but the error msg comes up:

Code: Select all

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'THEN 10 END FROM tbl_order WHERE od_id
My table is as follows:

Code: Select all

 
"INSERT INTO tbl_order(od_date, member_username, od_last_update, od_shipping_first_name, od_shipping_last_name, od_shipping_address1, od_shipping_address2, od_shipping_phone, od_shipping_state, od_shipping_city, od_shipping_postal_code, od_shipping_cost, od_vat, od_total_order_amount, od_payment_first_name, od_payment_last_name, od_payment_address1, od_payment_address2, od_payment_phone, od_payment_state, od_payment_city, od_payment_postal_code) VALUES (NOW(), '$hidClient', NOW(), '$hidShippingFirstName', '$hidShippingLastName', '$hidShippingAddress1', '$hidShippingAddress2', '$hidShippingPhone', '$hidShippingState', '$hidShippingCity', '$hidShippingPostalCode', '$shippingCost', '$vat', '$hidTotalAmount','$hidPaymentFirstName', '$hidPaymentLastName', '$hidPaymentAddress1', '$hidPaymentAddress2', '$hidPaymentPhone', '$hidPaymentState', '$hidPaymentCity', '$hidPaymentPostalCode')";
My code to calculate the price is as follows:

Code: Select all

"SELECT SUM(pd_price * od_qty)
                   FROM tbl_order_item oi, tbl_product p 
               WHERE oi.pd_id = p.pd_id and oi.od_id = $orderId
            
            UNION
            
            SELECT SUM(od_vat)
            FROM tbl_order
            WHERE od_id = $orderId
            
            UNION
            
            SELECT CASE WHEN SUM(pd_price * od_qty) <= 300 THEN 0
            ELSE SUM(pd_price * od_qty) >= 300 THEN 10
            END
            FROM tbl_order
            WHERE od_id = $orderId";
The problem lies in the last select statement with the CASE WHEN SUM. Can you please help as i'm so so soooooooooo close finishing it off. Please
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: SQL problem with adding the shipping value to vat and price

Post by califdon »

Aren't you storing the price and qty of what was ordered?

You are breaking one of the rules of relational database management if you are storing both the total amount and the components of that total. In technical terms, it violates 3rd Normal Form.

The normal way to handle your situation is to store the item ordered, the quantity, the VAT and the shipping cost. Not the total. The total is calculated when you retrieve the data from the database for whatever purpose.

Beyond that, the reason for the error is that your syntax is wrong, just like the error message says. Read it over carefully. Since you only have 2 conditions, you only need one WHEN (an IF would be more appropriate) and an ELSE. Also, since your first condition is if the price/qty <= 300, and your second condition is if the price/qty >= 300, what do you want to happen if it is exactly 300? The way you have it, the first condition will prevail.
ashebrian
Forum Contributor
Posts: 103
Joined: Sat Feb 02, 2008 8:01 pm

Re: SQL problem with adding the shipping value to vat and price

Post by ashebrian »

Hi, Firstly the <=300 should be <=299, stupid mistake there. I am storing the price and quantity. But i've added the od_total_order_amount in the db to try and get the total of the price and quantity into that row....however it did not work as it is set to '0'. I have all these stored item ordered, the quantity, the VAT and the shipping cost but not the total.

I don't understand with this condition 'price/qty <= 300', I don't want it divided. I need to * the price by the quantity to get the the total price of the order with the shipping cost added or not depending on the price.

I've sorted out the error but for the 3rd party not accepting my payments due to Invalid characters in amount field.

Code is now as follows:

Code: Select all

function getOrderAmount($orderId)
{
    
    $orderAmount = 0;
    
    $sql = "SELECT SUM(pd_price * od_qty)
            FROM tbl_order_item oi, tbl_product p 
            WHERE oi.pd_id = p.pd_id and oi.od_id = $orderId
            
            UNION
            
            SELECT SUM(od_vat)
            FROM tbl_order
            WHERE od_id = $orderId
            
            UNION
            
            SELECT CASE od_shipping_cost 
            WHEN SUM(pd_price * od_qty) < 300 THEN 0
            ELSE 10 END
            FROM tbl_order, tbl_order_item oi, tbl_product p
            WHERE oi.pd_id = p.pd_id and oi.od_id = $orderId";
            
    $result = dbQuery($sql);
 
    if (dbNumRows($result) == 3) {
        $row = dbFetchRow($result);
        $totalPurchase = $row[0];
        
        $row = dbFetchRow($result);
        $vat = $row[0];
        
        $row = dbFetchRow($result);
        $shippingCost = $row[0];
        
        $orderAmount = ($totalPurchase * 100) + ((($vat / 100) * $totalPurchase) * 100) + ($shippingCost * 100);
    }   
    return $orderAmount;    
}
 
As you can see, its either this line:

Code: Select all

$orderAmount = ($totalPurchase * 100) + ((($vat / 100) * $totalPurchase) * 100) + ($shippingCost * 100);
or the sql statement giving me this problem.

The 3rd party does not accept decimal points and accepts the amount in cents. So i've *'d each one by 100 to give me cents but the 3rd party won't accept it.

I do not understand it can you please advise.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: SQL problem with adding the shipping value to vat and price

Post by califdon »

ashebrian wrote:Firstly the <=300 should be <=299,
No, then you would not record a sale of 299.50. Don't use <= and => together. It should be <=300 and >300, or <300 and >=300.
I am storing the price and quantity.
I don't see where you are doing that. Nothing in your INSERT query looks like you are doing that.
But i've added the od_total_order_amount in the db to try and get the total of the price and quantity into that row..
That's where you are going wrong. You should never store calculated values in a relational database. It's just wrong.
I don't understand with this condition 'price/qty <= 300', I don't want it divided. I need to * the price by the quantity to get the the total price of the order with the shipping cost added or not depending on the price.
You have totally lost me. I don't see that anywhere in what you have presented. What are you talking about?
I've sorted out the error but for the 3rd party not accepting my payments due to Invalid characters in amount field.

Code is now as follows:

Code: Select all

function getOrderAmount($orderId)
{
    
    $orderAmount = 0;
    
    $sql = "SELECT SUM(pd_price * od_qty)
            FROM tbl_order_item oi, tbl_product p 
            WHERE oi.pd_id = p.pd_id and oi.od_id = $orderId
            
            UNION
            
            SELECT SUM(od_vat)
            FROM tbl_order
            WHERE od_id = $orderId
            
            UNION
            
            SELECT CASE od_shipping_cost 
            WHEN SUM(pd_price * od_qty) < 300 THEN 0
            ELSE 10 END
            FROM tbl_order, tbl_order_item oi, tbl_product p
            WHERE oi.pd_id = p.pd_id and oi.od_id = $orderId";
            
    $result = dbQuery($sql);
 
    if (dbNumRows($result) == 3) {
        $row = dbFetchRow($result);
        $totalPurchase = $row[0];
        
        $row = dbFetchRow($result);
        $vat = $row[0];
        
        $row = dbFetchRow($result);
        $shippingCost = $row[0];
        
        $orderAmount = ($totalPurchase * 100) + ((($vat / 100) * $totalPurchase) * 100) + ($shippingCost * 100);
    }   
    return $orderAmount;    
}
 
As you can see, its either this line:

Code: Select all

$orderAmount = ($totalPurchase * 100) + ((($vat / 100) * $totalPurchase) * 100) + ($shippingCost * 100);
or the sql statement giving me this problem.
You have enormously over-complicated what you are trying to accomplish. One simple SELECT statement should be all that is required, if you have to extract the data from the database. Is that how you have to do it, for the 3rd party? How are you submitting the data to the 3rd party?
The 3rd party does not accept decimal points and accepts the amount in cents. So i've *'d each one by 100 to give me cents but the 3rd party won't accept it.

I do not understand it can you please advise.
I would like to try to help you, but your description is very confusing. You will need to explain the process for submitting data to the 3rd party and be more specific about the sequence of events.

1. Customer fills out your order form.
2. You process form, record order in database.
3. You perform some other process for 3rd party.

Is that what you are saying? You will have to describe what that other process is. I can't understand at all what you are doing.
ashebrian
Forum Contributor
Posts: 103
Joined: Sat Feb 02, 2008 8:01 pm

Re: SQL problem with adding the shipping value to vat and price

Post by ashebrian »

Ok......sorry if its very confusing.
No, then you would not record a sale of 299.50. Don't use <= and => together. It should be <=300 and >300, or <300 and >=300.
From your first qoute i get you as for the cents i've forgotten about that.
I am storing the price and quantity.
I've given you part of the code but not all here's my Full code for this script which tells you i'm storing the price and quantity information:

Code: Select all

<?php
require_once 'config.php';
include('../include/session2.php');
 
/*********************************************************
*                 CHECKOUT FUNCTIONS 
*********************************************************/
function saveOrder()
{
    $orderId       = 0;
    $vat  = 21;
    $requiredField = array('hidShippingFirstName', 'hidShippingLastName', 'hidShippingAddress1', 'hidShippingCity', 'hidShippingPostalCode',
                           'hidPaymentFirstName', 'hidPaymentLastName', 'hidPaymentAddress1', 'hidPaymentCity', 'hidPaymentPostalCode');
                           
    if (checkRequiredPost($requiredField)) {
        extract($_POST);
        
        // make sure the first character in the 
        // customer and city name are properly upper cased
        $hidShippingFirstName = ucwords($hidShippingFirstName);
        $hidShippingLastName  = ucwords($hidShippingLastName);
        $hidPaymentFirstName  = ucwords($hidPaymentFirstName);
        $hidPaymentLastName   = ucwords($hidPaymentLastName);
        $hidShippingCity      = ucwords($hidShippingCity);
        $hidPaymentCity       = ucwords($hidPaymentCity);
                
        $cartContent = getCartContent();
        $numItem     = count($cartContent);
        
        // save order & get order id
        $sql = "INSERT INTO tbl_order(od_date, member_username, od_last_update, od_shipping_first_name, od_shipping_last_name, od_shipping_address1, 
                                      od_shipping_address2, od_shipping_phone, od_shipping_state, od_shipping_city, od_shipping_postal_code, od_shipping_cost, od_vat, od_total_order_amount, 
                                      od_payment_first_name, od_payment_last_name, od_payment_address1, od_payment_address2, 
                                      od_payment_phone, od_payment_state, od_payment_city, od_payment_postal_code)
                VALUES (NOW(), '$hidClient', NOW(), '$hidShippingFirstName', '$hidShippingLastName', '$hidShippingAddress1', 
                        '$hidShippingAddress2', '$hidShippingPhone', '$hidShippingState', '$hidShippingCity', '$hidShippingPostalCode', '$shippingCost', '$vat', '$hidTotalAmount',
                        '$hidPaymentFirstName', '$hidPaymentLastName', '$hidPaymentAddress1', 
                        '$hidPaymentAddress2', '$hidPaymentPhone', '$hidPaymentState', '$hidPaymentCity', '$hidPaymentPostalCode')";
        $result = dbQuery($sql);
        
        // get the order id
        $orderId = dbInsertId();
        
        if ($orderId) {
            // save order items
            for ($i = 0; $i < $numItem; $i++) {
                $sql = "INSERT INTO tbl_order_item(od_id, pd_id, od_qty)
                        VALUES ($orderId, {$cartContent[$i]['pd_id']}, {$cartContent[$i]['ct_qty']})";
                $result = dbQuery($sql);                    
            }
        
            
            // update product stock
            for ($i = 0; $i < $numItem; $i++) {
                $sql = "UPDATE tbl_product 
                        SET pd_qty = pd_qty - {$cartContent[$i]['ct_qty']}
                        WHERE pd_id = {$cartContent[$i]['pd_id']}";
                $result = dbQuery($sql);                    
            }
            
            
            // then remove the ordered items from cart
            for ($i = 0; $i < $numItem; $i++) {
                $sql = "DELETE FROM tbl_cart
                        WHERE ct_id = {$cartContent[$i]['ct_id']}";
                $result = dbQuery($sql);                    
            }                           
        }                   
    }
    
    return $orderId;
}
 
/*
    Get order total amount ( total purchase + shipping cost + vat)
*/
function getOrderAmount($orderId)
{
    
    $orderAmount = 0;
    
    $sql = "SELECT SUM(pd_price * od_qty)
            FROM tbl_order_item oi, tbl_product p 
            WHERE oi.pd_id = p.pd_id and oi.od_id = $orderId
            
            UNION
            
            SELECT SUM(od_vat)
            FROM tbl_order
            WHERE od_id = $orderId
            
            UNION
            
            SELECT CASE od_shipping_cost 
            WHEN SUM(pd_price * od_qty) < 300 THEN 0
            ELSE 10 END
            FROM tbl_order, tbl_order_item oi, tbl_product p
            WHERE oi.pd_id = p.pd_id and oi.od_id = $orderId";
            
    $result = dbQuery($sql);
 
    if (dbNumRows($result) == 3) {
        $row = dbFetchRow($result);
        $totalPurchase = $row[0];
        
        $row = dbFetchRow($result);
        $vat = $row[0];
        
        $row = dbFetchRow($result);
        $shippingCost = $row[0];
        
        $orderAmount = ($totalPurchase * 100) + ((($vat / 100) * $totalPurchase) * 100) + ($shippingCost * 100);
    }   
    return $orderAmount;    
}
 
?>
As you can see. the code is working very well but for the simple sql statement i'm struggling with.
You should never store calculated values in a relational database. It's just wrong.
Its been quite a while since i've work with sql in college so i am bond to forget some of the things. Never store calculated values........i'll def remember this now as i was trying to figure out an easier way to get the value as i couldn't get the sql statement to work the other way.
'price/qty <= 300'
When you showed me that code i persumed that you were telling me to divide the price by quantity as shown......and that price will be less than or = to 300. I tried to explain that the correct price i'm looking for is the price * qty <= 300.
You have enormously over-complicated what you are trying to accomplish. One simple SELECT statement should be all that is required, if you have to extract the data from the database. Is that how you have to do it, for the 3rd party? How are you submitting the data to the 3rd party?
You are correct about the Select statement as that is what i'm trying to achieve, trying to get it from the db. Lets call this php script checkout.php, from here i'll be submitting this data to a request.php file which will then by sent to the 3rd party's secure credit card page.
The request.php file is as follows: i've left out the top part that you don't need to see for security reasons, as you can see the $orderAmount will come from my checkout.php script for the amount.

Code: Select all

//The code below is used to create the timestamp format required byxxxx
 
$timestamp = strftime("%Y%m%d%H%M%S");
mt_srand((double)microtime()*1000000);
 
/*
 
 orderid:Replace this with the order id you want to use.The order id must be unique.
 In the example below a combination of the timestamp and a random number is used.
 
*/
 
$orderid = $timestamp."-".$orderId;
 
 
/*
In this example these values are hardcoded. In reality you may pass 
these values from another script or take it from a database. 
*/
$curr = "EUR";
$amount = $orderAmount;
 
/*-----------------------------------------------
Below is the code for creating the digital signature using the MD5 algorithm provided
by PHP. you can use the SHA1 algorithm alternatively. 
*/
$tmp = "$timestamp.$merchantid.$orderid.$amount.$curr";
$md5hash = md5($tmp);
$tmp = "$md5hash.$secret";
$md5hash = md5($tmp);
 
?>
<!-- Start of Outer table -->
                <br>
                <br>
                <br>
 
<!--
https://xxxxx is the script where the hidden fields
are POSTed to.
 
The values are sent to xxxxx via hidden fields in a HTML form POST.
Please look at the documentation to show all the possible hidden fields you
can send to xxxxx.
 
Note:> 
The more data you send to xxxxx the more details will be available
on our reporting tool, xxxx for the merchant to view and pull reports 
down from.
 
Note:>
If you POST data in hidden fields that are not a xxxxxx hidden field that data 
will be POSTed back directly to your response script. This way you can maintain
data even when you are redirected away from your site
 
-->
    <p>&nbsp;</p>
    <p align="center"><span class="header-txt"><b>Processing 
        Transaction . . . </b></span></p>
    <p>&nbsp;</p>
    <p align="center">Your order is being processed. Please click contine to proceed to the payment section of our secured site.</font></p>
<br>
    <form action="https://xxxxx" method="post">
        <input type="hidden" name="MERCHANT_ID" value="<?=$merchantid?>">
        <input type="hidden" name="ACCOUNT" value="<?=$internet?>">
        <input type="hidden" name="ORDER_ID" value="<?=$orderid?>">
        <input type="hidden" name="CURRENCY" value="<?=$curr?>">
        <input type="hidden" name="AMOUNT" value="<?=$amount?>">
        <input type="hidden" name="TIMESTAMP" value="<?=$timestamp?>">
        <input type="hidden" name="MD5HASH" value="<?=$md5hash?>">
        <input type="hidden" name="AUTO_SETTLE_FLAG" value="1">
        <table width="100%">
            <tr>
                <td align="center">
                    <input type=submit value="Continue">
                </td>
            </tr>
        </table>
    </form>
You will need to explain the process for submitting data to the 3rd party and be more specific about the sequence of events.

1. Customer fills out your order form.
2. You process form, record order in database.
3. You perform some other process for 3rd party.

Is that what you are saying? You will have to describe what that other process is. I can't understand at all what you are doing.
Finally,
1) Customer fills out your order form after the shopping cart.
2) All information is then displayed to customer including the shopping cart info, customer details and shipping details.
3) This info then stores the info from the checkout.php script as shown above into the db and calculates the price which is submitted automatically without this page being viewed by the customer.......to the request.php file as shown above.
4)That request.php file is then passed onto the third party for validation.

Hope this is much clearer as i've only a tiny problem as I KNOW IT and am stuck with it. Sorry again for being confusing earlier.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: SQL problem with adding the shipping value to vat and price

Post by califdon »

Yes, that is much clearer now. Thank you. I will look at it in greater detail shortly, but cannot at this moment. You have explained very well everything except the part about the 3rd party not accepting decimal points in the amount. What is it that they are asking for in that field?

Sorry about the "price/qty" confusion, that was my fault. I intended only to indicate "price-qty", but I understand why you saw it as division.
ashebrian
Forum Contributor
Posts: 103
Joined: Sat Feb 02, 2008 8:01 pm

Re: SQL problem with adding the shipping value to vat and price

Post by ashebrian »

They are asking for the amount to be in cents e.g 100 euros is = to 10000
From 3rd party:

AMOUNT : Total amount to authorise in the lowest unit of the currency –
i.e. 100 euro would be entered as 10000. If there is no decimal
in the currency then (e.g. EUR) contact xxxxx. No decimal points are allowed.
Therefore, what my shopping cart and db are set to are decimal points in the prices so the customers can see them clearly from each product they want to buy and in the shopping cart. However, this is the reason why i have to multiply my $totalPurchase, $vat, $shippingCost by 100 to get them into cents but it didn't work properly as there's a small problem.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: SQL problem with adding the shipping value to vat and price

Post by califdon »

ashebrian wrote:They are asking for the amount to be in cents e.g 100 euros is = to 10000
From 3rd party:

AMOUNT : Total amount to authorise in the lowest unit of the currency –
i.e. 100 euro would be entered as 10000. If there is no decimal
in the currency then (e.g. EUR) contact xxxxx. No decimal points are allowed.
Therefore, what my shopping cart and db are set to are decimal points in the prices so the customers can see them clearly from each product they want to buy and in the shopping cart. However, this is the reason why i have to multiply my $totalPurchase, $vat, $shippingCost by 100 to get them into cents but it didn't work properly as there's a small problem.
Well, first let me show you the SQL that I believe will extract the total amount to be charged, including calculation of VAT, for one order, assuming the contents of your 3 tables includes the fields shown, as I understand from what you showed:

Code: Select all

tbl_order:
   od_id
   od_shipping_cost
   od_vat
   od_total_order_amount
 
 
 
tbl_order_item:
   od_id
   pd_id
   od_qty
 
 
 
tbl_product:
   pd_id
   pd_price
   
 
 
Need: SUM(pd_price * od_qty)
If >= 300 THEN vat=10 ELSE 0
total_order_amount = SUM(pd_price * od_qty) + vat + od_shipping_cost
 
 
 
SELECT tbl_order.od_id, od_qty, SUM(od_qty * pd_price), od_shipping_cost,
      CASE SUM(od_qty * pd_price)
        WHEN >= 300 THEN 10 
        ELSE 0 END AS vat
   FROM tbl_product
   LEFT JOIN tbl_order_item ON tbl_product.pd_id = tbl_order_item.pd_id
   LEFT JOIN tbl_order ON tbl_order_item.od_id = tbl_order.od_id
   WHERE tbl_order_item.od_id = $orderID
Oh, of course, that produces the 3 components of the total order amount, so you would need to add
SUM(od_qty*pd_price)
vat
od_shipping_cost
to get the total order amount. That could easily be done in the PHP code, outside the database.

I am surprised by the requirement to state the amount in the lowest unit of currency, but it appears to me that you are doing exactly what they asked, by multiplying by 100. If it is being rejected, have you checked to see that you are actually sending them what you think you are? In case you have verified this, I guess it's time to contact xxxxx.

I would not store the vat and total amount in the table_order, since it can be calculated whenever it is needed, but I know there are others who would disagree with me. Actually, it will not cause a problem if you do, unless you go back later and modify one of the other fields without modifying the dependent fields. That is the reason that it is discouraged, and is in violation of third normal form.

I hope this is of some help to you.
ashebrian
Forum Contributor
Posts: 103
Joined: Sat Feb 02, 2008 8:01 pm

Re: SQL problem with adding the shipping value to vat and price

Post by ashebrian »

Hi,

Have a problem as my vat is not being displayed at all. it seems that i can get th value from the db.

Code: Select all

<?php
require_once 'config.php';
include('../include/session2.php');
 
/*********************************************************
*                 CHECKOUT FUNCTIONS 
*********************************************************/
function saveOrder()
{
    $orderId       = 0;
    $shippingCost  = 10;
    $requiredField = array('hidShippingFirstName', 'hidShippingLastName', 'hidShippingAddress1', 'hidShippingCity', 'hidShippingPostalCode',
                           'hidPaymentFirstName', 'hidPaymentLastName', 'hidPaymentAddress1', 'hidPaymentCity', 'hidPaymentPostalCode');
                           
    if (checkRequiredPost($requiredField)) {
        extract($_POST);
        
        // make sure the first character in the 
        // customer and city name are properly upper cased
        $hidShippingFirstName = ucwords($hidShippingFirstName);
        $hidShippingLastName  = ucwords($hidShippingLastName);
        $hidPaymentFirstName  = ucwords($hidPaymentFirstName);
        $hidPaymentLastName   = ucwords($hidPaymentLastName);
        $hidShippingCity      = ucwords($hidShippingCity);
        $hidPaymentCity       = ucwords($hidPaymentCity);
                
        $cartContent = getCartContent();
        $numItem     = count($cartContent);
        
        // save order & get order id
        $sql = "INSERT INTO tbl_order(od_date, member_username, od_last_update, od_shipping_first_name, od_shipping_last_name, od_shipping_address1, 
                                      od_shipping_address2, od_shipping_phone, od_shipping_state, od_shipping_city, od_shipping_postal_code, od_shipping_cost, od_vat, 
                                      od_payment_first_name, od_payment_last_name, od_payment_address1, od_payment_address2, 
                                      od_payment_phone, od_payment_state, od_payment_city, od_payment_postal_code)
                VALUES (NOW(), '$hidClient', NOW(), '$hidShippingFirstName', '$hidShippingLastName', '$hidShippingAddress1', 
                        '$hidShippingAddress2', '$hidShippingPhone', '$hidShippingState', '$hidShippingCity', '$hidShippingPostalCode', '$shippingCost', '$vat', 
                        '$hidPaymentFirstName', '$hidPaymentLastName', '$hidPaymentAddress1', 
                        '$hidPaymentAddress2', '$hidPaymentPhone', '$hidPaymentState', '$hidPaymentCity', '$hidPaymentPostalCode')";
        $result = dbQuery($sql);
        
        // get the order id
        $orderId = dbInsertId();
        
        if ($orderId) {
            // save order items
            for ($i = 0; $i < $numItem; $i++) {
                $sql = "INSERT INTO tbl_order_item(od_id, pd_id, od_qty)
                        VALUES ($orderId, {$cartContent[$i]['pd_id']}, {$cartContent[$i]['ct_qty']})";
                $result = dbQuery($sql);                    
            }
        
            
            // update product stock
            for ($i = 0; $i < $numItem; $i++) {
                $sql = "UPDATE tbl_product 
                        SET pd_qty = pd_qty - {$cartContent[$i]['ct_qty']}
                        WHERE pd_id = {$cartContent[$i]['pd_id']}";
                $result = dbQuery($sql);                    
            }
            
            
            // then remove the ordered items from cart
            for ($i = 0; $i < $numItem; $i++) {
                $sql = "DELETE FROM tbl_cart
                        WHERE ct_id = {$cartContent[$i]['ct_id']}";
                $result = dbQuery($sql);                    
            }                           
        }                   
    }
    
    return $orderId;
}
 
/*
    Get order total amount ( total purchase + shipping cost + vat)
*/
function getOrderAmount($orderId)
{
    $orderAmount = 0;
    
    $sql = "SELECT SUM(pd_price * od_qty)
            FROM tbl_order_item oi, tbl_product p
            WHERE oi.pd_id = p.pd_id and oi.od_id = $orderId
            
            UNION
            
            SELECT od_shipping_cost 
            FROM tbl_order
            WHERE od_id = $orderId
            
            UNION
            
            SELECT SUM(od_vat / 100)
            FROM tbl_order
            WHERE od_id = $orderId";
    $result = dbQuery($sql);
 
    if (dbNumRows($result) == 3) {
        $row = dbFetchRow($result);
        $totalPurchase = $row[0];
        
        $row = dbFetchRow($result);
        $shippingCost = $row[0];
        
        $row = dbFetchRow($result);
        $vat = $row[0];
        
        
        $orderAmount = ($totalPurchase + $shippingCost + ($vat * $totalPurchase)) * 100;
    }   
    
    return $orderAmount;    
}
?>
I've tried to set $vat = 21; under $shippingCost = 10; but wouldn't accept it. Can you please advise
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: SQL problem with adding the shipping value to vat and price

Post by califdon »

You need to analyze your script logically. When you describe your problem, make a clear distinction between whether something isn't being stored or isn't being displayed. When you say that something "isn't accepted", what do you mean? Are you receiving an error message? If so, what does it say? Without knowing these things, we can't really help you.

To get specific indicators of where something is going wrong, insert temporary "debugging" lines in your script to print out on the computer screen the value of key variables at critical points in the script. That way you will be able to see where a variable has the wrong (or no) value.

For example, I'd recommend that you insert a temporary line (you'll remove it once you've solved the problem) like:

Code: Select all

echo "VAT= $vat";  //  D E B U G
right after your extract($_POST); line. If the value isn't correct, now you know that the problem is occurring earlier, because $vat has the wrong (or no) value. Then you can return and explain your problem in a way that lets us try to help you.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: SQL problem with adding the shipping value to vat and price

Post by califdon »

Oh, and before I forget, it will help if you will use code tags that look like this:

Code: Select all

instead of just

Code: Select all

.
ashebrian
Forum Contributor
Posts: 103
Joined: Sat Feb 02, 2008 8:01 pm

Re: SQL problem with adding the shipping value to vat and price

Post by ashebrian »

Thanks for the advice on the code=php tags. I was wondering how they did that on this forum.

The problem for me is that when i checked my db it seems like the vat is being stored but then again that might just be coz in the admin side of my site i've assigned vat to 21.00 as in 21%.

However, i used the code below after the :

Code: Select all

 
extract($_POST);
echo "VAT= $vat";
which only displays:

Code: Select all

VAT=

so i added $vat = 21; which corrected my problem. But only for it being invalid amount on the 3rd parties side.

so i added:

Code: Select all

 
echo "VAT= $vat";
leaving in the code $vat = 21;

Code: Select all

$orderAmount = ($totalPurchase + (($vat/100) * $totalPurchase) + $shippingCost) * 100;
        echo "VAT= $vat";
    }   
    echo "VAT= $vat";
    return $orderAmount;
Which means that it has values of 21 at the extract($_POST); and values of 21.00 at the other 2.

If i get rid of the $vat = 21; no value is displayed just 0 at the .extract($_POST); and values of 0.00 at the other 2. I just find that weird when the database is displaying 21 in the column od_vat.

I changed the value of my vat in my db to:

Code: Select all

`od_vat` int(2) default '0',
and kicked out the decimal point

Code: Select all

`od_vat` decimal(5,2) default '0.00',
But the same values are being displayed 0, 0.00, 0.00 respectively.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: SQL problem with adding the shipping value to vat and price

Post by califdon »

Okay, I'm having a hard time following all the things you're describing, since I don't know the details of your application, but let me offer you some general suggestions:

When you're having trouble with something like this, avoid changing a lot of things at once, like changing the data type of the vat field. That distracts your attention from the problem and makes it very hard to figure out what's going on. Focus on just one thing and find out why it's going wrong. In this case, don't hide the problem by assigning a fixed value (21), that makes it impossible to tell why it's wrong. Where will you normally get this value? Start from there and put in your debugging lines to determine where it's not getting through.

The reason I suggested placing that debugging line right after your extract($_POST) line is precisely to show you that there is no value being assigned. So then the question is, Why not? The 2 most likely reasons are either that there is no vat variable in the $_POST data or that the vat variable in the $_POST data is not getting set to any value. With that knowledge, you can go back to the code that constructs the $_POST data (your Form) and find out why. Since I haven't seen your Form code, I can't venture a guess as to what's wrong. Where are you introducing the value for vat? It seems that you are going to store a percentage in a table somewhere and then apply to orders. All orders? Same vat? These are things that you will have to either tell us or figure out for yourself. If it's as simple as retrieving the vat percentage from some table in the database, are you then putting that into a Form element (<input>)? That's the only way it's going to get into $_POST data. $_POST data is merely all the data in the Form <input> elements, nothing more, nothing less. The other way you could get the value is simply query the database at the time you are processing the order. These are application design decisions that I can't help you with.
ashebrian
Forum Contributor
Posts: 103
Joined: Sat Feb 02, 2008 8:01 pm

Re: SQL problem with adding the shipping value to vat and price

Post by ashebrian »

I've added the $_POST to my forms and it's posted to my

Code: Select all

saveOrder()
function without a bother. using:

Code: Select all

$vat = $_POST['hidVat'];
But i'm using the code:

Code: Select all

echo "VAT= $vat";
Near every line and found that the VAT is not POSTED into the following code in:

Code: Select all

function getOrderAmount($orderId)
at the lines:

Code: Select all

if (dbNumRows($result) == 3) {
        $row = dbFetchRow($result);
        $totalPurchase = $row[0];
        
        $row = dbFetchRow($result);
        $vat = $row[0];
        echo "VAT= $vat";
        
        $row = dbFetchRow($result);
        $shippingCost = $row[0];
        
        $orderAmount = ($totalPurchase + (($vat / 100) * $totalPurchase) + $shippingCost) * 100;
        echo "VAT= $vat";
    }   
    return $orderAmount;    
}
I've tried numerous options and could not find the solution.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: SQL problem with adding the shipping value to vat and price

Post by califdon »

Maybe it's just me, but you've totally lost me. I thought we were talking about retrieving the VAT value from your form, but you are showing me something about fetching 3 rows from a database. I don't make the connection.

The only way I know to try to help you is to get back to the basics of what you are trying to do. You have an order form on which the user can enter several items, each for a different product and a qty. You want to store these records first as a shopping cart, then collect them and add shipping cost and perhaps VAT, depending on total order amount, then store the records, including an order record, to a different table, including the shipping cost and VAT. Is that correct?

So you have to determine which parts are working and which parts are not. I have the impression that everything is being stored correctly to the shopping cart table, yes? I presume that does NOT include shipping cost and VAT, but I don't think you have ever showed us what these two tables look like, so I'm only able to guess. Now, if that is correct, the next thing to determine is where you want to calculate VAT and add shipping cost; presumably when you are ready to write the total order. So where is the VAT going to come from? Somehow it doesn't seem appropriate to be coming from your form--your customer shouldn't be involved in this step. Does it need to come from a table, such as relating VAT to levels of purchase? If not, why wouldn't you just code the percentage in your script? I understand that it needs to be stored in the final order, but I don't see any need to be passing it in the $_POST variable array, nor indeed why ANY of the data for the final order should be coming from a form. Shouldn't it just be taking the data that's already stored in the shopping cart and moving it into the final order records?? That's why I'm very confused about your project.\

Edit: Oh, maybe you are displaying the final order to your customer for verification! That would make sense. So you will need to display the VAT calculation at that time, in the form. Is that what you are doing?

I think that if I am going to be able to offer you more help, I will need to see an outline of how you're trying to process your entire application. I clearly don't understand it.

If someone else is following this thread and has a better picture of what this OP is trying to do, by all means, join in!
Post Reply