How to make inactive one shipping table if the order get more than 4 kg?

[hairydog]

The order process includes a table for attributes (orders_products_attributes), the table on its own does not include the weight of the attributes, but does offer sufficient information to retrieve the associated attribute information that (still) exists in the store side.There are three paths that I could see to address this. The first, would be to pull all of the order information together using a left join of the attributes order table and the data that might still exist in the store to obtain all of the weights. (risk is that at some point in the future the live store data may change, so "historical" information would be lost, but on a day-to-day basis would give up-to-date information.

Thanks for confirming that the data isn't stored anywhere in the order record. Seems odd that it isn't, but there you are. It looks like the orders_products_attributes table has the attributes weight but the orders_products table lacks the products weights - need to go to the products table for that info.This is only needed for generating shipping labels, so it doesn't matter that there is no long term storage of attribute weights. So a left join is probably all that is needed. I just wish I was better at writing this sort of code!Right now I have

$proids_query="SELECT * FROM " . TABLE_PRODUCTS." as t2 WHERE t2.products_id = $prodid ";

$Orderedproductweight = $db->Execute($proids_query) ;

$sum = $Orderedproductweight->fields['products_weight'];

$sum=$sum * $prodnum; $totalweight=1.15*($totalweight+$sum+0.02);

The last line is to allow for packaging weight

[hairydog]

This seems to work- but I can't persuade it to look readable in this message!

$proid_query="SELECT * FROM " . TABLE_ORDERS_PRODUCTS . " as t1 LEFT JOIN orders_products_attributes t3 ON t1.orders_products_id = t3.orders_products_id WHERE t1.orders_id = $orderid"; $Orderedproduct = $db->Execute($proid_query); // sum total weight while (!$Orderedproduct->EOF) { $prodid = $Orderedproduct->fields['products_id']; $prodnum = $Orderedproduct->fields['products_quantity']; $proids_query="SELECT * FROM " . TABLE_PRODUCTS." as t2 WHERE t2.products_id = $prodid "; $Orderedproductweight = $db->Execute($proids_query) ; $sumprod = $Orderedproductweight->fields['products_weight']; $sumattrib = $Orderedproduct->fields['products_attributes_weight']; $sum = $sumprod+$sumattrib;$sum=$sum * $prodnum; // allow for packaging $totalweight=$totalweight+(1.15 * $sum); 

I'm not sure how elegant the code is, but...

[mc12345678]

Here is a suggestion on the code updated to reduce the query load, to add a little data sanitization (which could be done in a few different ways), and to address the possibility that a product could have more than one attribute associated (which would have added the weight of the product for each attribute associated with the product.

I tried to highlight the changes.

$proid_query="SELECT t1.products_id, t1.products_quantity, t3.products_attributes_weight FROM " . TABLE_ORDERS_PRODUCTS . " as t1 LEFT JOIN " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " t3 ON t1.orders_products_id = t3.orders_products_id WHERE t1.orders_id = " . (int)$orderid; 
$Orderedproduct = $db->Execute($proid_query); 

$prevProdID = 0;

// sum total weight 
while (!$Orderedproduct->EOF) 
{ 
  $prodid = $Orderedproduct->fields['products_id']; 
  $prodnum = $Orderedproduct->fields['products_quantity']; 

  $proids_query="SELECT t2.products_weight FROM " . TABLE_PRODUCTS." as t2 WHERE t2.products_id = " . (int)$prodid; 
  $Orderedproductweight = $db->Execute($proids_query) ; 

  if ($prodid != $prevProdID) {
    $sumprod = $Orderedproductweight->fields['products_weight'];
    $prevProdId = $prodid;
  } else {
    $sumprod = 0;
  }

  $sumattrib = $Orderedproduct->fields['products_attributes_weight']; 
  $sum = $sumprod+$sumattrib;
  $sum=$sum * $prodnum; 
  // allow for packaging 
  $totalweight=$totalweight+(1.15 * $sum);
  $Orderedproduct->MoveNext();
}

The original query could also be made as:

$proid_query="SELECT t1.products_id, t1.products_quantity, t2.products_weight, t3.products_attributes_weight FROM " . TABLE_ORDERS_PRODUCTS . " as t1 LEFT JOIN " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " t3 ON t1.orders_products_id = t3.orders_products_id LEFT JOIN " . TABLE_PRODUCTS." t2 ON t1.products_id = t2.products_id WHERE t1.orders_id = " . (int)$orderid;

[mc12345678]

Sorry I ran out of time to revise my message... Bad habit, but I review the code after posting and then see things.. :/

Below is yet a further revised *untested* code. Seems to have what you are seeking. I also reformatted a few things including adding the reference to the orders_products_attributes table to be compatible with those using a DB_PREFIX.

$proid_query="SELECT t1.products_id, t1.products_quantity, t2.products_weight, t3.products_attributes_weight 
            FROM " . TABLE_ORDERS_PRODUCTS . " as t1 
            LEFT JOIN " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " t3 ON t1.orders_products_id = t3.orders_products_id
            LEFT JOIN " . TABLE_PRODUCTS." t2 ON t1.products_id = t2.products_id 
            WHERE t1.orders_id = " . (int)$orderid; 
$Orderedproduct = $db->Execute($proid_query); 

$prevProdID = 0;

// sum total weight 
while (!$Orderedproduct->EOF) 
{ 
  $sumprod = 0.0; // Added in case a product has more than one attribute so that the previous value is not carried forward.
  $sumattrib = 0.0;; // Added in case a product does not have an attribute that the previous value is not carried forward.

  $prodid = $Orderedproduct->fields['products_id']; 
  $prodnum = $Orderedproduct->fields['products_quantity']; 

  if ($prodid != $prevProdID) {
    $sumprod = $Orderedproduct->fields['products_weight'];
    $prevProdId = $prodid;
  } 

  if (zen_not_null($Orderedproduct->fields['products_attributes_weight'])) {
    $sumattrib = $Orderedproduct->fields['products_attributes_weight'];
  }

  $sum = $sumprod+$sumattrib;
  $sum=$sum * $prodnum; 
  // allow for packaging 
  $totalweight=$totalweight+(1.15 * $sum);
  $Orderedproduct->MoveNext();
}

[hairydog]

That's marvellous - works a treat! Thank you so much for that.

[hairydog]

Oops! It isn't working quite as well as I thought.

What it is doing is to add the product's weigh to the attribute's weight for each attribute

For example, a product with a weight of 3, and attributes weighing 0.3 and 0.4 should come out weighing 3.7 (which is 4.275 after packing allowance) but it actually comes out at 3.3 + 3.4 = 6.7 (7.725).

If it has three attributes, weighing 0, 0.3, 0.4 the same item comes out weighing 3 + 3.3 + 3.4 = 9.7 (which is 11.175 after packing allowance)

If the three attributes weigh 0, it comes out at 9 (10.37)

This is the code I'm using:

$proid_query="SELECT t1.products_id, t1.orders_products_id, t1.products_quantity, t2.products_weight, t3.products_attributes_weight             FROM " . TABLE_ORDERS_PRODUCTS . " as t1 
            LEFT JOIN " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " t3 ON t1.orders_products_id = t3.orders_products_id
            LEFT JOIN " . TABLE_PRODUCTS." t2 ON t1.products_id = t2.products_id 
            WHERE t1.orders_id = " . (int)$orderid; 


// not using comments 1 of 2
// next line grabs the comments to output on the interlink labels 
// $comments_query ="SELECT comments FROM orders_status_history WHERE `orders_id` = $orderid ORDER BY orders_status_history_id LIMIT 1"; 
  
// Now start per product within an order


$Orderedproduct = $db->Execute($proid_query); 
$prevProdID = 0;


// sum total weight of each item in this order    
while (!$Orderedproduct->EOF)  
{ 


  $sumprod = 0.0; // Added in case a product has more than one attribute so that the previous value is not carried forward.
  $sumattrib = 0.0; // Added in case a product does not have an attribute that the previous value is not carried forward.




$prodid = $Orderedproduct->fields['orders_products_id']; 
$prodnum = $Orderedproduct->fields['products_quantity'];


  if ($prodid != $prevProdID) {
    $sumprod = $Orderedproduct->fields['products_weight'];
    $prevProdId = $prodid;
  }   
   if (zen_not_null($Orderedproduct->fields['products_attributes_weight'])) {
    $sumattrib = $Orderedproduct->fields['products_attributes_weight'];
  } 


  $sum = $sumprod+$sumattrib;
  $sum=$sum * $prodnum; 


 $totalweight = $totalweight+$sum;
 
 $Orderedproduct->MoveNext(); 
 
} 
// that finishes the per product bit
// The rest is per order


  // allow for packaging (per order) 
$totalweight=(1.15*$totalweight)+0.02;

Can anyone help me out with getting it to only add the product weight once per product, not once per attribute?

[mc12345678]

Oops! It isn't working quite as well as I thought.

What it is doing is to add the product's weigh to the attribute's weight for each attribute

For example, a product with a weight of 3, and attributes weighing 0.3 and 0.4 should come out weighing 3.7 (which is 4.275 after packing allowance) but it actually comes out at 3.3 + 3.4 = 6.7 (7.725).

If it has three attributes, weighing 0, 0.3, 0.4 the same item comes out weighing 3 + 3.3 + 3.4 = 9.7 (which is 11.175 after packing allowance)

If the three attributes weigh 0, it comes out at 9 (10.37)

This is the code I'm using:

$proid_query="SELECT t1.products_id, t1.orders_products_id, t1.products_quantity, t2.products_weight, t3.products_attributes_weight             FROM " . TABLE_ORDERS_PRODUCTS . " as t1 
            LEFT JOIN " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " t3 ON t1.orders_products_id = t3.orders_products_id
            LEFT JOIN " . TABLE_PRODUCTS." t2 ON t1.products_id = t2.products_id 
            WHERE t1.orders_id = " . (int)$orderid; 


// not using comments 1 of 2
// next line grabs the comments to output on the interlink labels 
// $comments_query ="SELECT comments FROM orders_status_history WHERE `orders_id` = $orderid ORDER BY orders_status_history_id LIMIT 1"; 
  
// Now start per product within an order


$Orderedproduct = $db->Execute($proid_query); 
$prevProdID = 0;


// sum total weight of each item in this order    
while (!$Orderedproduct->EOF)  
{ 


  $sumprod = 0.0; // Added in case a product has more than one attribute so that the previous value is not carried forward.
  $sumattrib = 0.0; // Added in case a product does not have an attribute that the previous value is not carried forward.




$prodid = $Orderedproduct->fields['orders_products_id']; 
$prodnum = $Orderedproduct->fields['products_quantity'];


  if ($prodid != $prevProdID) {
    $sumprod = $Orderedproduct->fields['products_weight'];
    $prevProdId = $prodid;
  }   
   if (zen_not_null($Orderedproduct->fields['products_attributes_weight'])) {
    $sumattrib = $Orderedproduct->fields['products_attributes_weight'];
  } 


  $sum = $sumprod+$sumattrib;
  $sum=$sum * $prodnum; 


 $totalweight = $totalweight+$sum;
 
 $Orderedproduct->MoveNext(); 
 
} 
// that finishes the per product bit
// The rest is per order


  // allow for packaging (per order) 
$totalweight=(1.15*$totalweight)+0.02;

Can anyone help me out with getting it to only add the product weight once per product, not once per attribute?

Seemed like something was wrong with that, but hadn't quite realized what it would be.

The below is modified from above and might accomplish the requested action.

$proid_query="SELECT t1.products_id, t1.orders_products_id, t1.products_quantity, t1.products_prid, t2.products_weight, t3.products_attributes_weight             FROM " . TABLE_ORDERS_PRODUCTS . " as t1 
            LEFT JOIN " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " t3 ON t1.orders_products_id = t3.orders_products_id
            LEFT JOIN " . TABLE_PRODUCTS." t2 ON t1.products_id = t2.products_id 
            WHERE t1.orders_id = " . (int)$orderid . " ORDER BY t1.products_prid"; 


// not using comments 1 of 2
// next line grabs the comments to output on the interlink labels 
// $comments_query ="SELECT comments FROM orders_status_history WHERE `orders_id` = $orderid ORDER BY orders_status_history_id LIMIT 1"; 
  
// Now start per product within an order


$Orderedproduct = $db->Execute($proid_query); 
$prevProdID = 0;


// sum total weight of each item in this order    
while (!$Orderedproduct->EOF)  
{ 


  $sumprod = 0.0; // Added in case a product has more than one attribute so that the previous value is not carried forward.
  $sumattrib = 0.0; // Added in case a product does not have an attribute that the previous value is not carried forward.




$prodid = $Orderedproduct->fields['products_prid']; 
$prodnum = $Orderedproduct->fields['products_quantity'];


  if ($prodid != $prevProdID) {
    $sumprod = $Orderedproduct->fields['products_weight'];
    $prevProdId = $prodid;
  }   
   if (zen_not_null($Orderedproduct->fields['products_attributes_weight'])) {
    $sumattrib = $Orderedproduct->fields['products_attributes_weight'];
  } 


  $sum = $sumprod+$sumattrib;
  $sum=$sum * $prodnum; 


 $totalweight = $totalweight+$sum;
 
 $Orderedproduct->MoveNext(); 
 
} 
// that finishes the per product bit
// The rest is per order


  // allow for packaging (per order) 
$totalweight=(1.15*$totalweight)+0.02;

[hairydog]

Unfortunately, that does exactly the same. It seems to be looping round the product weight once fer each attribute entry.

I've been trying to separate the queries so that it looks up the product weights first, then uses a separate query to look up the attribute weights, then adds the results together and multiplies the result by the quantity ordered, but without much success.

[mc12345678]

Unfortunately, that does exactly the same. It seems to be looping round the product weight once fer each attribute entry.

I've been trying to separate the queries so that it looks up the product weights first, then uses a separate query to look up the attribute weights, then adds the results together and multiplies the result by the quantity ordered, but without much success.

There's a typo. I may have introduced it, not sure, not going back to check:

 if ($prodid != $prevProdID) {
    $sumprod = $Orderedproduct->fields['products_weight'];
    $prevProdID = $prodid;
  }

[mc12345678]

I believe either method of the previous code with that typo corrected would provide the desired result as there is one orders_products_id for each unique product added to the cart (two products each having a different set of attributes will each have a unique orders_products_id). As well, each product added to the cart is expected to have a unique products_prid for any two or more product having attributes. Any two or more product having the same attributes (or none) will be treated by the cart as an additional quantity of the first one added, therefore within an order, the products_prid is expected to be unique for each product or quantity of product added to the cart.

Seems to me though that the first one is lighter on the database engine/more efficient. Though a method like you are describing would likely be clearer code:
Obtain each product and related other table relationship.
Obtain the information from the order_products_attributes table for the order and the specific product. If there are attributes, add the weight, if not use the product's weight and then multiply by the quantity.

Involves a lot more code and frequent queries to the database, but later could offer expansion/modification to use that data in other ways not yet envisioned...