...

View Full Version : Gouping user by id



graham23s
07-29-2007, 07:57 PM
Hi Guys,

i have a table in mysql that has basically 3 fields

id
user_id
user_profile_id

id = just the standard auto increment field
user_id = the id of the user posting a comment
user_profile_id = the id of the user whom profile is getting posted on

say i'm posting on daves profile it would be:

id = 1
user_id = 6 <-- my id
user_profile_id = 12 <-- daves id

what i wanted to do is count the id's and display in a table the 10 most active posters, say if i had posted on 110 profiles it would be like:

Top Posters

6 (aka graham) posted on X profiles

im not sure of the exact syntax this would take i have done others for users etc for example:


$query3 = "SELECT `country`, COUNT(*) AS `count` FROM `membership` GROUP BY `country` ORDER BY `count` DESC LIMIT 10";
$result3 = mysql_query($query3);

echo '<table width="60%" border="1" bordercolor="#000000" cellpadding="2" cellspacing="0" />
<tr>
<th bgcolor="#004E98" colspan="2"><font color="#ffffff">Top 10 Countries</font></th>
</tr>
<tr>
<td width="30%" bgcolor="#004E98" align="center"><font color="#ffffff"><b>Country</b></font></td><td width="30%" bgcolor="#004E98" align="center"><font color="#ffffff"><b>Number Of Members</b></font></td>
</tr>';

while ($row = mysql_fetch_array($result3)) {

// get the usernames clickable.../////////////////////////////////////////////

$top_10_files = $row['country'];
$count_country = $row['count'];
$id2 = $row['id'];

$query4 = "SELECT * FROM `countries` WHERE `id`='$top_10_files'";
$result4 = mysql_query($query4);

$row_flags = mysql_fetch_array($result4);

$flag_pics = $row_flags['flagpic'];
$name = $row_flags['name'];

echo "<tr><td align=\"center\"><img src=\"flags/$flag_pics\" alt=\"$name\"></a></td><td align=\"center\">$count_country</td></tr>";

}

echo "</table><br /><br />";

####################################################################################

any help would be appreciated

Graham

Fumigator
07-30-2007, 02:00 AM
The code you posted already has the query you can use for this (first line of code). I recommend you do NOT store the top ten posters in a table-- you should simply use a query each time you need that information.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum