Query error...help me!

[ivanella]

Hi guys i have a problem:

 

in my DATABASE i have 3 tables

 

TABLE 1

 

name : "consigli_User"

 

fields : "UserID" and "Name"

 

TABLE 2

 

name : "consigli_Discussion "

 

fields : "AuthUserID" and "CommentID"

 

TABLE 3

 

name  : "consigli_Discussion "

 

fields : "UserID" and "CommentID"

 

i execute the script:

 

$sql = "SELECT UserID,Name,AuthUserID,CommentID,count(*) AS numero_commenti

FROM consigli_User AS u JOIN consigli_Comment AS c JOIN consigli_ThankfulPeople AS v

ON u.UserID=c.AuthUserID

AND c.AuthUserID=v.UserID

AND c.CommentID=v.CommentID

GROUP BY numero_commenti

ORDER BY numero_commenti DESC";

 

$res=mysql_query($sql) or die ("Query non valida: $sql");

 

while ($row = mysql_fetch_row($res)) {

  echo 'ID: ', $row[0] , ' Nome: ', $row[1] , "\n";

}

 

I need to list the user with the numbers of "thanks" recived to their coments by there is an error:

 

Query error: SELECT UserID,Name,AuthUserID,CommentID,count(*) AS numero_commenti FROM consigli_User AS u JOIN consigli_Comment AS c JOIN consigli_ThankfulPeople AS v ON u.UserID=c.AuthUserID AND c.AuthUserID=v.UserID AND c.CommentID=v.CommentID GROUP BY numero_commenti ORDER BY numero_commenti DESC

 

please help me...

[ivanella]

sorry i made a mistake...

 

TABLE 3

 

nome tabella: "consigli_ThankfulPeople"

 

[LordOrange]

can you post the whole script

[ivanella]

this is the woole code....

 

I tried with this code:

 

ho modificato il codice in:

 

$sql = "SELECT UserID,Name,AuthUserID,CommentID,count(*) AS numero_commenti 
FROM consigli_User AS u JOIN consigli_Comment AS c JOIN consigli_ThankfulPeople AS v 
ON u.UserID=c.AuthUserID 
AND c.AuthUserID=v.UserID 
AND c.CommentID=v.CommentID 
GROUP BY numero_commenti 
ORDER BY numero_commenti DESC"; 

$res=mysql_query($sql) or die ("Query non valida: $sql <br>Errore: ".mysql_error()); 

while ($row = mysql_fetch_row($res)) { 
  echo 'ID: ', $row[0] , ' Nome: ', $row[1] , "\n"; 
}

 

and i have this error:

 

Query non valida: SELECT UserID,Name,AuthUserID,CommentID,count(*) AS numero_commenti FROM consigli_User AS u JOIN consigli_Comment AS c JOIN consigli_ThankfulPeople AS v ON u.UserID=c.AuthUserID AND c.AuthUserID=v.UserID AND c.CommentID=v.CommentID GROUP BY numero_commenti ORDER BY numero_commenti DESC

Errore: Column 'UserID' in field list is ambiguous

 

help?????

[ivanella]

I correct the code:

 

and now the script work....

 

$sql = "SELECT u.UserID,Name,AuthUserID,c.CommentID,count(*) AS numero_commenti 
FROM consigli_User AS u JOIN consigli_Comment AS c JOIN consigli_ThankfulPeople AS v 
ON u.UserID=c.AuthUserID 
AND c.AuthUserID=v.UserID 
AND c.CommentID=v.CommentID 
GROUP BY numero_commenti 
ORDER BY numero_commenti DESC"; 

$res=mysql_query($sql) or die ("Query non valida: $sql <br>Errore: ".mysql_error()); 

 

but now, how can i show the list of all User showing:

 

UserID, Name, numero_commenti

 

thanks for help....

 

 

[ivanella]

I tried in this way:

 

echo '   
<table border="1">   ';
  
while ($row = mysql_fetch_assoc($res)) {   
    $userID     = htmlentities($row['userID']);   
    $Name     = htmlentities($row['Name']);   
    $numero_commenti = htmlentities($row['numero_commenti']);   
  
  
    echo "<tr>   
            <td>$userID</td>   
            <td>$Name</td>   
            <td>$numero_commenti</td>   
          </tr>";   
}   
  
echo '</table>';