Displaying custom field on different templates

I have created a custom text field called it products_final_sale under zen_products TABLE that displays a notice on the product_info_page. I would also like to display this field on the following pages as well.

index.php?main_page=shopping_cart
index.php?main_page=checkout_confirmation
index.php?main_page=checkout_success
index.php?main_page=account_history_info&order_id=12

Any ideas how this could be down?

You would have to edit/add the code to each of those pages

Thanks kobra,

<?php 

$dataQuery = mysql_query("SELECT * FROM zen_products") 
or die(mysql_error()); 

while($lookup = mysql_fetch_array( $dataQuery )) 
	{ 
	echo $lookup['products_id'] . " = " . $lookup['products_final_sale'] . "<br>"; 
	} 
?>

This echos all $product['id']s on the browser. I am trying write an if statement to exit the array when the product id is found, so it displays the products_final_sale message. Working on index.php?main_page=shopping_cart (tpl_shopping_cart_default.php).

Can't find the correct look up function.

Kevin205:

I am trying write an if statement to exit the array when the product id is found, so it displays the products_final_sale message. Working on index.php?main_page=shopping_cart (tpl_shopping_cart_default.php).
Why are you looping through all products in the entire database?

Why not look for just a specific product, and display the message if the flag is set?

ie: (this is CONCEPT ONLY ... it should be done much more efficiently than this):

$sql = "SELECT products_final_sale from " . TABLE_PRODUCTS . " where products_id = " . (int)$whatever_product_id_you_are_inspecting;
$result = $db->Execute($sql);
$is_final_sale = (!$result->EOF) ? $result->fields['products_final_sale'] : FALSE;

I got it to display the notice on index.php?main_page=shopping_cart using the following code:

<?php 
$dataQuery = mysql_query("SELECT * FROM zen_products") 
or die(mysql_error()); 

while($lookup= mysql_fetch_array( $dataQuery )) 
if ($lookup['products_id'] == $product['id'])
echo '<span class="alert bold">' . $lookup['products_final_sale'] . '</span>' ; 
?>

If not efficient, I will try your suggestion.

DrByte
How about the following. Is it OK?

<?php 
$dataFinalSale = "SELECT products_final_sale FROM " . TABLE_PRODUCTS .  " where products_id = " . $product['id'];
$result = $db->Execute($dataFinalSale);

if ($result->fields['products_id'] = $product['id']){
echo '<span class="alert bold">' . $result->fields['products_final_sale'] . '</span>' ; 
}
?>

Or

<?php 
$dataFinalSale = "SELECT products_final_sale FROM " . TABLE_PRODUCTS .  " where products_id = " . $product['id'];
$result = $db->Execute($dataFinalSale);
echo '<span class="alert bold">' . $result->fields['products_final_sale'] . '</span>' ; 
?>

I don't know why I used the If statement.

Both methods generate the products_final_sale message.

If this checks out. I have to implement it on the index.php?main_page=checkout_confirmation

1. It's time to completely stop writing ALL code that uses "mysql_query" and "mysql_fetch_array" and all other functions starting with "mysql_", since all that is obsolete and will break your site starting with PHP 5.5
   Instead, use the Zen Cart $db wrapper, such as I did in my example.

2. I intentionally used the line which checks for (!$result->EOF) in order to correctly handle cases where 0 records are returned from your SELECT query, instead of throwing "undefined" warnings in PHP.

3. I would suggest using this variant:

Kevin205:

How about the following. Is it OK?

Now it is time to deal with
index.php?main_page=checkout_confirmation
index.php?main_page=account_history_info
and
e-mail Order Confirmation (html)

Thank you DrByte.

The following is the result when I add two products tp the cart, one with no attributes and another with attributes.

When I use

 <?php 
$dataFinalSale = "SELECT products_final_sale FROM " . TABLE_PRODUCTS .  " where products_id = " . $product['id'];
$result = $db->Execute($dataFinalSale);
if (!$result->EOF){
echo '<span class="alert bold">' . $result->fields['products_final_sale'] . '</span>' ; 
}
?> 

It generates the following error:

[23-Jun-2013 06:14:15 UTC] PHP Fatal error: 1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':748515423d0630d23ac623f25d519ab0' at line 1 :: SELECT products_final_sale FROM zen_products where products_id = 2184:748515423d0630d23ac623f25d519ab0 in path\Store\includes\classes\db\mysql\query_factory.php on line 120

When I use

It generates the following error:
> [23-Jun-2013 06:16:53 UTC] PHP Fatal error:  1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':748515423d0630d23ac623f25d519ab0' at line 1 :: SELECT products_final_sale FROM zen_products where products_id = 2184:748515423d0630d23ac623f25d519ab0 in path\Store\includes\classes\db\mysql\query_factory.php on line 120

When I use
```php
 <?php 
$dataQuery = mysql_query("SELECT * FROM zen_products") 
or die(mysql_error()); 

while($lookup= mysql_fetch_array( $dataQuery )) 
if ($lookup['products_id'] == $product['id'])
echo '<span class="alert bold">' . $lookup['products_final_sale'] . '</span>' ; 
?> 

It generates no error!

After further testing the following suggestion:

<?php 
$dataFinalSale = "SELECT products_final_sale FROM " . TABLE_PRODUCTS .  " where products_id = " . $product['id'];
echo '<br>Step 1' ;
$result = $db->Execute($dataFinalSale);
echo '<br>Step 2';
if (!$result->EOF){
echo '<span class="alert bold">' . $result->fields['products_final_sale'] . '</span>' ; 
}
?> 

FYI, The above is being placed
between

       <td class="cartProductDisplay">
<a href="<?php echo $product['linkProductsName']; ?>"><span class="listingProductImage back"><?php echo $product['productsImage']; ?></span><span class="itemTitle"><?php echo $product['productsName'] . '<span class="alert bold">' . $product['flagStockCheck'] . '</span>'; ?></span></a>
<br class="clearBoth" />

and

<?php
  echo $product['attributeHiddenField'];
  if (isset($product['attributes']) && is_array($product['attributes'])) {
  echo '<div class="cartAttribsList">';
  echo '<ul>';
    reset($product['attributes']);
    foreach ($product['attributes'] as $option => $value) {
?>

<li><?php echo $value['products_options_name'] . TEXT_OPTION_DIVIDER . nl2br($value['products_options_values_name']); ?></li>

<?php

The error occurs and site breaks at Step 2, after the execution of $result = $db->Execute($dataFinalSale); and when attributes to the product are selected, otherwise when there are no attributes selected or involved there are no errors.

Kevin205:

[23-Jun-2013 06:14:15 UTC] PHP Fatal error: 1064:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':748515423d0630d23ac623f25d519ab0' at line 1 :: SELECT products_final_sale FROM zen_products where products_id = 2184:748515423d0630d23ac623f25d519ab0 in path\Store\includes\classes\db\mysql\query_factory.php on line 120

Note the (int) I added back into my last post. I had forgotten to put it in there after attempting to rewrite yours based on my previous example which had it.

Adding the (int) cleared the errors.

Would it be the same process for the following pages and e-mail confirmation as well?

index.php?main_page=checkout_confirmation
index.php?main_page=account_history_info
and
e-mail Order Confirmation (html)

Thank you.

The concept will be the same, yes. Naturally your source for whatever generates your $product value may vary depending on where you're doing it.

I tried to replicate the same, to display the same field on the following pages and html e-mail, and had no luck.

index.php?main_page=checkout_confirmation
index.php?main_page=account_history_info
and
e-mail Order Confirmation (html)

I can't even get to echo the product ID.

I need some guidance. Thank you.

On index.php?main_page=checkout_confirmation page, code below produces the additional field

 <?php 
$dataFinalSale = "SELECT products_final_sale FROM " . TABLE_PRODUCTS .  " where products_id = " . (int)$order->products[$i]['id'];
$result = $db->Execute($dataFinalSale);
if (!$result->EOF){
echo '<br><span class="alert bold">' . $result->fields['products_final_sale'] . '</span>' ; 
}
?> 

Same PHP code did the trick on index.php?main_page=account_history_info

 <?php 
$dataFinalSale = "SELECT products_final_sale FROM " . TABLE_PRODUCTS .  " where products_id = " . (int)$order->products[$i]['id'];
$result = $db->Execute($dataFinalSale);
if (!$result->EOF){
echo '<br><span class="alert bold">' . $result->fields['products_final_sale'] . '</span>' ; 
}
?>

Replacing (int)$product['id'] with (int)$order->products[$i]['id'], to retrieve products_id.

Everything else is done, but email_template_checkout.html.

I found from a thread on this forum's (How do I modify the text in a 'checkout email'?) that I have to modify /includes/classes/order.php file to make the changes.

At around line 176 I have added:

$dataFinalSale = "SELECT products_final_sale FROM " . TABLE_PRODUCTS .  " where products_id = " . (int)$products['id'];
$result = $db->Execute($dataFinalSale);
if (!$result->EOF){
echo '<br><span class="alert bold">' . $result->fields['products_final_sale'] . '</span>' ; 
}

I have also added $result->fields['products_final_sale'] to the following

// build output for email notification
      $this->products_ordered .=  $this->products[$i]['qty'] . ' x ' . $this->products[$i]['name'] . ($this->products[$i]['model'] != '' ? ' (' . $this->products[$i]['model'] . ') ' : '') . ' = ' .
      $currencies->display_price($this->products[$i]['final_price'], $this->products[$i]['tax'], $this->products[$i]['qty']) .
      ($this->products[$i]['onetime_charges'] !=0 ? "\n" . TEXT_ONETIME_CHARGES_EMAIL . $currencies->display_price($this->products[$i]['onetime_charges'], $this->products[$i]['tax'], 1) : '') .
      $this->products_ordered_attributes . "\n";
      $this->products_ordered_html .=
      '<tr>' . "\n" .
      '<td class="product-details" align="right" valign="top" width="30">' . $this->products[$i]['qty'] . ' x</td>' . "\n" . $result->fields['products_final_sale'] .
      '<td class="product-details" valign="top">' . nl2br($this->products[$i]['name']) . ($this->products[$i]['model'] != '' ? ' (' . nl2br($this->products[$i]['model']) . ') ' : '') . "\n" .
      '<nobr>' .
      '<small><em> '. nl2br($this->products_ordered_attributes) .'</em></small>' .

But I am not getting the products_final_sale message from the products table.

I have also noticed that through the file (int)$order_id is used, but I can not see and match the relation between (int)$order_id and ** (int)$products_id** found in zen_orders_products table.

I know I am **not **matching the products_id, but at this point I am lost.

I think email_template_checkout.html is done also.

in /includes/classes/order.php
in my case around line904

// BOF ADDED TO DISPALY THE FINAL SALE MESSAGE 
$dataFinalSale = "SELECT products_final_sale FROM " . TABLE_PRODUCTS .  " where products_id = " . zen_get_prid($this->products[$i]['id']);
$result = $db->Execute($dataFinalSale);
if (!$result->EOF){
$FinalSale = $result->fields['products_final_sale']; 
}
// EOF ADDED TO DISPALY THE FINAL SALE MESSAGE 

and added $FinalSale after the above

      // build output for email notification
      $this->products_ordered .=  $this->products[$i]['qty'] . ' x ' . $this->products[$i]['name'] . ($this->products[$i]['model'] != '' ? ' (' . $this->products[$i]['model'] . ') ' : '') . ' = ' .
      $currencies->display_price($this->products[$i]['final_price'], $this->products[$i]['tax'], $this->products[$i]['qty']) .
      ($this->products[$i]['onetime_charges'] !=0 ? "\n" . TEXT_ONETIME_CHARGES_EMAIL . $currencies->display_price($this->products[$i]['onetime_charges'], $this->products[$i]['tax'], 1) : '') .
      $this->products_ordered_attributes . "\n";
      $this->products_ordered_html .=
      '<tr>' . "\n" .
      '<td class="product-details" align="right" valign="top" width="30">' . $this->products[$i]['qty'] . ' x</td>' . "\n" . 
      '<td class="product-details" valign="top">' . nl2br($this->products[$i]['name']) . ($this->products[$i]['model'] != '' ? ' (' . nl2br($this->products[$i]['model']) . ') ' : '') . "\n" .
      '<br><strong>' . $FinalSale . '</strong>' .
      '<nobr>' .

Thank you DrByte.