Can you help me to solve a php problem?

[ivanella]

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...

[Distant_storm]

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

[BenInBlack]

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

[ivanella]

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....

[ivanella]

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....

[BenInBlack]

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

";