[ShishaPipeUK]

Thanks for that, yes it works great, i replaced

<TD nowrap>{if ($users[cat_num].last_login ne 0)}{$users[cat_num].last_login|date_format:$config.Appearance.datetime_format}{else}{$lng.lbl_never_logged_in}{/if}</TD> 

With

<TD nowrap>{if ($users[cat_num].last_login ne 0)}{$users[cat_num].last_login|date_format:$config.Appearance.datetime_format}{else}{$lng.lbl_never_logged_in}{/if}</TD> 
<TD>{* NEWSLISTS GO HERE *}</TD> 
<TD>{$users[cat_num].membership}</TD>

And now shows all the membership, so now to see if anyone is subscribed to the news group, anyone any ideas.

MySQL database has this section in it:

CREATE TABLE xcart_newslist_subscription (
  listid int(11) NOT NULL ,
  email char(128) NOT NULL ,
  since_date int(11) NOT NULL ,
  PRIMARY KEY (listid, email)
);

INSERT INTO xcart_newslist_subscription VALUES (1, '?????@???????.freeserve.co.uk', 1103225577);
INSERT INTO xcart_newslist_subscription VALUES (1, '??????@uni-mainz.de', 1103319498);
INSERT INTO xcart_newslist_subscription VALUES (1, '??????@hotmail.com', 1103378519);
INSERT INTO xcart_newslist_subscription VALUES (1, '??????@hotmail.com', 1104529406);

naturaly i have masked (??????) the email addresses.

All i want to see is a tick box to know they are subscribed to at least one newsgroup.

So looking at the user.php i know i must add the news to:

$advanced_options = array("usertype", "membership", "registration_type", "address_type", "phone", "url", "registration_date", "last_login_date");

I have changed my shopcart/admin/user.php so that i can sort the membership, and just placed in there the title news for the moment, until i work out what i need to do:

I know i need to add the news to be like the membership which is:

   if (!empty($data["membership"])) { 
      # Search by membership... 
      if (preg_match("/pending_membership/i", $data["membership"])) 
         $search_condition .= " AND membership!=pending_membership"; 
      else 
         $search_condition .= " AND membership='".$data["membership"]."' "; 
   } 

Here is the shopcart/admin/user.php code

#
# $Id: users.php,v 1.44.2.3 2005/10/26 05:31:40 max Exp $
#

require "./auth.php";
require $xcart_dir."/include/security.php";

x_session_register("search_data");

$location[] = array(func_get_langvar_by_name("lbl_users_management"), "");

include "./users_tools.php";

$advanced_options = array("usertype", "news", "membership", "registration_type", "address_type", "phone", "url", "registration_date", "last_login_date");

x_session_unregister("users_search_condition");

if ($REQUEST_METHOD == "POST") {
#
# Update the session $search_data variable from $posted_data
#
	if (!empty($posted_data)) {
	
		if (preg_match("/^([A-Z])(-{0,1})(.*)$/", $posted_data["membership"], $found)) {
			$posted_data["usertype"] = $found[1];
			$posted_data["membership"] = $found[3];
		}
		else
			$posted_data["usertype"] = "";
		
		$need_advanced_options = false;
		foreach ($posted_data as $k=>$v) {
			if (!is_array($v) && !is_numeric($v))
				$posted_data[$k] = stripslashes($v);
			if (in_array($k, $advanced_options) and !empty($v))
				$need_advanced_options = true;
		}
		
		$posted_data["need_advanced_options"] = $need_advanced_options;

		if (empty($search_data["users"]["sort_field"])) {
			$posted_data["sort_field"] = "last_login";
			$posted_data["sort_direction"] = 1;
		}
		else {
			$posted_data["sort_field"] = $search_data["users"]["sort_field"];
			$posted_data["sort_direction"] = $search_data["users"]["sort_direction"];
		}
	
		if ($StartMonth) {
			$posted_data["start_date"] = mktime(0,0,0,$StartMonth,$StartDay,$StartYear);
			$posted_data["end_date"] = mktime(23,59,59,$EndMonth,$EndDay,$EndYear);
		}
		
		$search_data["users"] = $posted_data;

	}
	func_header_location("users.php?mode=search");
}


if ($mode == "search") {
#
# Perform search and display results
#

	$data = array();
	
#
# Prepare the search data
#
	if (!empty($sort) and in_array($sort, array("username","membership","news","name","email","usertype","last_login"))) {
		$search_data["users"]["sort_field"] = $sort;
		$search_data["users"]["sort_direction"] = abs(intval($search_data["users"]["sort_direction"]) - 1);
		$flag_save = true;
	}

    if (!empty($page) and $search_data["users"]["page"] != intval($page)) {
        # Store the current page number in the session
        $search_data["users"]["page"] = $page;
        $flag_save = true;
    }

    if ($flag_save)
        x_session_save("search_data");

	if (is_array($search_data["users"])) {
		$data = $search_data["users"];
		foreach ($data as $k=>$v)
			if (!is_array($v) && !is_numeric($v))
				$data[$k] = addslashes($v);
	}
	
	$condition = array();
	$search_condition = " WHERE 1";
	
	if (!empty($data["usertype"])) {
		# Search by usertype...
		$search_condition .= " AND usertype='".$data["usertype"]."'";

		if ($data["usertype"] == "C") {
			# Search by customer registration type...
			if ($data["registration_type"] == "1")
				$search_condition .= " AND login LIKE '".addslashes($anonymous_username_prefix)."%'";
			elseif ($data["registration_type"] == "2")
				$search_condition .= " AND login NOT LIKE '".addslashes($anonymous_username_prefix)."%'";
		}

	}

	if (!empty($data["membership"])) {
		# Search by membership...
		if (preg_match("/pending_membership/i", $data["membership"]))
			$search_condition .= " AND membership!=pending_membership";
		else
			$search_condition .= " AND membership='".$data["membership"]."' ";
	}
	
	if (!empty($data["substring"])) {
	
		# Search for substring in some fields...

		if (!empty($data["by_username"]))
			$condition[] = "login LIKE '%".$data["substring"]."%'";
		
		if (!empty($data["by_firstname"]))
			$condition[] = "firstname LIKE '%".$data["substring"]."%'";
		
		if (!empty($data["by_lastname"]))
			$condition[] = "lastname LIKE '%".$data["substring"]."%'";
		
		if (preg_match("/^(.+)(\s+)(.+)$/", $data["substring"], $found) and !empty($data["by_firstname"]) and !empty($data["by_lastname"])) {
			$condition[] = "firstname LIKE '%".$found[1]."%' AND lastname LIKE '%".$found[3]."%'";
		}
		
		if (!empty($data["by_email"]))
			$condition[] = "email LIKE '%".$data["substring"]."%'";
		
		if (!empty($data["by_company"]))
			$condition[] = "company LIKE '%".$data["substring"]."%'";
		
		$search_condition .= " AND (".implode(" OR ", $condition).")";
	}

	if (!empty($data["phone"])) {
		# Search by phone...
		$alt_phone = preg_replace("/[- ]/", "", $data["phone"]);
		$search_condition .= " AND (phone LIKE '%".$data["phone"]."%' OR phone LIKE '%$alt_phone%' OR fax LIKE '%".$data["phone"]."%' OR fax LIKE '%$alt_phone%')";
	}

	if (!empty($data["url"])) {
		# Search by web site url...
		$search_condition .= " AND url LIKE '%".$data["url"]."%'";
	}

	if (!empty($data["address_type"])) {
	
		# Search by address...
		
		if (!empty($data["city"]))
			$address_condition .= " AND PREFIX_city LIKE '%".$data["city"]."%'";
			
		if (!empty($data["state"]))
			$address_condition .= " AND PREFIX_state='".$data["state"]."'";
			
		if (!empty($data["country"]))
			$address_condition .= " AND PREFIX_country='".$data["country"]."'";

		if ($data["address_type"] == "B" || $data["address_type"] == "Both")
			$search_condition .= preg_replace("/AND PREFIX_(city|state|country)/", "AND b_\\1", $address_condition);
			
		if ($data["address_type"] == "S" || $data["address_type"] == "Both")
			$search_condition .= preg_replace("/AND PREFIX_(city|state|country)/", "AND s_\\1", $address_condition);

	}
	
	#
	# Search by first or/and last login date condition
	#
	$compare_date_fields = array();
	if (!empty($data["registration_date"]))
		$compare_date_fields[] = "first_login";
	
	if (!empty($data["last_login_date"]))
		$compare_date_fields[] = "last_login";

	if (!empty($compare_date_fields)) {
		$end_date = mktime();

		# ...dates within specified period
		if ($data["date_period"] == "C") {
			$start_date = $data["start_date"];
			$end_date = $data["end_date"];
		}
		# ...dates within this month
		else {
			if ($data["date_period"] == "M")
				$start_date = mktime(0,0,0,date("n",$end_date),1,date("Y",$end_date));
			elseif ($data["date_period"] == "D")
				$start_date = mktime(0,0,0,date("n",$end_date),date("j",$end_date),date("Y",$end_date));
			elseif ($data["date_period"] == "W") {
				$first_weekday = $end_date - (date("w",$end_date) * 86400);
				$start_date = mktime(0,0,0,date("n",$first_weekday),date("j",$first_weekday),date("Y",$first_weekday));
			}
				
		}
		foreach ($compare_date_fields as $k=>$v) {
			$search_condition .= " AND $sql_tbl[customers].$v>='$start_date'";
			$search_condition .= " AND $sql_tbl[customers].$v<='$end_date'";
		}
	}

	if (!empty($active_modules["Simple_Mode"])) {
		$search_condition .= " AND $sql_tbl[customers].usertype!='A'";
	}
	

	$sort_string = "";
	
	if (!empty($data["sort_field"])) {

		# Sort the search results...

		$direction = ($data["sort_direction"] ? "DESC" : "ASC");
		switch ($data["sort_field"]) {
			case "username":
				$sort_string = " ORDER BY login $direction";
				break;
			case "name":
				$sort_string = " ORDER BY lastname $direction, firstname $direction";
				break;
			case "usertype":
			case "email":
			case "membership":
			case "news":
			case "last_login":
				$sort_string = " ORDER BY ".$data["sort_field"]." $direction";
		}
	}

	#
	# Calculate the number of rows in the search results
	#
	$count_users = func_query_first_cell("SELECT COUNT(*) FROM $sql_tbl[customers]".$search_condition);
	
	$total_items = 0;

	if ($count_users > 0) {

		$page = $search_data["users"]["page"];
		
		#
		# Prepare the page navigation
		#
		$objects_per_page = $config["Appearance"]["users_per_page_admin"];

		$total_items = $count_users;

		$total_nav_pages = ceil($total_items/$objects_per_page)+1;

		include $xcart_dir."/include/navigation.php";
		
		#
		# Perform the SQL query and getting the search results
		#
		$users = func_query("SELECT * FROM $sql_tbl[customers]".$search_condition.$sort_string." LIMIT $first_page, $objects_per_page");

		x_session_register("users_search_condition", $search_condition);
		x_session_save("users_search_condition");
		
		if (is_array($users)) {
		#
		# Correct the search results...
		#
			foreach($users as $k=>$v) {
				if ($v["last_login"])
					$users[$k]["last_login"] += $config["General"]["timezone_offset"];
				$users[$k]["first_login"] += $config["General"]["timezone_offset"];
				if ($v["usertype"] == "P" and !$single_mode)
					$users[$k]["products"] = func_query_first_cell("SELECT COUNT(*) FROM $sql_tbl[products] WHERE provider='".addslashes($v["login"])."'");
			}
		}

		# Assign the Smarty variables
		$smarty->assign("navigation_script","users.php?mode=search");
		$smarty->assign("users",$users);
		$smarty->assign("first_item", $first_page+1);
		$smarty->assign("last_item", min($first_page+$objects_per_page, $total_items));
	}

	$smarty->assign("total_items",$total_items);
	$smarty->assign("mode", $mode);
	
} # /if ($mode == "search")

if (empty($users)) {
#
# Get the states and countries list for search form
#
	include $xcart_dir."/include/states.php";
	include $xcart_dir."/include/countries.php";
}

$smarty->assign("usertypes",$usertypes);

$smarty->assign("search_prefilled", $search_data["users"]);

#
# Assign Smarty variables and show template
#
$smarty->assign("main","users");

# Assign the current location line
$smarty->assign("location", $location);

# Assign the section navigation data
$smarty->assign("dialog_tools_data", $dialog_tools_data);

@include $xcart_dir."/modules/gold_display.php";
func_display("admin/home.tpl",$smarty);
?>