SQL problem with adding the shipping value to vat and price
Moderator: General Moderators
Re: SQL problem with adding the shipping value to vat and price
i'm using this.....www.phpwebcommerce.com/php-mysql-shoppi ... torial.php. And am not using paypal
I have to go now and will be back with more info and be more clearer
I have to go now and will be back with more info and be more clearer
Re: SQL problem with adding the shipping value to vat and price
I am retrieving the vat value from my form:
Would make a difference if the form i'm retrieving it from displays the information to the customer before they click the confirm button and bring them to the third party's website.
My tables are as follows:
This is the shopping cart i'm using. I'm trying to not use Paypal but a different 3rd party.
http://www.phpwebcommerce.com/php-mysql ... torial.php
Code: Select all
$vat = $_POST['hidVat'];Would make a difference if the form i'm retrieving it from displays the information to the customer before they click the confirm button and bring them to the third party's website.
The above is correct.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?
My tables are as follows:
Code: Select all
CREATE TABLE `tbl_order` (
`od_id` int(10) unsigned NOT NULL auto_increment,
`member_username` varchar(30),
`od_date` datetime default NULL,
`od_last_update` datetime NOT NULL default '0000-00-00 00:00:00',
`od_status` enum('New', 'Paid', 'Shipped','Completed','Cancelled') NOT NULL default 'New',
`od_memo` varchar(255) NOT NULL default '',
`od_shipping_first_name` varchar(50) NOT NULL default '',
`od_shipping_last_name` varchar(50) NOT NULL default '',
`od_shipping_address1` varchar(100) NOT NULL default '',
`od_shipping_address2` varchar(100) NOT NULL default '',
`od_shipping_phone` varchar(32) NOT NULL default '',
`od_shipping_city` varchar(100) NOT NULL default '',
`od_shipping_state` varchar(32) NOT NULL default '',
`od_shipping_postal_code` varchar(10) NOT NULL default '',
`od_shipping_cost` decimal(5,2) default '0.00',
`od_vat` int(2) NOT NULL default '21',
`od_payment_first_name` varchar(50) NOT NULL default '',
`od_payment_last_name` varchar(50) NOT NULL default '',
`od_payment_address1` varchar(100) NOT NULL default '',
`od_payment_address2` varchar(100) NOT NULL default '',
`od_payment_phone` varchar(32) NOT NULL default '',
`od_payment_city` varchar(100) NOT NULL default '',
`od_payment_state` varchar(32) NOT NULL default '',
`od_payment_postal_code` varchar(10) NOT NULL default '',
PRIMARY KEY (`od_id`,`member_username`)
) TYPE=MyISAM AUTO_INCREMENT=1310 ;
CREATE TABLE `tbl_order_item` (
`od_id` int(10) unsigned NOT NULL default '0',
`pd_id` int(10) unsigned NOT NULL default '0',
`od_qty` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`od_id`,`pd_id`)
) TYPE=MyISAM;
CREATE TABLE `tbl_cart` (
`ct_id` int(10) unsigned NOT NULL auto_increment,
`pd_id` int(10) unsigned NOT NULL default '0',
`ct_qty` mediumint(8) unsigned NOT NULL default '1',
`ct_session_id` char(32) NOT NULL default '',
`ct_date` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`ct_id`),
KEY `pd_id` (`pd_id`),
KEY `ct_session_id` (`ct_session_id`)
) TYPE=MyISAM AUTO_INCREMENT=58 ;I've tried to just add $vat = 21; but the 3rd parties does not accept it for some reason. But if i used a $_POST it'll accept it. Quite weird after i tested it. Not sure how that is. The shpping cart is 3 php scripts away from where the vat and shipping cost needs to be sorted in checout-functions.php so i don't think you can take it from the shopping cart. i may be wrong.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.
thats correct, thats what i'm doing.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?
This is the shopping cart i'm using. I'm trying to not use Paypal but a different 3rd party.
http://www.phpwebcommerce.com/php-mysql ... torial.php
Re: SQL problem with adding the shipping value to vat and price
I am really sorry, and I don't like just blowing you off, but I'm just not able to analyze an entire system that is made up of subsystems of which I have no knowledge and snippets of code that depend on something that's not being shown. I hope that someone else who is able to understand what you are doing and is familiar with these external systems will come along and offer you some help. I'm sorry, but I just can't help you. Good luck.
Re: SQL problem with adding the shipping value to vat and price
Thats no problem. I understand. The thing is that i've now actually pin pointed the actual problem. My problem lies with the VAT price that ends up having a decimal point at the end of it. E.g 21/100 * €13.72 = €2.8812.........to get this into cents it'll be * 100 to give you 288.12.
What i now need is a simple code to get rid of the .12 decimal points. Something similar to %f%s or (float)(string).
I've now used hidden inputs in my form that displays everything to my customers for the total price, shipping cost and vat.
and:
This will then POST the amounts to the next script that only adds the orders to the db and gets the totalAmount which is then sent on to the 3rd party. The totalAmount function code is as follows:
I'm only echoing them to see the correct values. It will be taken out once my problem is solved. As seen above the totalAmount is just for show. I can just use that instead of the others and call it $orderAmount. Will need the decimal points sorted tho. I think to get this sorted it might have to be in the form where the inputs edited. Please advise on the best and easiest solution to get rid of decimal points.
What i now need is a simple code to get rid of the .12 decimal points. Something similar to %f%s or (float)(string).
I've now used hidden inputs in my form that displays everything to my customers for the total price, shipping cost and vat.
Code: Select all
$numItem = count($cartContent);
$subTotal = 0;
$vat = 21;
$shipTotalHigh = 300;
for ($i = 0; $i < $numItem; $i++) {
extract($cartContent[$i]);
$subTotal += $pd_price * $ct_qty;Code: Select all
<input name="totalPriceItems" type="hidden" id="totalPriceItems" value="<?php echo displayAmount($subTotal * 100); ?>">
<input name="totalShipping" type="hidden" id="totalShipping" value="<?php if ($subTotal >= $shipTotalHigh){
echo displayAmount(0 * 100);
} else {
echo displayAmount($shopConfig['shippingCost'] * 100);
}?>">
<input name="totalVat" type="hidden" id="totalVat" value="<?php echo displayAmount((($vat / 100) * $subTotal) * 100); ?>">
<input name="totalTotal" type="hidden" id="totalTotal" value="<?php if ($subTotal >= $shipTotalHigh){
echo displayAmount(($subTotal + 0 + (($vat / 100) * $subTotal)) * 100);
} else {
echo displayAmount(($subTotal + $shopConfig['shippingCost'] + (($vat / 100) * $subTotal)) * 100);
}
?>">Code: Select all
function getOrderAmount($orderId)
{
$orderAmount = 0;
$totalPurchase = $_POST['totalPriceItems'];
$vat = $_POST['totalVat'];
$shipping = $_POST['totalShipping'];
$total = $_POST['totalTotal'];
echo "VAT= $vat";
echo "ship= $shipping";
echo "total= $totalPurchase";
echo "totalAmont= $total";
$orderAmount = $totalPurchase + $vat + $shipping;
return $orderAmount;
}
?>
Re: SQL problem with adding the shipping value to vat and price
Why divide by 100 and then multiply by 100?ashebrian wrote:My problem lies with the VAT price that ends up having a decimal point at the end of it. \
E.g 21/100 * €13.72 = €2.8812.........to get this into cents it'll be * 100 to give you 288.12.
What i now need is a simple code to get rid of the .12 decimal points. Something similar to %f%s or (float)(string).
Code: Select all
21 * 13.72 = 288.12Code: Select all
$cents = floor($calcvat);Re: SQL problem with adding the shipping value to vat and price
i changed it and had something very similar using floor. The amount is displayed correctly when i echo it out. but the 3rd party is saying Invalid characters.
when i use echo under neat my
It does not display the amount. Is this supposed to happen. The code is as follows:
Maybe my function is a mess and too empty
when i use echo under neat my
Code: Select all
return orderTotal;Code: Select all
function getOrderAmount($orderId)
{
$orderTotal = 0;
$orderTotal = $_POST['totalTotal'];
echo "orderAmont= $orderTotal";
return $orderTotal;
echo "orderAmont= $orderTotal";
}
?>Re: SQL problem with adding the shipping value to vat and price
Are they expecting a numeric or text? the floor() function returns an integer, according to the manual.ashebrian wrote:i changed it and had something very similar using floor. The amount is displayed correctly when i echo it out. but the 3rd party is saying Invalid characters.
When you issue a "return ..." statement, the function is complete, so it won't execute anything that follows. I take it the first echo statement does echo to your screen?when i use echo under neat myIt does not display the amount. Is this supposed to happen. The code is as follows:Code: Select all
return orderTotal;Maybe my function is a mess and too emptyCode: Select all
function getOrderAmount($orderId) { $orderTotal = 0; $orderTotal = $_POST['totalTotal']; echo "orderAmont= $orderTotal"; return $orderTotal; echo "orderAmont= $orderTotal"; } ?>
Re: SQL problem with adding the shipping value to vat and price
it echos the correct amt in cents.
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. JPY Yen) then contact xxxxxxxx. No decimal points are allowed.
Amount is Numeric (0-9).
May i ask about the $_post. the request file comes after this and show the echo amount. But as i used $_POST, will i have to use that for the request file too?
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. JPY Yen) then contact xxxxxxxx. No decimal points are allowed.
Amount is Numeric (0-9).
May i ask about the $_post. the request file comes after this and show the echo amount. But as i used $_POST, will i have to use that for the request file too?
Re: SQL problem with adding the shipping value to vat and price
I don't see any reason that it doesn't accept it, then.ashebrian wrote:it echos the correct amt in cents.
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. JPY Yen) then contact xxxxxxxx. No decimal points are allowed.
Amount is Numeric (0-9).
I don't understand your question at all.May i ask about the $_post. the request file comes after this and show the echo amount. But as i used $_POST, will i have to use that for the request file too?
Re: SQL problem with adding the shipping value to vat and price
I was thinking that the maybe the
Only posts the file once but not to request.php. But i'm wrong coz its an include file.
I've pin pointed the problem again. I have been getting my result as lets say 2,660. As you see the comma in that total, this means that its not in cents. So i've changed this.
My code now stands at:
with the code from my checkout-fuctions.php multiplying the $orderTotal by 100 as shown below:
However the floor is not working properly as it doesn't give me the total of 2660 but it gives me 2700. I've been working on this and have got another result as 2600 but no 2660 as needed. I understand where those figures are coming from but i'm unsure if floor can be used to get the 2660 value.
Code: Select all
$orderTotal = $_POST['totalTotal'];I've pin pointed the problem again. I have been getting my result as lets say 2,660. As you see the comma in that total, this means that its not in cents. So i've changed this.
My code now stands at:
Code: Select all
$numItem = count($cartContent);
$subTotal = 0;
$vat = 21;
$shipTotalHigh = 300;
for ($i = 0; $i < $numItem; $i++) {
extract($cartContent[$i]);
$subTotal += $pd_price * $ct_qty;
$vattaxing = ((21/100) * $subTotal);
$vattax = floor(($vattaxing) * 100 + .5) * .01;
$ashtotal = $vattax + $subTotal;
$ashtotalShip = $vattax + $subTotal + $shopConfig['shippingCost'];Code: Select all
<input name="totalTotal" type="hidden" id="totalTotal" value="<?php if ($subTotal >= $shipTotalHigh){
echo displayTotal($ashtotal);
} else {
echo displayTotal($ashtotalShip);
}
?>">Code: Select all
function getOrderAmount($orderId)
{
$orderTotal = $_POST['totalTotal'] * 100;
echo "orderTotal= $orderTotal";
return $orderTotal;
}
?>Re: SQL problem with adding the shipping value to vat and price
You should not guess at these things. Read the manual: http://us3.php.net/floor
Re: SQL problem with adding the shipping value to vat and price
Ok what i did was i'm able to get the amount. But it was displayed with a comma so i used
to get rid of this. However, the 3rd party still does not see that as cents and will not accept it. I think that they'll still read it as a comma.
Therefore, i think posting the prices over will not work. I'm going back to get the info from the db again. This time i'm trying to add the od_price_total to my tbl_order table. The only thing i've changed in my checkout-functions.php is from this code that used post and has the correct amount but wouldn't send due to the str_replace:
and replaced it with this:
however, this code i replace it with only returns 2 from the db. ummmmmmm.....My table is as follows:
I'm not sure if bigint is a problem for od_price_total.
Code: Select all
str_replaceTherefore, i think posting the prices over will not work. I'm going back to get the info from the db again. This time i'm trying to add the od_price_total to my tbl_order table. The only thing i've changed in my checkout-functions.php is from this code that used post and has the correct amount but wouldn't send due to the str_replace:
Code: Select all
$orderTotal = str_replace(',', '', $_POST['totalTotal']);
echo "orderTotal= $orderTotal";
return $orderTotal;Code: Select all
$orderTotal = 0;
$totalTotal = $_POST['totalTotal'];
$sql = "SELECT SUM('$totalTotal')
FROM tbl_order
WHERE od_id = $orderId";
$result = dbQuery($sql);
if (dbNumRows($result) == 1) {
$row = dbFetchRow($result);
$totalPurchase = $row[0];
$orderTotal = $totalPurchase;
echo "orderTotal= $orderTotal";
}
return $orderTotal;however, this code i replace it with only returns 2 from the db. ummmmmmm.....My table is as follows:
Code: Select all
CREATE TABLE `tbl_order` (
`od_id` int(10) unsigned NOT NULL auto_increment,
`member_username` varchar(30),
`od_date` datetime default NULL,
`od_last_update` datetime NOT NULL default '0000-00-00 00:00:00',
`od_status` enum('New', 'Paid', 'Shipped','Completed','Cancelled') NOT NULL default 'New',
`od_memo` varchar(255) NOT NULL default '',
`od_shipping_first_name` varchar(50) NOT NULL default '',
`od_shipping_last_name` varchar(50) NOT NULL default '',
`od_shipping_address1` varchar(100) NOT NULL default '',
`od_shipping_address2` varchar(100) NOT NULL default '',
`od_shipping_phone` varchar(32) NOT NULL default '',
`od_shipping_city` varchar(100) NOT NULL default '',
`od_shipping_state` varchar(32) NOT NULL default '',
`od_shipping_postal_code` varchar(10) NOT NULL default '',
`od_shipping_cost` decimal(5,2) default '0.00',
`od_vat` int(2) NOT NULL default '21',
`od_price_total` bigint NOT NULL default '',
`od_payment_first_name` varchar(50) NOT NULL default '',
`od_payment_last_name` varchar(50) NOT NULL default '',
`od_payment_address1` varchar(100) NOT NULL default '',
`od_payment_address2` varchar(100) NOT NULL default '',
`od_payment_phone` varchar(32) NOT NULL default '',
`od_payment_city` varchar(100) NOT NULL default '',
`od_payment_state` varchar(32) NOT NULL default '',
`od_payment_postal_code` varchar(10) NOT NULL default '',
PRIMARY KEY (`od_id`,`member_username`)
) TYPE=MyISAM AUTO_INCREMENT=1610 ;