Hi,

before I begin I wanted to let you know that I do not have a lot of SQL-experience, yet.

I got this query:


Code:
$requests_q = mysql_query("
	
	SELECT COUNT (*) FROM (
		SELECT count(*) AS cnt, ava_users.*, 0 AS ord
		      FROM ava_friend_requests
		      LEFT JOIN ava_users 
		      ON ava_friend_requests.from_user = ava_users.id
		      WHERE ava_friend_requests.to_user = $user[id]
		      
		      UNION ALL
		      
		SELECT count(*), ava_users.*, 1 AS ord
		      FROM ava_friends
		      LEFT JOIN ava_users 
		      ON ava_friends.user2 = ava_users.id
		      WHERE ava_friends.user1 = $user[id]
		)
		
	GROUP BY ava_users.id
	ORDER BY ord
	LIMIT $from, $display_num");
What I'd like to know is how do you get a count for a SINGLE query (in this case the top one) when you're using UNION ALL? Also I'd like to know how do you get the complete number of rows when you're using a UNION ALL?

The code I posted above has bugs. I know that because in my sample database there a 6 entries and it shows me 5. I need a UNION because it is important for a pagination system.

Please - any help? Thank you so much!