Sorting list, isolating host in e-mail

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
DarkArchon
Forum Newbie
Posts: 8
Joined: Mon May 23, 2005 10:00 am

Sorting list, isolating host in e-mail

Post by DarkArchon »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


Ok, setup...

I'm using PHPBB2 and have added the Advanced User Search ([url=http://www.phpbb.com/phpBB/viewtopic.php?t=241289]Click for topic on script[/url]). Well I'm trying to add new sort options for the output that aren't normally availible, such as sorting by enabled/disabled accounts. This is simple, even if I wasn't framilar with PHP I could follow the examples of the others (such as sort by username or e-mail).

One thing that I'd -really- like to have is sorting by e-mail host, for example, bring all the @gmail.com to the top, then all the @yahoo.com and on down the alphabetical list. I'm having trouble figuring out how to isolate the host though. I was hoping someone had a suggestion. Here's bits of the normal sorting for e-mail.

Code: Select all

switch(strtolower($HTTP_GET_VARS['sort']))
	{
		case 'user_email':
			$sort = 'user_email';
			
			$select_sql .= "u.user_email";
			break;
	}

Code: Select all

'U_EMAIL' => ( ( $sort == 'user_email' ) ? append_sid("$base_url&sort=$sort&order=$o_order") : append_sid("$base_url&sort=user_email&order=$order") ),
Hopefully that'll give you an idea of the method of sorting that's being used. Pretty much everything else that needs to be added is display related and I can fix that. Thanks in advance for any help you guys can give me.


feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Code: Select all

substring_index(user_email, '@', -1)
is for MySQL.
DarkArchon
Forum Newbie
Posts: 8
Joined: Mon May 23, 2005 10:00 am

Post by DarkArchon »

Thanks for the help, but please pardon my noobishness, but I can't seem to figure out where that goes. Seems to destroy the page when I impliment it. Can you give me a bit more of a hint on how that would fit into my code?
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

If I may take a crack at this one... (I'm no MySQL god)

It would go in the GROUP BY clause of the SQL statement. Right?
DarkArchon
Forum Newbie
Posts: 8
Joined: Mon May 23, 2005 10:00 am

Post by DarkArchon »

ummmm... you may or may not be right, but I can't tell so it doesn't help me :/ sorry.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Well.. follow the trail $sort takes. :)
DarkArchon
Forum Newbie
Posts: 8
Joined: Mon May 23, 2005 10:00 am

Post by DarkArchon »

$sort appears 25 times.

7 times, one for each item being sorted, from my first php example.
14 times, 2 for each of the URL creater, from my second example.
4 more times shown below (/me uses PHP code tags this time ;) :P )

Code: Select all

// this one appears right after my last URL creater (for lack of a better term)
'S_POST_ACTION' => append_sid("$base_url&sort=$sort&order=$order")

// these appear somewhere between my first and second examples (the two are pretty close, not that that really matters)
	if($page > 1)
	{
		$pagination .= '<a href="'.append_sid("$base_url&sort=$sort&order=$order&page=".($page - 1)).'">'.$lang['Previous'].'</a>';
	}

	if($page < $num_pages)
	{
		$pagination .= ( $pagination == '' ) ? '<a href="'.append_sid("$base_url&sort=$sort&order=$order&page=".($page + 1)).'">'.$lang['Next'].'</a>' : ' | <a href="'.append_sid("$base_url&sort=$sort&order=$order&page=".($page + 1)).'">'.$lang['Next'].'</a>';
	}
4 files being used for this script, those are the only referances to $sort, and none appear to have anything to do with a GROUP BY clause, which is why Huggins post confused me a bit.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Keep following the paths... like where $base_url goes....
DarkArchon
Forum Newbie
Posts: 8
Joined: Mon May 23, 2005 10:00 am

Post by DarkArchon »

ok, followed that path and got nowhere, picked another one and found this.

Code: Select all

$select_sql .= ", ".GROUPS_TABLE." AS g, ".USER_GROUP_TABLE." AS ug, ".AUTH_ACCESS_TABLE." AS aa
								WHERE u.user_id = ug.user_id
									AND ug.group_id = g.group_id
									AND	g.group_id = aa.group_id
									AND aa.forum_id = ". $moderators_forum ."
									AND aa.auth_mod = 1
									AND u.user_id <> ".ANONYMOUS."
								GROUP BY u.user_id, u.username, u.user_email, u.user_posts, u.user_regdate, u.user_level, u.user_active, u.user_lastvisit";			
			break;
At this point I feel like a noob (not that I'm exactly experianced to begin with) b/c I don't see where this is going. Taking a stab in the dark though and keeping in mind Huggins "go in the GROUP BY clause" could I like...

Code: Select all

GROUP BY u.user_id, u.username, u.user_email, u.user_posts, u.user_regdate, u.user_level, u.user_active, u.user_lastvisit, substring_index(user_email, '@', -1)";
Ok, assuming that's right, which it probably isn't, would I just use substring_index in the rest of the script? or like u.substring_index? Everything I do seems to break the script (which is usually a pretty handy sign I'm doing something wrong huh?)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The result information from substring_index() won't be available in your result set. If you want it apart of the result set it must be in the SELECT part of the query.

You want to sort solely first by their email address, correct? If so, it must be the first term in the ORDER BY clause.
DarkArchon
Forum Newbie
Posts: 8
Joined: Mon May 23, 2005 10:00 am

Post by DarkArchon »

hummm... I don't think you see quite what I want to do, which may be a cause of some of the confusion. The e-mail is already part of the search results, I don't want it sorted by that solely or to start off with. Here's what I'm trying to do (due to large pixle count I'm linking the picture, I tried to keep the filesize to a minimum though)

http://www.orderofatlas.com/assets/forum.gif

Along the top there is a Sort Options menu. I've already added Active/Disabled Users, and you can see where I want to add the E-mail host option (it's photoshop'ed in right now). Currently the menu is sorted by username (edited out for obvious reasons). If I click sort by e-mail it will sort by their e-mail in the following order [first]@[second].[third] like any good alphabatizer would. If I were to ckick sort by e-mail host the entire list will change and in the section I took a pic of the users will be orderd by their e-mail hosts, as follows [third]@[first].[second] basically starting the alphabitation after the @ instead of with the first character in the string. the list would then look like...

*@163.com
*@cashette.com
*@gmail.com
*@hanmail.net
*@hotmail.com
*@hotmail.com
*@imail.blackgold.ab.ca
*@mybulelight.com
*@naver.com
*@web.de
*@yahoo.com
etc
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The query previously posted is using GROUP BY, not ORDER BY. These are very different things.

To perform the sort on host like you wish, it would be an ORDER BY with the substring_index() call followed immediately by user_email.
Post Reply