order by - using two different tables

[liamoco]

I want to list all users by name but also by status for example

 

Bob Charles - Online

Bob Jones - Online

Bob Back - Away

 

but my names and status are in two different tables

 

I order my names using ORDER BY fname, lname ASC // table name = users

I order my status using ORDER BY FIELD(status, 'online', 'away', 'busy') //table name = user_info

 

How can I do this?

 

Thanks

[liamoco]

How can I do this without putting the data into the same table?

[fenway]

You mean a JOIN?

[liamoco]

yes, this is what I have tried, not quite working, cannot get the join working, the field name status is from a different table called user_assign

 

$query_online = mysql_query("SELECT id, fname, mname, lname FROM users JOIN chat_assign ON status WHERE id in ('" . implode("', '", $friend_id_array) . "')  ORDER BY status, fname, lname");

 

[liamoco]

Sorry I forgot to post the error that is shown when using the query above.

 

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given in J:......................\functions.php on line 164

 

[liamoco]

solved with this...

 

$query_online = mysql_query("SELECT users.fname, users.mname, users.lname, chat_assign.status 
						FROM users, chat_assign
						WHERE (users.id = chat_assign.user_id) AND users.id in ('" . implode("', '", $friend_id_array) . "')
						ORDER BY FIELD(chat_assign.status, 'online', 'busy', 'away'), users.fname, users.lname
						");