Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Customer List Improved - Adding Number of Orders


Guest

Recommended Posts

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.

Link to comment
Share on other sites

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>

Link to comment
Share on other sites

  • 2 weeks later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...