Guest Posted August 28, 2007 Share Posted August 28, 2007 I have installed the Customer List Improved mod and like it very much. It would help me if I could also view # of orders in the list and even sort by most ordered. The query I am working with is: $customers_query_raw = "select c.customers_id, c.customers_lastname, c.customers_firstname, c.customers_email_address, c.customers_telephone, c.customers_dob, ci.customers_info_date_of_last_logon as last_logon, ci.customers_info_number_of_logons as num_logons, ci.customers_info_date_account_created as date_account_created, a.entry_city as city, a.entry_state as state_alt, z.zone_name as state, ctry.countries_iso_code_2 as country,c.customers_newsletter, a.entry_country_id, c.member_flag from " . TABLE_CUSTOMERS . " c 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_CUSTOMERS_INFO . " ci on c.customers_id = ci.customers_info_id left join " . TABLE_COUNTRIES . " ctry on a.entry_country_id = ctry.countries_id left join " . TABLE_ZONES . " z on a.entry_zone_id = z.zone_id " . $search . " order by " . $db_orderby . " " . $sort; I'm guessing I would have to do something like left join" . TABLE_ORDERS . " o on c.customer_id = o.customer_id" . ...and at that point I'm not really sure how to issue a count of numbers in o.order_id If someone could push me in the right direction or give me a field where the customers orders are already counted (summed) that would be great. Thank you. Quote Link to comment Share on other sites More sharing options...
Guest Posted August 29, 2007 Share Posted August 29, 2007 Solved myself. Posting the solution if someone else looks for it (or I need to find it again) Found this line while ($customers = tep_db_fetch_array($customers_query)) { $info_query = tep_db_query("select customers_info_date_account_created as date_account_created, customers_info_date_account_last_modified as date_account_last_modified, customers_info_date_of_last_logon as date_last_logon, customers_info_number_of_logons as number_of_logons from " . TABLE_CUSTOMERS_INFO . " where customers_info_id = '" . $customers['customers_id'] . "'"); $info = tep_db_fetch_array($info_query); and below it added //BOF: Add Orders to table $order_count_query = tep_db_query("select count(*) as number_of_orders from " . TABLE_ORDERS . " where customers_id = '" . (int)$customers['customers_id'] . "'"); $order_count = tep_db_fetch_array($order_count_query); $info = array_merge($info, $order_count); //EOF: Add Orders to Table then added to the table list the call to the array <td class="dataTableContent" align="center"><?php echo ($info['number_of_orders']); ?></td> Quote Link to comment Share on other sites More sharing options...
UncleSteve Posted September 6, 2007 Share Posted September 6, 2007 Which folder/file are your working with please? Quote ____________________________________________________________________ ____________________________________________________________________ Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.