Customize admin customer search query: search alt emails, too?

[Matt008]

Hey, all. Hopefully you guys out there have experienced this before.

In the admin section, when searching through the customer list I would like the query to also search any listed alternate emails. Right now if customer John Doe has email1 and email2, I'd like to be able to put in email2 and have it show John Doe's customer details.

I just need help adjusting the query in customers.php to reflect this addition. I suspect that the "customers_alternate_email" table will need to be thrown somewhere in the mix as well.

// preliminary search?
      $pre_search = $db->Execute("select customers_id from customers c, serials s where c.customers_email_address=s.email and s.serial like '%" . $keywords . "%' limit 1");
      $cid = $pre_search->fields['customers_id'];
      
      $search = "where c.customers_lastname like '%" . $keywords . "%' or c.customers_firstname like '%" . $keywords . "%' or c.customers_email_address like '%" . $keywords . "%' or c.customers_telephone rlike ':keywords:' or a.entry_company rlike ':keywords:' or a.entry_street_address rlike ':keywords:' or a.entry_city rlike ':keywords:' or a.entry_postcode rlike ':keywords:' or c.customers_notes like '%" . $keywords . "%' or c.customers_id='" . $cid . "'";
      $search = $db->bindVars($search, ':keywords:', $keywords, 'regexp');
    }
    $new_fields=', c.customers_telephone, a.entry_company, a.entry_street_address, a.entry_city, a.entry_postcode, c.customers_authorization, c.customers_referral';
    $customers_query_raw = "select c.customers_id, c.customers_lastname, c.customers_firstname, c.customers_email_address, c.customers_group_pricing, a.entry_country_id, a.entry_company, ci.customers_info_date_of_last_logon, ci.customers_info_date_account_created " . $new_fields . ",
    cgc.amount
    from " . TABLE_CUSTOMERS . " c
    left join " . TABLE_CUSTOMERS_INFO . " ci on c.customers_id= ci.customers_info_id
    left join " . TABLE_ADDRESS_BOOK . " a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id " . "
    left join " . TABLE_COUPON_GV_CUSTOMER . " cgc on c.customers_id = cgc.customer_id " .
    $search . " group by c.customers_email_address order by $disp_order"; 

If I'm completely off with this code snippet or there's a much easier way then editing the query (I don't want to break anything with the DB..), I'd be happy to hear it!

Thank you!

[Matt008]

Anyone have thoughts on this or have tried it?

I tried to put in a nested select.

$keywords . "%' or c.customers_id='" . $cid . "'";
            $keywords . " or (SELECT customers_email FROM customers_alternate_emails where customers_alternate_emails.customers_id='" . $cid . "') like '%" . $keywords . "%'";
      $search = $db->bindVars($search, ':keywords:', $keywords, 'regexp'); 

..but no luck there. Everything searches fine still, but doesn't recognize any additional email. I just haven't worked with a search field like this before and I'm not sure where to tweak the code a bit.

[Matt008]

Closed! It's working now. Just had to dive deeper into how the PHP search stuff works and how all of this code is organized.

If anyone else browses through here, here is my code that I changed to include the alternate email info in the search:

$search = "where c.customers_lastname like '%" . $keywords . 
            "%' or c.customers_firstname like '%" . $keywords . 
            "%' or c.customers_email_address like '%" . $keywords .
            "%' or cae.customers_email like '%" . $keywords .
            "%' or c.customers_telephone rlike ':keywords:' or a.entry_company rlike ':keywords:' " .
            "or a.entry_street_address rlike ':keywords:' or a.entry_city rlike ':keywords:' " . 
            "or a.entry_postcode rlike ':keywords:' or c.customers_notes like '%" . 
            $keywords . "%' or c.customers_id='" . $cid . "'";

      $search = $db->bindVars($search, ':keywords:', $keywords, 'regexp');
    }
    $new_fields=', c.customers_telephone, a.entry_company, a.entry_street_address, ' . 
        'a.entry_city, a.entry_postcode, c.customers_authorization, c.customers_referral';
    $customers_query_raw = "select c.customers_id, c.customers_lastname, c.customers_firstname, " .
        "c.customers_email_address, c.customers_group_pricing, a.entry_country_id, a.entry_company, " .
        "ci.customers_info_date_of_last_logon, ci.customers_info_date_account_created " . $new_fields . 
        ", cgc.amount from " . TABLE_CUSTOMERS . " c
    left join " . TABLE_CUSTOMERS_INFO . " ci on c.customers_id= ci.customers_info_id
    left join " . TABLE_ADDRESS_BOOK . " a on c.customers_id = a.customers_id and c.customers_default_address_id = a.address_book_id " . "
    left join " . TABLE_COUPON_GV_CUSTOMER . " cgc on c.customers_id = cgc.customer_id " . "
    left join " . TABLE_CUSTOMERS_ALTERNATE_EMAILS . " cae on c.customers_id = cae.customers_id " .
    $search . " group by c.customers_email_address order by $disp_order"; 

I also had to update the database_tables.php file to include the TABLE_CUSTOMERS_ALTERNATE_EMAILS definition.

[AATECH]

Hi Matt008... Thank you for posting this... Do you know how we can also be able to search for what is in the customers_referral table in the code. It is already in the code but the search doesn't pick this up.

Thanks in advance.