Has the customer purchased a certain item?

[TecBrat]

I will be using php in ezpages to write some custom pages and I want to know if the signed in customer (if they are signed in) has purchase a particular item.

What is the best way to get that information from the database.

I figure it's like this, but I don't actually know the table and field names and my mySQL skills are not as strong as I'd like.

select ORDER NUMBER and ORDER STATUS from ORDERS where CUSTOMERID is CURRENTCUSTOMER and ITEMID is MY_SPECIFIED_ID

and I need a little help using zen's db class to make it work.

[kobra]

Should already be done...
Sign in as a customer with a purchase history and select "my account"
Pick from that code for your page

[TecBrat]

Here's what worked for me, Not fully tested, but quickly tested and seemed to be okay.

<?php
//referenced these files to get the right code bits.
//includes/templates/template_default/templates/tpl_account_default.php -> 
//includes/modules/pages/account/header_php.php -> 

$orders_query = "SELECT o.orders_id, o.date_purchased, o.delivery_name,
                        o.delivery_country, o.billing_name, o.billing_country,
                        ot.text as order_total, s.orders_status_name
                 FROM   " . TABLE_ORDERS . " o, " . TABLE_ORDERS_TOTAL . "  ot, " . TABLE_ORDERS_STATUS . " s
                 WHERE  o.customers_id = :customersID
                 AND    o.orders_id = ot.orders_id
                 AND    ot.class = 'ot_total'
                 AND    o.orders_status = s.orders_status_id
                 AND   s.language_id = :languagesID
                 ORDER BY orders_id DESC LIMIT 3";

$orders_query = $db->bindVars($orders_query, ':customersID', $_SESSION['customer_id'], 'integer');
$orders_query = $db->bindVars($orders_query, ':languagesID', $_SESSION['languages_id'], 'integer');
$orders = $db->Execute($orders_query);

$ordersArray = array();
while (!$orders->EOF) {
  if (zen_not_null($orders->fields['delivery_name'])) {
    $order_name = $orders->fields['delivery_name'];
    $order_country = $orders->fields['delivery_country'];
  } else {
    $order_name = $orders->fields['billing_name'];
    $order_country = $orders->fields['billing_country'];
  }

  $ordersArray[] = array('orders_id'=>$orders->fields['orders_id'],
  'date_purchased'=>$orders->fields['date_purchased'],
  'order_name'=>$order_name,
  'order_country'=>$order_country,
  'orders_status_name'=>$orders->fields['orders_status_name'],
  'order_total'=>$orders->fields['order_total']
  );

  $orders->MoveNext();
}
?>
<?php
  $special_item_id=1;
  $special_ordered=FALSE;
  foreach($ordersArray as $orders) {
   $current_order_id=$orders['orders_id'];
   //$order_item_query="SELECT product_id from zen_orders_products WHERE order_id = $current_order_id";
   $current_product_id=$db->Execute($orders_query);
   if ($current_product_id==$special_item_id){$special_ordered=TRUE;}   
  }
if($special_ordered){echo('Welcome to our Special Page.');}else{echo('Please order our Special Item.');}
?>

[TecBrat]

I know I could have cleaned that up a bit, but I just copied and pasted the first part right out of header_php.php

[ksoup]

I know I could have cleaned that up a bit, but I just copied and pasted the first part right out of header_php.php

Would we be able to use this code right on a product page to show the customer if they have already purchased the item they are currently looking at??? If so ... that would rock!!

[TecBrat]

ksoup,

This might not be the answer you hoped for, but all I can say is "Give it a try". That's how I learn.