Hi,

I have a messages table and a friends table and I am using the following queriers to display the number of messages and buddy requests for the user that is currently logged in.

Since the userid is the same in the where clause in both queries should they be joined? I can join basic tables but I'm not sure how to join these tables correctly, can anyone show me how they would do it?

Any help will be appreciated

PHP Code:

<?
// Get number of messages
$messagesNum= mysql_num_rows(mysql_query("SELECT userto FROM messages WHERE userto = '$_SESSION[userid]' and status='unread'"));
 
// Get number of friend requests
$buddyrequests= mysql_num_rows(mysql_query("SELECT * FROM friends WHERE friend1='$_SESSION[userid]' and status='pending'"));

I don't know much of mySql, I will give you the code for MS SQL you can convert it to mySql

Select M.userto from Messages M Join Friends F ON M.userto=F.friend1
Where M.userto='$_SESSION[userid]' and M.status='unread'
AND F.status='pending'

If you're justing wanting to display counts, there's no reason you should be loading all that data with your queries. But I wouldn't make one query out of it, since you're just counting from two tables, a join would be rather inefficient I would think.

Try this:

"SELECT count(status) AS thecount FROM messages WHERE userto = '$_SESSION[userid]' and status='unread'"

Let the database do the heavylifting for you, then you just have to read one variable =)

Thanks