The SitePoint Forums have moved.

You can now find them here.
This forum is now closed to new posts, but you can browse existing content.
You can find out more information about the move and how to open a new account (if necessary) here.
If you get stuck you can get support by emailing forums@sitepoint.com

If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

choose most active member based on 4 tables

Hiya, not really sure how to explain this so if it doesn't make sense tell me What I want to do is display the member of the month on my homepage. This will be based on the member who has been most active throughout the previous month. I am aiming to measure activity by creating a select query that counts the number of times a member ID occurs in 4 different tables. The member with the highest count will have their details displayed in a section on the homepage.

What I need help with is how would I write a query to count mID over 4 tables for each member and select the member with the highest number so I can query the user table to obtain their details.

I don't have access to test this at work, but here is something I wrote about 5 minutes ago I realise this is incorrect but it may contain some of the required elements?

I am not sure the date part of this function will work, but I think you get the general ideal here. You said member of the month so you need to use some type of time constraint. This is untested but should work. The return will be an array with a member ID and the count, you should be able to get the avatar with the user ID. Please note also for brevity sake I left out the code to open your databases, close them and release the recordset's.

Computers and Fire ...
In the hands of the inexperienced or uneducated,
the results can be disastrous.
While the professional can tame, master even conquer.

SELECT tbluser.uID
, tbluser.uName
, tbluser.otherstuff
, D2.total
FROM tbluser
INNER
JOIN (
SELECT uID
, sum(subtotal) as total
FROM (
SELECT uID,count(*) as subtotal
FROM tblgallery GROUP BY uID
UNION ALL
SELECT uID,count(*) as subtotal
FROM tbllogs GROUP BY uID
UNION ALL
SELECT uID,count(*) as subtotal
FROM tblfthread GROUP BY uID
UNION ALL
SELECT uID,count(*) as subtotal
FROM tblfcomment GROUP BY uID
) as D
GROUP BY uID
) as D2
ON D2.uID = tbluser.uID
ORDER BY total DESC LIMIT 1

Thanx for that query code but I can't get it to work. I have never used joins before and am not sure where the D2.total comes from? This is what I ended up with

PHP Code:

$sql = mysql_query("SELECT tbluser.uID
, tbluser.uusername
, D2.total
FROM tbluser
INNER
JOIN (
SELECT uID
, sum(subtotal) as total
FROM (
SELECT uID,count(*) as subtotal
FROM tblgallery GROUP BY uID
UNION ALL
SELECT uID,count(*) as subtotal
FROM tbllogs GROUP BY uID
UNION ALL
SELECT uID,count(*) as subtotal
FROM tblfthread GROUP BY uID
UNION ALL
SELECT uID,count(*) as subtotal
FROM tblfcomment GROUP BY uID
) as D
GROUP BY uID
) as D2
ON D2.uID = tbluser.uID
ORDER BY total DESC LIMIT 1");
$row = mysql_fetch_array($sql);
$uusername = $row['uusername'];

echo 'username = '.$uusername;

When I use this I get an error

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\wamp\www\site\index.php on line 75

EDITI made a typo on one of my table names which was causing the problem. The code now works perfectly. Thanks

Hiya, sorry I have not been back to this thread sooner, I have been working on other projects. I ran the code in MySQL and this is the error I got:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$sql = mysql_query("SELECT tbluser.uID
, tbluser.uusername
, D2.tot' at line 1

As far as I can tell, the code looks fine to me. Please can you have a look at my code below.

PHP Code:

$sql = mysql_query("SELECT tbluser.uID
, tbluser.uusername
, D2.total
FROM tbluser
INNER
JOIN (
SELECT uID
, sum(subtotal) as total
FROM (
SELECT uID,count(*) as subtotal
FROM tblgallery GROUP BY uID
UNION ALL
SELECT uID,count(*) as subtotal
FROM tbllogs GROUP BY uID
UNION ALL
SELECT uID,count(*) as subtotal
FROM tblfthread GROUP BY uID
UNION ALL
SELECT uID,count(*) as subtotal
FROM tblfcomments GROUP BY uID
) as D2
GROUP BY uID
) as D2
ON D2.uID = tbluser.uID
ORDER BY total DESC LIMIT 1");
$row = mysql_fetch_array($sql);
$uusername = $row['uusername'];