sql help plz

[Adamthenewbie]

Hello,

I have a home made message board on my website that I want to add a feature that shows when the user registered and how many posts they have posted. (Like on the left side of this forum) I added a column to my users Database (Posts) This users database just has info on the user, ex: userid, password, etc. Is there some SQL statement that I could write to make it compare the userid in the users table with the userid in the messageboard table and then automatically put how many posts each person has??

Anyway not sure if you can do this. Anyhelp would be great.

Adam

[barand]

You don't need to hold the total posts, in fact its not a good idea to hold derived data. All you need is a join on the two tables

$sql = "SELECT user.name, COUNT(messages.userid) as posts
FROM user INNER JOIN messages on user.id = messages.userid
WHERE user.id = $id
GROUP BY user.name";

hth

[Adamthenewbie]

hello again. Ok here is what I have so far.

$sql = "SELECT userlogininformation.userid, COUNT(messageboard.userid) as postsGet
FROM userlogininformation INNER JOIN messageboard on userlogininformation.userid = messageboard.userid
WHERE userlogininformation.userid = 'adam' GROUP BY userlogininformation.userid";
$Result = mysql_query($sql);
echo $Result;

Its outputing

Resource id #3

I dont have an id in my table? So can I match on userid instead? Hope this makes sense

Thanks
Adam

[Sxooter]

You have two ways to do this. While barand's answer is the most comp sci compliant one, it may not be able to handle load well enough.

Aggregates can get expensive, fast, in a non-linear kind of way, as your dataset grows.

It's not uncommon for a count(fieldname) to cause a sequential scan of a table, and if that table is a message table, it's may take a while.

What I'd do is run a one time transaction that both adds up all the posts and inserts that number into the user info table, then adds triggers to message table that update the user info table everytime they post. If you do it all as a single transaction, it should stay correct after that. And if it doesn't., you still have the old data to derive the answer from.

but I'm thinking from a load handling perspective, since total number of posts isn't something that has to be absolutely accurate, it's ok if it gets out of sync while you're figuring out how to make the database do the updates.

[Sxooter]

Originally posted by Adamthenewbie
hello again. Ok here is what I have so far.

$sql = 
"SELECT 
  userlogininformation.userid,
COUNT(messageboard.userid)
  as postsGet 
FROM 
  userlogininformation 
INNER JOIN 
  messageboard on 
    (userlogininformation.userid = messageboard.userid)
WHERE 
  userlogininformation.userid = 'adam'	
GROUP BY 
  userlogininformation.userid"; 
$Result = mysql_query($sql);
echo $Result;

Its outputing

Resource id #3

I dont have an id in my table? So can I match on userid instead? Hope this makes sense

Nah, that's normal. You now need to cycle through your result set with mysql_fetch_array or something similar. Look over the example code in the PHP manual for that.

since you're on MySQL, what I'd suggest for performance reasons, is to update the total number of posts made everytime a message is posted. This means the aggregate to figure out the total is run when the user posts, not for every time he comes up.

Doing it for each read of a message is gonna cost a lot more, and result in a slow server under any kind of load. Just run something like this after each post:

update userinfo set msg_posted=(select count(messages.userid) from messages where messages.userid = $userid);

[Adamthenewbie]

I think I will do it so it updates a posts column in the users table each time the user posts a message. So basically I need to populate the posts column in the users table automatically somehow. It would take forever to do it manually. This is the SQL statement I have. I am just trying to show all the users that have posted and the number of posts they have posted. It is giving me an error saying

Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause

Here is my Query

SELECT userlogininformation.userid AS user, COUNT( messageboard.userid ) AS posts FROM messageboard INNER JOIN userlogininformation ON messageboard.userid = userlogininformation.userid GROUP BY ?????

I tried grouping by userlogininformation.userid = messageboard.userid. But it just returned one name and said that name posted every topic. Anyway I am a little confused.. Any help would be awesome

Thanks
Adam

[barand]

... GROUP BY user

should give total of posts for every user. After your mysql_query, do

while (list($user, $posts) = mysql_fetch_row($Result)) {
    echo " $user $posts <br>";
}

hth

[Adamthenewbie]

ok I have this Query

SELECT userlogininformation.userid AS user, COUNT( messageboard.userid ) AS posts FROM messageboard INNER JOIN userlogininformation ON messageboard.userid = userlogininformation.userid GROUP BY userlogininformation.userid

It works perfect. Shows me every user that has posted and how many times. Is there an easy way I can use UPDATE to automatically change the column POSTS in table userlogininformation?

Thanks for all the help thus far!!
Adam

[barand]

Add this query

while (list($user, $posts) = mysql_fetch_row($Result)) { 
    echo " $user $posts <br>"; 
    $sql = "update userlogininformation set posts = $posts
               where userid = '$user' ";
    mysql_query($sql);
}