ORDER BY subquery?

[jacko310592]

hey guys, below i have my query, which does the following:

[*]Gets all accepted Friends from one table

[*]Cross-checks these friends with a main table (userdetails) to see if their online

 

my problem is that i need the results to be sorted by a last active time stamp ('lastActive' within 'userdetails')

 

$userInfoArr = mysql_query("SELECT SQL_CALC_FOUND_ROWS userId FROM {$loggedInUserId}_friends WHERE accepted=TRUE
	&& userId IN (SELECT userId lastActive FROM userdetails WHERE markedOnline=TRUE)
	ORDER BY lastActive IN (SELECT lastActive FROM userdetails) DESC
	LIMIT 0,6")or die(mysql_error());

 

 

my ORDER BY attempt within the above code just throws the following error:

Unknown column 'lastActive' in 'IN/ALL/ANY subquery'

 

can someone please point out where im going wrong?

thanks guys

[Mchl]

A separate table for each user? Smells like bad design...

 

Anyway

 

SELECT 
  SQL_CALC_FOUND_ROWS userId 
FROM 
  {$loggedInUserId}_friends AS uf
CROSS JOIN 
  userdetails AS ud
USING(userId)
WHERE 
  ud.accepted=TRUE
ORDER BY 
  ud.lastActive DESC
LIMIT 0,6

[jacko310592]

had to change it just a little bit:

$userInfoArr = mysql_query("SELECT SQL_CALC_FOUND_ROWS userId FROM {$loggedInUserId}_friends AS uf
	CROSS JOIN userdetails AS ud
	USING(userId)
	WHERE uf.accepted=TRUE
	&& ud.markedOnline=TRUE
	ORDER BY ud.lastActive DESC
	LIMIT 0,6")or die(mysql_error());

 

but it seems to be working fine, thank you.

 

 

and just on the "A separate table for each user? Smells like bad design...",

how would you suggest i do it rarther than having a table for each user's friends?

 

currently within the user's friend table there are 4 columns:

userId, accepted, blocked, date  (date being the time stamp of accepting friend request)

 

would this work as one overall table for all user's friends?

 

the reason i wanted a table for each user was just so a single table didnt get too large.

 

thanks again Mchl

[Mchl]

It's better to have a single, large table, than lots of small ones.

 

http://www.phpfreaks.com/blog/giuseppe-maxia-on-database-normalisation-and-smoking

[jacko310592]

thanks again, ill have a look at that link; and have a little think of a better table layout.

 

when my site is hosted, it will be done so on a Linux server, are there no limitations with the size for sql tables on linux? and arent large tables supposed to take longer in loading?

 

ive looked on other forums, but each seem to say differently

[Mchl]

Having separate table for each user means you have to open such table each time you use it. This hit's hard disk performane much worse than having one large table. There are of course limits on how large table can be, but don't expect to hit them anytime soon. Not until your site starts getting thousands of users.