customers.php & orders.php - query - duplicate lines where address book entry > 1

[Supertex]

I've made modifications to both orders.php and customers.php intended to work alongside the 'blacklist' addon by @@Jack_mcs .  My modifications query the 'blacklist' table, and apply a colored background to the customer in either list, depending on the level of blacklisting (watch, vs. fully banned).

 

I noticed in the addon itself, that if a customer has multiple addresses in their address book, that they show up in the blacklist module multiple times - matching their address count.  Not a big deal.  However, I've noticed the same behavior in 

customers.php and orders.php as well.

 

If someone would have a peek at this and suggest where I've made my mistake, I'd be grateful.  Here is the query:

    if (isset($HTTP_GET_VARS['cID'])) {
	    // BOF Separate Pricing Per Customer
      $cID = tep_db_prepare_input($HTTP_GET_VARS['cID']);
      $orders_query_raw = "select b.ban_customer as banned, b.customers_id as watch_list, o.orders_id, o.customers_name, o.customers_id, o.payment_method, o.shipping_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total, cg.customers_group_name from " . TABLE_ORDERS . " o left join " . TABLE_BLACKLIST . " b on o.customers_id = b.customers_id left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) left join " . TABLE_CUSTOMERS . " c on c.customers_id = o.customers_id left join " . TABLE_CUSTOMERS_GROUPS . " cg using(customers_group_id), " . TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by orders_id DESC";
    } elseif (isset($HTTP_GET_VARS['status'])) {
      $status = tep_db_prepare_input($HTTP_GET_VARS['status']);
      $orders_query_raw = "select b.ban_customer as banned, b.customers_id as watch_list, o.orders_id, o.customers_name, o.payment_method, o.shipping_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total, cg.customers_group_name from " . TABLE_ORDERS . " o left join " . TABLE_BLACKLIST . " b on o.customers_id = b.customers_id left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) left join " . TABLE_CUSTOMERS . " c on c.customers_id = o.customers_id left join " . TABLE_CUSTOMERS_GROUPS . " cg using(customers_group_id), " . TABLE_ORDERS_STATUS . " s where o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and s.orders_status_id = '" . (int)$status . "' and ot.class = 'ot_total' order by o.orders_id DESC";
    } else {
      $orders_query_raw = "select b.ban_customer as banned, b.customers_id as watch_list, o.orders_id, o.customers_name, o.payment_method, o.shipping_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total, cg.customers_group_name from " . TABLE_ORDERS . " o left join " . TABLE_BLACKLIST . " b on o.customers_id = b.customers_id left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id) left join " . TABLE_CUSTOMERS . " c on c.customers_id = o.customers_id left join " . TABLE_CUSTOMERS_GROUPS . " cg using(customers_group_id), " . TABLE_ORDERS_STATUS . " s where (o.orders_status != 9) and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by o.orders_id DESC";
      // EOF Separate Pricing Per Customer
    }

I'm unsure if I've used a join inappropriately, or if (since this occurs in the module itself also) this is due to the blacklist table structure.

 

I'll also attach a screen of the DB structure for that table:

  

 

And here is how I've modified the output to reference the .css, in case that might have some bearing on this:

 

   if (isset($oInfo) && is_object($oInfo) && ($orders['orders_id'] == $oInfo->orders_id)) {
        if(is_null($orders['watch_list'])){
        echo '              <tr id="defaultSelected" class="dataTableRowSelected" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID', 'action')) . 'oID=' . $oInfo->orders_id . '&action=edit') . '\'">' . "\n";
      	} elseif($orders['banned'] == 0){
        echo '              <tr id="defaultSelected" class="dataTableRowSelectedSpec1" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID', 'action')) . 'oID=' . $oInfo->orders_id . '&action=edit') . '\'">' . "\n";
				} elseif($orders['banned'] == 1){
				echo '              <tr id="defaultSelected" class="dataTableRowSelectedSpec2" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID', 'action')) . 'oID=' . $oInfo->orders_id . '&action=edit') . '\'">' . "\n";
				}
      } else {
      	if(is_null($orders['watch_list'])){
        echo '              <tr class="dataTableRow" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID')) . 'oID=' . $orders['orders_id']) . '\'">' . "\n";
      	}elseif($orders['banned'] == 0){
        echo '              <tr class="dataTableRowSpec1" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID')) . 'oID=' . $orders['orders_id']) . '\'">' . "\n";
      	}elseif($orders['banned'] == 1){
        echo '              <tr class="dataTableRowSpec2" onmouseover="rowOverEffect(this)" onmouseout="rowOutEffect(this)" onclick="document.location.href=\'' . tep_href_link(FILENAME_ORDERS, tep_get_all_get_params(array('oID')) . 'oID=' . $orders['orders_id']) . '\'">' . "\n";
      	}
      }

Any clues?

 

[Supertex]

I think I'm seeing why this happens now.  In the blacklist table, there's a separate row for every address a customer has, and the code just looks for whether or not that customer ID exists in a row.  When it finds 3 rows with that ID, it outputs 3 rows.  So I suppose the way to fix this, is to add an INT field in the customers table with a default 0, and have blacklist.php set it to 1 for watch, and 2 for ban completely, then change my customers.php and orders.php to look for that instead.  Not sure how to have the blacklist.php do that without breaking something though.