Displaying Customers inputed tex attributes on product page

Hi

I run a personalised gift shop and customers enter there personalization to the item when purchasing goods.

I would like to display the last 5 or so "personalised messages" which customers have previously typed in on the product page below the product description.

Does anyone know how to do this? I know I need to edit the productinfo page but I don't know how to query the database for the text and display this using php for the product.

Regards
Tracey

You would need to find the options_id for the Attribute that you are using for the text ...

Then on the tpl_product_info_display.php you can use your templates and overrides with something like this:

<?php
// bof: show last 5 text orders for options_id 10
  $sql = "SELECT products_options_values FROM " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " WHERE products_prid LIKE '" . (int)$_GET['products_id'] . ":%' and products_options_id = 10 limit 5";
//  echo 'GETTING: ' . $sql . '<br/>';
  $show_text = $db->Execute($sql);
  echo 'Last Personalized Messages: ' . '<br>';
  while (!$show_text->EOF) {
    echo $show_text->fields['products_options_values'] . '<br />';
    $show_text->MoveNext();
  }
// bof: show last 5 text orders for options_id 10
?>

Many Thanks Ajeh, I have added the code to the products page for option id 19 and it shows the customers messages for some products which is brilliant.

I am using a variety of option ids however, so is it possible to check for multiple option ids? I've had a play around with the code ie, adding more ids separated by commas and brackets but keep breaking the cart.

Many many thanks

Tracey

You can pull from several options_id with:

  $sql = "SELECT products_options_values FROM " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " WHERE products_prid LIKE '" . (int)$_GET['products_id'] . ":%' and products_options_id IN ('10', '11', '12') limit 5";

That works great, the order appears in ascending order and shows the first orders placed by in 2009, could the code be modified to show this is descending order by the order id showing the most recent ones?

I have had a play with this and read some articles online and my code is as follows but it just gives me an error :) but I think I'm on the right track. I assume i could display the most recent personalised messages by the "orders_id". This would then refresh each product page whenever a customer makes a purchase, and keep the page fresh in Google.

$sql = "SELECT products_options_values FROM " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " ORDER by orders_id WHERE products_prid LIKE '" . (int)$_GET['products_id'] . ":%' and products_options_id IN ('7', '19', '177', '174', '178', '12', '13', '27', '38', '39', '40', '150', '64', '179', '180', '203', '200', '204', '153', '261', '52', '218', '219', '257', '258', '261', '266', '270', '274', '70') limit 7 ";

Also a nifty "If" statement would be helpful to not show any details at all if a)noone has ever purchased a product ie a newly added item, and b) if the item has no attributes ie not personalised.

Think I've cracked it sql putting it in descending order (i am well chuffed!!) see below. Just need to look at the if statement now. :)

$sql = "SELECT products_options_values FROM " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " WHERE products_prid LIKE '" . (int)$_GET['products_id'] . ":%' and products_options_id IN ('7', '19', '177', '174', '178', '12', '13', '27', '38', '39', '40', '150', '64', '179', '180', '203', '200', '204', '153', '261', '52', '218', '219', '257', '258', '261', '266', '270', '274', '70') ORDER BY orders_id DESC limit 7 ";

Add an order by statement to that select to sort the SQL statement ...

To do that, you are going to need to work in a JOIN or something on the orders table to work with the date ...

Or, you could sort by the field:
orders_products_attributes_id

which, as the table is built numerically, should produce them in the order in which the the Orders were created ...

Hi Linda

Just after I posted again I cracked the SQL so our posts overlap, I sorted the data by the orders_id and works a treat.

Think I've cracked it sql putting it in descending order (i am well chuffed!!) see below. Just need to look at the if statement now. :)

$sql = "SELECT products_options_values FROM " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " WHERE products_prid LIKE '" . (int)$_GET['products_id'] . ":%' and products_options_id IN ('7', '19', '177', '174', '178', '12', '13', '27', '38', '39', '40', '150', '64', '179', '180', '203', '200', '204', '153', '261', '52', '218', '219', '257', '258', '261', '266', '270', '274', '70') ORDER BY orders_id DESC limit 7 ";

Hmm not having much luck, I don't want to display any HTML if there is no result, I've tried using an IF statement like below but it doesn't seem to work and have tried an else statement but keep breaking the cart.

// bof: show last 5 text orders for options_id 10
$sql = "SELECT products_options_values FROM " . TABLE_ORDERS_PRODUCTS_ATTRIBUTES . " WHERE products_prid LIKE '" . (int)$_GET['products_id'] . ":%' and products_options_id IN ('7', '19', '177', '174', '178', '12', '13', '27', '38', '39', '40', '150', '64', '179', '180', '203', '200', '204', '153', '261', '52', '218', '219', '257', '258', '261', '266', '270', '274', '70') ORDER BY orders_id DESC limit 10 ";

if(mysql_num_rows($result)==0){
//do this
(STOP);
}

$show_text = $db->Execute($sql);
echo '<div style="border: 1px solid #999;margin-bottom: 10px; width:400px;">

You need to run this line first before the IF

$show_text = $db->Execute($sql);

then you can use something like:

if ($show_text->RecordCount() >= 1) {