Tables not recording order_id properly

GanodermaCoffee.com · November 19, 2006

Ok...I love OSCommerce, but oh my...one little character here or there an hair gets pulled out!

I hope that someone can help me with my issue:

I have added a couple contributions like order ip recorder and edit order, but I have compared to a clean osc install and not really anything that is alarming to me.

So here is the problem:

1) I go through the process of submitting an order.

2) I get the successful page, and I am happy!

3) I look at what would be the page of my customer in the order section, and their is no order, because the order_id is only recorded in the Order table in my database.

For some reason, it is not recording to any of the other tables like order_total, order_status_history, and others where it should be.

Can someone please give me some insite into why this is happening...I would be happy to paste some code in this if that is necessary.

My site is http://www.ganodermacoffee.com

PLEASE HELP ME!

November 19, 2006

The problem is usually either in checkout_process.php or your payment module (eg. Paypal IPN)

Sonia

GanodermaCoffee.com · November 19, 2006

Here is what my checkout_process.php file looks like currently, and it does get the information into the orders table but all of the other tables it does not update properly leaving out the order_id.

Thanks for everyones help :)

<?php 
/* 
 $Id: checkout_process.php,v 1.128 2003/05/28 18:00:29 hpdl Exp $ 

 osCommerce, Open Source E-Commerce Solutions 
 http://www.oscommerce.com 

 Copyright (c) 2003 osCommerce 

 Released under the GNU General Public License 
*/ 

 include('includes/application_top.php'); 

 $ip = $HTTP_SERVER_VARS["REMOTE_ADDR"]; 
 $client = gethostbyaddr($HTTP_SERVER_VARS["REMOTE_ADDR"]); 
 $str = preg_split("/\./", $client); 
 $i = count($str); 
 $x = $i - 1; 
 $n = $i - 2; 
 $isp = $str[$n] . "." . $str[$x]; 

// if the customer is not logged on, redirect them to the login page 
 if (!tep_session_is_registered('customer_id')) { 
$navigation->set_snapshot(array('mode' => 'SSL', 'page' => FILENAME_CHECKOUT_PAYMENT)); 
tep_redirect(tep_href_link(FILENAME_LOGIN, '', 'SSL')); 
 } 

 if (!tep_session_is_registered('sendto')) { 
tep_redirect(tep_href_link(FILENAME_CHECKOUT_PAYMENT, '', 'SSL')); 
 } 

 if ( (tep_not_null(MODULE_PAYMENT_INSTALLED)) && (!tep_session_is_registered('payment')) ) { 
tep_redirect(tep_href_link(FILENAME_CHECKOUT_PAYMENT, '', 'SSL')); 
} 

// avoid hack attempts during the checkout procedure by checking the internal cartID 
 if (isset($cart->cartID) && tep_session_is_registered('cartID')) { 
if ($cart->cartID != $cartID) { 
  tep_redirect(tep_href_link(FILENAME_CHECKOUT_SHIPPING, '', 'SSL')); 
} 
 } 

 include(DIR_WS_LANGUAGES . $language . '/' . FILENAME_CHECKOUT_PROCESS); 

// load selected payment module 
 require(DIR_WS_CLASSES . 'payment.php'); 
 $payment_modules = new payment($payment); 

// load the selected shipping module 
 require(DIR_WS_CLASSES . 'shipping.php'); 
 $shipping_modules = new shipping($shipping); 

 require(DIR_WS_CLASSES . 'order.php'); 
 $order = new order; 

// load the before_process function from the payment modules 
 $payment_modules->before_process(); 

 require(DIR_WS_CLASSES . 'order_total.php'); 
 $order_total_modules = new order_total; 

 $order_totals = $order_total_modules->process(); 

 $sql_data_array = array('customers_id' => $customer_id, 
					  'customers_name' => $order->customer['firstname'] . ' ' . $order->customer['lastname'], 
					  'customers_company' => $order->customer['company'], 
					  'customers_street_address' => $order->customer['street_address'], 
					  'customers_suburb' => $order->customer['suburb'], 
					  'customers_city' => $order->customer['city'], 
					  'customers_postcode' => $order->customer['postcode'],  
					  'customers_state' => $order->customer['state'],  
					  'customers_country' => $order->customer['country']['title'],  
					  'customers_telephone' => $order->customer['telephone'],  
					  'customers_email_address' => $order->customer['email_address'], 
					  'customers_address_format_id' => $order->customer['format_id'],  
					  'delivery_name' => $order->delivery['firstname'] . ' ' . $order->delivery['lastname'],  
					  'delivery_company' => $order->delivery['company'], 
					  'delivery_street_address' => $order->delivery['street_address'],  
					  'delivery_suburb' => $order->delivery['suburb'],  
					  'delivery_city' => $order->delivery['city'],  
					  'delivery_postcode' => $order->delivery['postcode'],  
					  'delivery_state' => $order->delivery['state'],  
					  'delivery_country' => $order->delivery['country']['title'],  
					  'delivery_address_format_id' => $order->delivery['format_id'],  
					  'billing_name' => $order->billing['firstname'] . ' ' . $order->billing['lastname'],  
					  'billing_company' => $order->billing['company'], 
					  'billing_street_address' => $order->billing['street_address'],  
					  'billing_suburb' => $order->billing['suburb'],  
					  'billing_city' => $order->billing['city'],  
					  'billing_postcode' => $order->billing['postcode'],  
					  'billing_state' => $order->billing['state'],  
					  'billing_country' => $order->billing['country']['title'],  
					  'billing_address_format_id' => $order->billing['format_id'],  
					  'payment_method' => $order->info['payment_method'],  
					  'cc_type' => $order->info['cc_type'],  
					  'cc_owner' => $order->info['cc_owner'],  
					  'cc_number' => $order->info['cc_number'],  
					  'cc_expires' => $order->info['cc_expires'],  
					  'date_purchased' => 'now()',  
					  'orders_status' => $order->info['order_status'],  
					  'currency' => $order->info['currency'],  
					  'currency_value' => $order->info['currency_value'], 
					  'ipaddy' => $ip, 
   'ipisp' => $isp); 
 tep_db_perform(TABLE_ORDERS, $sql_data_array); 
 $insert_id = tep_db_insert_id(); 
 for ($i=0, $n=sizeof($order_totals); $i<$n; $i++) { 
$sql_data_array = array('orders_id' => $insert_id, 
						'title' => $order_totals[$i]['title'], 
						'text' => $order_totals[$i]['text'], 
						'value' => $order_totals[$i]['value'],  
						'class' => $order_totals[$i]['code'],  
						'sort_order' => $order_totals[$i]['sort_order']); 
tep_db_perform(TABLE_ORDERS_TOTAL, $sql_data_array); 
 } 

 $customer_notification = (SEND_EMAILS == 'true') ? '1' : '0'; 
 $sql_data_array = array('orders_id' => $insert_id,  
					  'orders_status_id' => $order->info['order_status'],  
					  'date_added' => 'now()',  
					  'customer_notified' => $customer_notification, 
					  'comments' => $order->info['comments']); 
 tep_db_perform(TABLE_ORDERS_STATUS_HISTORY, $sql_data_array); 

// initialized for the email confirmation 
 $products_ordered = ''; 
 $subtotal = 0; 
 $total_tax = 0; 

 for ($i=0, $n=sizeof($order->products); $i<$n; $i++) { 
// Stock Update - Joao Correia 
if (STOCK_LIMITED == 'true') { 
  if (DOWNLOAD_ENABLED == 'true') { 
	$stock_query_raw = "SELECT products_quantity, pad.products_attributes_filename  
						FROM " . TABLE_PRODUCTS . " p 
						LEFT JOIN " . TABLE_PRODUCTS_ATTRIBUTES . " pa 
						 ON p.products_id=pa.products_id 
						LEFT JOIN " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad 
						 ON pa.products_attributes_id=pad.products_attributes_id 
						WHERE p.products_id = '" . tep_get_prid($order->products[$i]['id']) . "'"; 
// Will work with only one option for downloadable products 
// otherwise, we have to build the query dynamically with a loop 
	$products_attributes = $order->products[$i]['attributes']; 
	if (is_array($products_attributes)) { 
	  $stock_query_raw .= " AND pa.options_id = '" . $products_attributes[0]['option_id'] . "' AND pa.options_values_id = '" . $products_attributes[0]['value_id'] . "'"; 
	} 
	$stock_query = tep_db_query($stock_query_raw); 
  } else { 
	$stock_query = tep_db_query("select products_quantity from " . TABLE_PRODUCTS . " where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'"); 
  } 
  if (tep_db_num_rows($stock_query) > 0) { 
	$stock_values = tep_db_fetch_array($stock_query); 
// do not decrement quantities if products_attributes_filename exists 
	if ((DOWNLOAD_ENABLED != 'true') || (!$stock_values['products_attributes_filename'])) { 
	  $stock_left = $stock_values['products_quantity'] - $order->products[$i]['qty']; 
	} else { 
	  $stock_left = $stock_values['products_quantity']; 
	} 
	tep_db_query("update " . TABLE_PRODUCTS . " set products_quantity = '" . $stock_left . "' where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'"); 
	if ( ($stock_left < 1) && (STOCK_ALLOW_CHECKOUT == 'false') ) { 
	  tep_db_query("update " . TABLE_PRODUCTS . " set products_status = '0' where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'"); 
	} 
  } 
} 

// Update products_ordered (for bestsellers list) 
tep_db_query("update " . TABLE_PRODUCTS . " set products_ordered = products_ordered + " . sprintf('%d', $order->products[$i]['qty']) . " where products_id = '" . tep_get_prid($order->products[$i]['id']) . "'"); 

$sql_data_array = array('orders_id' => $insert_id,  
						'products_id' => tep_get_prid($order->products[$i]['id']),  
						'products_model' => $order->products[$i]['model'],  
						'products_name' => $order->products[$i]['name'],  
						'products_price' => $order->products[$i]['price'],  
						'final_price' => $order->products[$i]['final_price'],  
						'products_tax' => $order->products[$i]['tax'],  
						'products_quantity' => $order->products[$i]['qty']); 
tep_db_perform(TABLE_ORDERS_PRODUCTS, $sql_data_array); 
$order_products_id = tep_db_insert_id(); 

//------insert customer choosen option to order-------- 
$attributes_exist = '0'; 
$products_ordered_attributes = ''; 
if (isset($order->products[$i]['attributes'])) { 
  $attributes_exist = '1'; 
  for ($j=0, $n2=sizeof($order->products[$i]['attributes']); $j<$n2; $j++) { 
	if (DOWNLOAD_ENABLED == 'true') { 
	  $attributes_query = "select popt.products_options_name, poval.products_options_values_name, pa.options_values_price, pa.price_prefix, pad.products_attributes_maxdays, pad.products_attributes_maxcount , pad.products_attributes_filename  
						   from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_OPTIONS_VALUES . " poval, " . TABLE_PRODUCTS_ATTRIBUTES . " pa  
						   left join " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " pad 
							on pa.products_attributes_id=pad.products_attributes_id 
						   where pa.products_id = '" . $order->products[$i]['id'] . "'  
							and pa.options_id = '" . $order->products[$i]['attributes'][$j]['option_id'] . "'  
							and pa.options_id = popt.products_options_id  
							and pa.options_values_id = '" . $order->products[$i]['attributes'][$j]['value_id'] . "'  
							and pa.options_values_id = poval.products_options_values_id  
							and popt.language_id = '" . $languages_id . "'  
							and poval.language_id = '" . $languages_id . "'"; 
	  $attributes = tep_db_query($attributes_query); 
	} else { 
	  $attributes = tep_db_query("select popt.products_options_name, poval.products_options_values_name, pa.options_values_price, pa.price_prefix from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_OPTIONS_VALUES . " poval, " . TABLE_PRODUCTS_ATTRIBUTES . " pa where pa.products_id = '" . $order->products[$i]['id'] . "' and pa.options_id = '" . $order->products[$i]['attributes'][$j]['option_id'] . "' and pa.options_id = popt.products_options_id and pa.options_values_id = '" . $order->products[$i]['attributes'][$j]['value_id'] . "' and pa.options_values_id = poval.products_options_values_id and popt.language_id = '" . $languages_id . "' and poval.language_id = '" . $languages_id . "'"); 
	} 
	$attributes_values = tep_db_fetch_array($attributes); 

	$sql_data_array = array('orders_id' => $insert_id,  
							'orders_products_id' => $order_products_id,  
							'products_options' => $attributes_values['products_options_name'], 
							'products_options_values' => $attributes_values['products_options_values_name'],  
							'options_values_price' => $attributes_values['options_values_price'],  
							'price_prefix' => $attributes_values['price_prefix']); 
	tep_db_perform(TABLE_ORDERS_PRODUCTS_ATTRIBUTES, $sql_data_array); 

	if ((DOWNLOAD_ENABLED == 'true') && isset($attributes_values['products_attributes_filename']) && tep_not_null($attributes_values['products_attributes_filename'])) { 
	  $sql_data_array = array('orders_id' => $insert_id,  
							  'orders_products_id' => $order_products_id,  
							  'orders_products_filename' => $attributes_values['products_attributes_filename'],  
							  'download_maxdays' => $attributes_values['products_attributes_maxdays'],  
							  'download_count' => $attributes_values['products_attributes_maxcount']); 
	  tep_db_perform(TABLE_ORDERS_PRODUCTS_DOWNLOAD, $sql_data_array); 
	} 
	$products_ordered_attributes .= "\n\t" . $attributes_values['products_options_name'] . ' ' . $attributes_values['products_options_values_name']; 
  } 
} 
//------insert customer choosen option eof ---- 
$total_weight += ($order->products[$i]['qty'] * $order->products[$i]['weight']); 
$total_tax += tep_calculate_tax($total_products_price, $products_tax) * $order->products[$i]['qty']; 
$total_cost += $total_products_price; 

$products_ordered .= $order->products[$i]['qty'] . ' x ' . $order->products[$i]['name'] . ' (' . $order->products[$i]['model'] . ') = ' . $currencies->display_price($order->products[$i]['final_price'], $order->products[$i]['tax'], $order->products[$i]['qty']) . $products_ordered_attributes . "\n"; 
 } 

// lets start with the email confirmation 
 $email_order = STORE_NAME . "\n" .  
			 EMAIL_SEPARATOR . "\n" .  
			 EMAIL_TEXT_ORDER_NUMBER . ' ' . $insert_id . "\n" . 
			 EMAIL_TEXT_INVOICE_URL . ' ' . tep_href_link(FILENAME_ACCOUNT_HISTORY_INFO, 'order_id=' . $insert_id, 'SSL', false) . "\n" . 
			 EMAIL_TEXT_DATE_ORDERED . ' ' . strftime(DATE_FORMAT_LONG) . "\n\n"; 
 if ($order->info['comments']) { 
$email_order .= tep_db_output($order->info['comments']) . "\n\n"; 
 } 
 $email_order .= EMAIL_TEXT_PRODUCTS . "\n" .  
			  EMAIL_SEPARATOR . "\n" .  
			  $products_ordered .  
			  EMAIL_SEPARATOR . "\n"; 

 for ($i=0, $n=sizeof($order_totals); $i<$n; $i++) { 
$email_order .= strip_tags($order_totals[$i]['title']) . ' ' . strip_tags($order_totals[$i]['text']) . "\n"; 
 } 

 if ($order->content_type != 'virtual') { 
$email_order .= "\n" . EMAIL_TEXT_DELIVERY_ADDRESS . "\n" .  
				EMAIL_SEPARATOR . "\n" . 
				tep_address_label($customer_id, $sendto, 0, '', "\n") . "\n"; 
 } 

 $email_order .= "\n" . EMAIL_TEXT_BILLING_ADDRESS . "\n" . 
			  EMAIL_SEPARATOR . "\n" . 
			  tep_address_label($customer_id, $billto, 0, '', "\n") . "\n\n"; 
 if (is_object($$payment)) { 
$email_order .= EMAIL_TEXT_PAYMENT_METHOD . "\n" .  
				EMAIL_SEPARATOR . "\n"; 
$payment_class = $$payment; 
$email_order .= $payment_class->title . "\n\n"; 
if ($payment_class->email_footer) {  
  $email_order .= $payment_class->email_footer . "\n\n"; 
} 
 } 
 tep_mail($order->customer['firstname'] . ' ' . $order->customer['lastname'], $order->customer['email_address'], EMAIL_TEXT_SUBJECT, $email_order, STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS); 

// send emails to other people 
 if (SEND_EXTRA_ORDER_EMAILS_TO != '') { 
tep_mail('', SEND_EXTRA_ORDER_EMAILS_TO, EMAIL_TEXT_SUBJECT, $email_order, STORE_OWNER, STORE_OWNER_EMAIL_ADDRESS); 
 } 

// load the after_process function from the payment modules 
 $payment_modules->after_process(); 

 $cart->reset(true); 

// unregister session variables used during checkout 
 tep_session_unregister('sendto'); 
 tep_session_unregister('billto'); 
 tep_session_unregister('shipping'); 
 tep_session_unregister('payment'); 
 tep_session_unregister('comments'); 

 tep_redirect(tep_href_link(FILENAME_CHECKOUT_SUCCESS, '', 'SSL')); 

 require(DIR_WS_INCLUDES . 'application_bottom.php'); 
?>

GanodermaCoffee.com · November 19, 2006

I just tried to add another customer and it adds once again, but it does not update all of the tables. For example, it does not updated the address_book table because it leaves the customer_id as 0 instead of the correct id.

Not sure if this gives anyone any more clues as to what the heck might be wrong here.

Thanks for any help.

Ugh...I dont want to have to start over again.

Evan

zxedxz · April 19, 2007

I have exaclty the same problem. why is it? Did you find the solution?

GeorgeP · April 19, 2007

I dont know if this is the same problem I had so please backup backup backup.

Try to see if modifying includes/functions/database.php will do the trick:

#original function comment

# function tep_db_insert_id() {

# return mysql_insert_id();

# }

function tep_db_insert_id( $link = 'db_link') {

global $$link;

return mysql_insert_id($$link);

}

kewldude_3001 · April 19, 2007

just like to say i like ur shop layout :)

zxedxz · April 19, 2007

I already have that on my database.php file. But still have the error. Sometimes I get wrong order_id input on my database and customer_default_address_id also happens the same. Sometimes it has = 0. But I mean...the error only happens like 2 out of 3 or 4 orders.

zxedxz · April 30, 2007

Any help? Still have the problem

ErollorD · April 30, 2007

maybe you should check the support forum for that contribution...

zxedxz · May 1, 2007

there is no contribution involved in this error

May 1, 2007

there is no contribution involved in this error

is it? what changes have you done with the countries/states for your store?

zxedxz · May 1, 2007

Do you think it have to do with that? I have done a lot of changes to the country and state script. What file should I post?

May 2, 2007

Do you think it have to do with that? I have done a lot of changes to the country and state script. What file should I post?

Like what kind of changes? Deleted countries from a seasoned store? That could cause problems. Also what happens if someone blocks jscripts and submts the create account form? Will he end up with country canada and state california? Things like that. That can completely mess your store.

zxedxz · May 2, 2007

No, no such problem. State and country is fine. The problem is with Order_id. Witht he order. the id generated doesn't get input correctly on all the order tables. The order table has the right order_id, but the other order table like order_total, have a weird order_id.

May 3, 2007

in your configure.php file what's the persistent connection setting? Is it set to true? If so try it with false.

define('USE_PCONNECT', 'false'); // use persistent connections?

zxedxz · May 4, 2007

eerr...no. It has always been false. Any other suggestion? Thanks

zxedxz · May 6, 2007

Fixed this problem already. Thanks. It was a problem with sessions. I fixed the way they are stored and bump..the problem went away.

marcus76 · June 18, 2008

anyone else seen this issue and know the exact fix??

i'm desperate :(

Tables not recording order_id properly

Recommended Posts

GanodermaCoffee.com

Guest

GanodermaCoffee.com

GanodermaCoffee.com

zxedxz

GeorgeP

kewldude_3001

zxedxz

zxedxz

ErollorD

zxedxz

Guest

zxedxz

Guest

zxedxz

Guest

zxedxz

zxedxz

marcus76

Archived

Browse

Activity