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> </p>
<p align="center"><span class="header-txt"><b>Processing
Transaction . . . </b></span></p>
<p> </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.