[RESOLVED] removing results with certain value and sorting (nested query is preventing)

[schwim]

Hi there everyone,

I'm writing a "users online" block for a phpbb3 based site and have run into an issue.

I want to strip user_type 2 from all results and I want to sort by username. The problem I'm having is that both of these values are retrieved from a nested query.

$sql="SELECT session_user_id,session_time FROM phpbb_sessions WHERE session_user_id != 1 AND session_time > $session_time ORDER BY session_time DESC";;
$result1=mysql_query($sql) or die (mysql_error());
$numr=mysql_num_rows($result1);
if($numr>0){
	while($rows=mysql_fetch_array($result1)){
		$user_id = $rows['session_user_id'];

	$sql="SELECT username,user_type FROM phpbb_users WHERE user_id='$user_id'";;
	$result2=mysql_query($sql) or die (mysql_error());
	while($rows=mysql_fetch_array($result2)){
		$username=$rows['username'];
		$user_type=$rows['user_type'];
	}

	echo("".$username."<br>");
}
}

To explain, the session table contains only the user_id as an identifying value. I retrieved the user_id from the session table, then in the while loop, grabbed the username and user_type for each user_id.

My problem is that I can't really strip all results with a user_type of 2 and I can't sort by username, simply because of the nested nature of that query.

Could someone help me figure out how to alter my queries to allow both the sorting by username and the stripping of user_type 2?

thanks,
json

[zypher11]

SELECT s.session_user_id, s.session_time, u.username, u.user_type FROM phpbb_sessions s INNER JOIN phpbb_users u ON u.user_id = s.session_user_id WHERE session_user_id <> 1 AND session_time > $session_time AND u.user_type <> 2 ORDER BY u.username ASC

see how that works for ya

[schwim]

Hi there zypher and thanks so much for your help. It seems to be working fine except for one aspect:

It's sorting the username in alphabetical order, but also by case:

Alan
Bob
Charley
andrew
billy
candace

Is how it's currently sorting. how might I get it to ignore the case during the sort?

thanks,
json

[zypher11]

Did you try just ordering by u.username? If that don't work try ORDER BY LOWER(u.username) ASC

[schwim]

LOWER() worked fantastically, thanks so much!

[bradgrafelman]

Note that by doing something like ORDER BY LOWER(col), you'll forfeit the use of any indexes which can significantly increase the query execution time.

Instead, if case doesn't matter to you, then you should switch to using a case-insensitive character collation (e.g. one that ends with "_ci").