ivanella Posted November 23, 2007 Share Posted November 23, 2007 Hi i'm italian so i don't speak english well... I must do a script... but i'm not expert... The php script has to crate a list of my site members showing the fields "ID" and "NAME" of "USER" table in DATABASE. It's difficult becausa the script has to show for each user the number of votes. The number of votes has to be calculated for each user counting the number of ROWS in table "VOTI" that contain in the field "UserID" the "ID" of user. I know, it's difficult, but i hope there is someone that can help me.... Thanks... Quote Link to comment Share on other sites More sharing options...
Distant_storm Posted November 24, 2007 Share Posted November 24, 2007 It would be easier to make a field called votes on table when person votes for user check if user already voted for if so add 1 to votes field in table else create new entry into table with vote default as 1 Saves alot of processing Quote Link to comment Share on other sites More sharing options...
BenInBlack Posted November 24, 2007 Share Posted November 24, 2007 for one thing I would do this in SQL SELECT USER.ID as UserID, max(USER.NAME) as UserName, count(USER.ID) as VoteCount FROM USER, VOTI WHERE USER.ID = VOTI.UserID GROUP BY USER.ID now it is just a simple display dump in PHP Quote Link to comment Share on other sites More sharing options...
ivanella Posted November 24, 2007 Author Share Posted November 24, 2007 first of all thanks for help.... I didn't explain well what i need..... because is so comlex for me.... In DATABASE i have: table "USER" with ures information like "ID" and "NOME" table "VOTI" with field like "COMMENTOID" and "USERID" that say wich user has voted for the comment. table "COMMENTI" with field "COMMENTOID" e "USERID" that say wich user has insert the comment. The Script has to take "ID" and "NOME" for each member from table "USER", see from table "COMMENTI" what are the comments the user has insert and find from table "VOTI" how many times a comment insert from him has been voted. Then create a list of user whit the number of votes he recived to his comments.... It's so difficoult ...help me please.... Quote Link to comment Share on other sites More sharing options...
ivanella Posted November 24, 2007 Author Share Posted November 24, 2007 I tried this Query: $sql = "SELECT UserID ,Name,count(*) AS numero_commenti FROM consigli_User AS u JOIN consigli_Comment AS c JOIN consigli_ThankfulPeople AS v ON u.UserID=c.UserID AND c.UserID=v.UserID AND c.CommentID=v.CommentID GROUP BY Name ORDER BY numero_commenti DESC "; while ($row = mysql_fetch_row($sql)) { echo 'ID: ', $row[0] , ' Nome: ', $row[1] , "\n"; } but I recive this error: mysql_fetch_row(): supplied argument is not a valid MySQL result can you help me? thanks.... Quote Link to comment Share on other sites More sharing options...
BenInBlack Posted November 24, 2007 Share Posted November 24, 2007 GROUP BY must contain all the column names that are NOT aggregated so I suggest that since UserID and Name should be 1 to 1 that you do this $sql = "SELECT UserID ,max(Name) AS UserName,count(*) AS numero_commenti FROM consigli_User AS u JOIN consigli_Comment AS c JOIN consigli_ThankfulPeople AS v ON u.UserID=c.UserID AND c.UserID=v.UserID AND c.CommentID=v.CommentID GROUP BY UserID ORDER BY numero_commenti DESC "; Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.