Ideas?

[gufmn]

So I'm wroking on processing work orders from a custom built cart through authorize.net.

I'm not sure of the best way to handle things so I thought I'd ask.

As it stands, after the customer has created an account and entered all credit card/shipping info, I have functions set up do do the following:

Make sure the user is logged in and there are items in the cart to process.
If so, process credit card.
Generate a new work order in the "orders" table.
Add items purchased to the "items" table
Clear all entries from cart (assuming payment is authorized).
If card authorization fails, clear work order and leave cart in tact.

The problem I'm having is if I wait to create the work order until after the card is approved, there is a small chance of something going wrong and payment going through but no work order generated and no record of what the user wanted to purchase.
On the other hand, since the work order numbers are being generated by an auto-incrementing field, if the transaction is terminated and I have to remove the work order from the table, the work order numbers will have gaps in them which can lead to confusion while filling orders.

I've thought about using a temp table to store orders while authorizing payment then moving that info to the real orders table, but I'm wondering if someone has a better solution. I figure I'm not the first one to run in to this situation.

Thanks

[halojoy]

The problem I'm having is
if I wait to create the work order until after the card is approved,
there is a small chance of something going wrong and
payment going through but no work order generated and
no record of what the user wanted to purchase.
On the other hand,
since the work order numbers are being generated by an auto-incrementing field,
if the transaction is terminated and I have to remove the work order from the table,
the work order numbers will have gaps in them
which can lead to confusion while filling orders.

I've thought about
using a temp table to store orders
while authorizing payment
then moving that info to the real orders table,
but I'm wondering
if someone has a better solution.

Yes, you are right
This is the classical
- gaps in the numbers of ID in a table
[right]which happens when we delete and create new records
sure most anyone of us has run into this
at least we who has been doing own scripting for some time[/right]
approach to deal with this are several different

.... you mentioned temporary list your self ....

advice:
publish a php page of yours ( or a part of a page )
that contains this very issue
give us some a bit more practical ( not abstract )
to discuss around
[right]you can post your php discussion example
here
or start a new thread in Coding help forum[/right]
I promise you will get good suggestions
within an hour or two

🙂

halojoy

[gufmn]

I purposely did not include any code as this is more a conceptual question than a coding question. But, if you think it will help, here's the code:

session_start();
include('db.php');

function countCart() {
	$query = "select count(*) from cart where cookieId = '".GetCartId()."'";
	$result = mysql_query($query) or die("Error counting items in cart: ".mysql_error());
	$count = mysql_fetch_row($result);
	if($count[0] >= 1){
		return true;
	}
	else{
		return false;
	}
}

if($_SESSION['user_logged_in'] != "true"){
	header("Location: signin.php");
}
else if(countCart() != true){
	header("Location: store.php");
} 
else{
	include('site_tools/dopayment.php');
}

and here's dopayment.php:

function generateOrder(){
	$query = "insert into orders(orderID, shippingAddress1, shippingAddress2, contactNumber, dispatchDate, orderCost, orderTax, 
	shippingCost, orderTotalCost, orderDate, orders_clientId) values(0, '".$_SESSION['shipAddress1']."', '".$_SESSION['shipAddress2']."', '".$_SESSION['clientPhone']."', 
	'today', '50', '50', '50', '50', 'today', '".$_SESSION['clientId']."')";
	if(mysql_query($query)){
		$_SESSION['orderId'] = mysql_insert_id();
		return true;
	else {
		return false;
	}
}

function createJobs(){
	$getQuery = "select * from cart where cookieId = '".GetCartId()."'";
	$result = mysql_query($getQuery);
	while($row = mysql_fetch_array($result)){
		$moveQuery = "
		insert into frameJobs(jobId, userImage, userThumb, printSize, mouldingName, mouldingNumber, topMatName, topMatNumber, bottomMatName, bottomMatNumber, itemCost, frameJobs_orderId, jobQuantity) 
		values('0', '".$row['userimage']."', '".$row['thumbImage']."', '".$row['size']."', '". $row['moulding_name'] . "', 
		'".$row['mldg_number']."', '".$row['t_mat_value']."', '".$row['t_mat_number']."', '".$row['b_mat_value']."', 
		'".$row['b_mat_number']."', '".$row['price']."', '".$_SESSION['orderId']."',  '" . $row['qty'] . "')";
		mysql_query($moveQuery) or die("Error creating jobs: " . mysql_error());
	}
	return true;
}

function clearCart(){
	$query = "delete from cart where cookieId = '" . GetCartId() . "'";
	mysql_query($query) or die("Error clearing cart: ".mysql_error());
}

function clearOrder(){
	$query = "delete from orders where orderId = '" . $_SESSION['orderId'] . "'";
	$query2 = "delete from frameJobs where frameJobs_orderId = '" . $_SESSION['orderId'] . "'";
	mysql_query($query);
	mysql_query($query2);
}

function processCard(){
return true;
}

if(processCard() && generateOrder() && createJobs()){
	$_SESSION['paymentStatus'] = "Card authorized";
		clearCart();
		include('site_tools/send_confirm.php');
		mailInvoice($_SESSION['clientId'], $_SESSION['orderId']);
}
else {
	clearOrder();
	$_SESSION['paymentStatus'] = "Card not authorized Jerk";
}

[halojoy]

I purposely did not include any code as
this is more a conceptual question than a coding question.

Purely conceptually
of course:

What is the improvement
1. - you would like

compared to
2. - what is now done by your code

As I undestood you
your script works well, except for some situation
where is a loophole and some not wanted things can happen.

you want to plug this loophole
so
not have to worry about this, no more
if possible

/halojoy

[gufmn]

halojoy wrote:

Purely conceptually
of course:

What is the improvement
1. - you would like

Addressed in original post.

halojoy wrote:

compared to
2. - what is now done by your code

Addressed in original post and the code is right there.

halojoy wrote:

As I undestood you
your script works well, except for some situation
where is a loophole and some not wanted things can happen.

you want to plug this loophole
so
not have to worry about this, no more
if possible

Addressed in original post.