Hey Everyone,
I'm currently creating a simple shopping cart with interaction to PayPal. I've set up a database with a table named orders. I'm using the PayPal sandbox and what I'm looking to do is to post the order details into my database on payment completion. I have a script for this but for some reason my Insert statement doesn't seem to want to work. I've been working on this for the last few hours so it could just be me missing something blatantly obvious. Here is my code below, any help is greatly appreciated.
Thanks
paypal.php (PayPal IPN functionality)
<?php
include('db_fns.php');
$paypal_email = "orders_1301416231_biz@gmail.com";
$paypal_currency = 'GBP';
$shipping = 10.00;
function no_paypal_trans_id($trans_id)
{
$connection = db_connect();
$query = sprintf("SELECT id from orders WHERE paypal_trans_id = '%s'",
mysql_real_escape_string($trans_id));
$result = mysql_query($query);
$num_results = mysql_num_rows($result);
if($num_results == 0)
{
return true;
}
return false;
}
function payment_amount_correct($shipping, $params)
{
$amount = 0.00;
for ($i=1; $i <= $params['num_cart_items']; $i++)
{
$query = sprintf("SELECT price from products where id='%s'",
mysql_real_escape_string($params["item_number{$i}"]));
$result = mysql_query($query);
if($result)
{
$item_price = mysql_result($result, 0, 'price');
$amount += $item_price * $params["quantity{$i}"];
}
}
if(($amount + $shipping) == $params['mc_gross'])
{
return true;
}
else
{
return false;
}
}
function create_order($params)
{
db_connect();
$query = sprintf("INSERT INTO orders SET
orders.firstname = '%s',
orders.lastname = '%s',
orders.email = '%s',
orders.country = '%s',
orders.address = '%s',
orders.city = '%s',
orders.zip_code = '%s',
orders.state = '%s',
orders.status = '%s',
orders.amount = '%s',
orders.paypal_trans_id = '%s',
created_at = NOW()",
mysql_real_escape_string($params['first_name']),
mysql_real_escape_string($params['last_name']),
mysql_real_escape_string($params['payer_email']),
mysql_real_escape_string($params['address_country']),
mysql_real_escape_string($params['address_street']),
mysql_real_escape_string($params['address_city']),
mysql_real_escape_string($params['address_zip']),
mysql_real_escape_string($params['address_state']),
mysql_real_escape_string($params['payment_status']),
mysql_real_escape_string($params['mc_gross']),
mysql_real_escape_string($params['txn_id'])
);
$result = mysql_query($query);
if(!$result)
{
return false;
}
$order_id = mysql_insert_id();
for ($i=1; $i <= $params['num_cart_items'] ; $i++)
{
$product = find_product($params["item_number{$i}"]);
$query = sprintf("INSERT INTO items SET
order_id = '%s',
product_id = '%s',
title = '%s',
price = '%s',
qty = '%s'",
mysql_real_escape_string($order_id),
mysql_real_escape_string($product['id']),
mysql_real_escape_string($product['title']),
mysql_real_escape_string($product['price']),
mysql_real_escape_string($params["quantity{$i}"])
);
$result = mysql_query($query);
if(!$result)
{
return false;
}
}
return true;
}
// read the post from PayPal system and add 'cmd'
$req = 'cmd=_notify-validate';
foreach ($_POST as $key => $value) {
$value = urlencode(stripslashes($value));
$req .= "&$key=$value";
}
// post back to PayPal system to validate
$header .= "POST /cgi-bin/webscr HTTP/1.0\r\n";
$header .= "Content-Type: application/x-www-form-urlencoded\r\n";
$header .= "Content-Length: " . strlen($req) . "\r\n\r\n";
$fp = fsockopen ('www.sandbox.paypal.com', 80, $errno, $errstr, 30);
// assign posted variables to local variables
$item_name = $_POST['item_name'];
$item_number = $_POST['item_number'];
$payment_status = $_POST['payment_status'];
$payment_amount = $_POST['mc_gross'];
$payment_currency = $_POST['mc_currency'];
$txn_id = $_POST['txn_id'];
$receiver_email = $_POST['receiver_email'];
$payer_email = $_POST['payer_email'];
if (!$fp) {
// HTTP ERROR
} else {
fputs ($fp, $header . $req);
while (!feof($fp)) {
$res = fgets ($fp, 1024);
if (strcmp ($res, "VERIFIED") == 0) {
if ($_POST['payment_status'] == 'Completed'
&& no_paypal_trans_id($_POST['txn_id'])
&& $paypal_email == $_POST['receiver_email']
&& $paypal_currency == $_POST['mc_currency']
&& payment_amount_correct($shipping, $_POST)
)
{
// process payment
create_order($_POST);
}
}
else if (strcmp ($res, "INVALID") == 0) {
// log for manual investigation
}
}
fclose ($fp);
}
?>checkout.php
<h2>Checkout</h2>
<?php
if($_SESSION['cart'])
{
?>
<form action="index.php?view=update_cart" method="post">
<table id="items">
<thead>
<tr>
<th>Item</th>
<th>Item Price</th>
<th>Qty</th>
<th>Subtotal</th>
</tr>
</thead>
<tbody>
<?php foreach($_SESSION['cart'] as $id => $qty):
$product = find_product($id);
?>
<tr>
<td><?php echo $product['title']; ?></td>
<td>£<?php echo number_format($product['price'],2); ?></td>
<td><input type="text" size="2" name="<?php echo $id ?>" maxlength="2" value="<?php echo $qty; ?>" /></td>
<td>£<?php echo number_format($product['price'] * $qty, 2); ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<p><input type="submit" name="update" value="update" /></p>
</form>
<form action="https://www.sandbox.paypal.com/cgi-bin/webscr" method="post">
<input type="hidden" name="cmd" value="_cart">
<input type="hidden" name="upload" value="1">
<input type="hidden" name="business" value="orders_1301416231_biz@gmail.com">
<?php
$i = 1;
foreach($_SESSION['cart'] as $id => $qty):
$product = find_product($id);
?>
<input type="hidden" name="item_name_<?php echo $i; ?>" value="<?php echo $product['title']; ?>">
<input type="hidden" name="item_number_<?php echo $i; ?>" value="<?php echo $product['id']; ?>">
<input type="hidden" name="amount_<?php echo $i; ?>" value="<?php echo $product['price']; ?>">
<input type="hidden" name="quantity_<?php echo $i; ?>" value="<?php echo $qty; ?>">
<?php
$i++;
endforeach;
?>
<input type="hidden" name="currency_code" value="GBP">
<input type="hidden" name="lc" value="UK">
<input type="hidden" name="rm" value="2">
<input type="hidden" name="shipping_1" value="<?php echo $shippingl?>">
<input type="hidden" name="return" value="http://www.glammed.co.uk/Shopping/index.php?view=thankyou">
<input type="hidden" name="cancel_return" value="http://www.glammed.co.uk/Shopping/">
<input type="hidden" name="notify_url" value="http://www.glammed.co.uk/Shopping/paypal.php">
<input type="submit" name="pay now" value="pay" />
</form>
<?php
}
else
{
echo '<p>your cart is empty... <a href="index.php">continue shopping</a></p>';
}
?>