...

View Full Version : Retrieving a list of values form a database but only displaying once



newmand2
10-14-2004, 09:20 AM
This is a hypothetical, unfortunately I have no code to show just yet, but I'm trying to think of a solution of how to tackle the problem, rather than just get some code off you guys

Lets say I have a table, Table A...


Table A:
| A | NextID | UserID |
+---+--------+--------+
| 1 | 1 | 45 |
| 2 | 1 | 32 |
| 3 | 2 | 12 |
| 4 | 2 | 76 |
| 5 | 1 | 76 |


What I now want to do is to list the User's ID and the number of times they are represented in this table, like so:

UserID: 76, Listed: 2
UserID: 45, Listed: 1

And so on

Any ideas? 'preciated

raf
10-14-2004, 11:43 AM
SELECT UserID, COUNT(*) AS numtimes FROM tableA GROUP BY UserID

newmand2
10-14-2004, 02:07 PM
hmmmpft... thanks for the reply, but this simply returns the 'resource id #47'


echo(mysql_query("SELECT UserID, COUNT(*) AS numtimes FROM TableA GROUP BY UserID"));

what does this mean? thanks again

raf
10-14-2004, 02:18 PM
hmmmpft... thanks for the reply, but this simply returns the 'resource id #47'


echo(mysql_query("SELECT UserID, COUNT(*) AS numtimes FROM TableA GROUP BY UserID"));

what does this mean? thanks again

it returns a resultset with a specific "resource id" (47 in your case). You can then acces the resultset (the records you actually want) by using the resource-id as a parameter to get an element of the resultset. Like this:


$sql = "SELECT UserID, COUNT(*) AS numtimes FROM TableA GROUP BY UserID";
$result = mysql_query($sql) or die ('Queryproblem getting the counts');
if (mysql_num_rows($result) >= 1){
echo 'Count per user<ul>';
while ($row = mysql_fetch_assoc($result)){
echo '<li>UserID: '. $row['UserID'] .', listed: ' . $row['numtimes'] . '</li>';
}
echo '</ul>';
}else{
echo 'No records found.';
}

newmand2
10-14-2004, 02:41 PM
right - got that sorted... but how could I sort the rows by the frequency, turns out I can't have ORDER BY COUNT(*) in the same sql statement?

raf
10-14-2004, 06:41 PM
You need to use an "ORDER BY" clause.
try:

$sql = "SELECT UserID, COUNT(*) AS numtimes FROM TableA GROUP BY UserID ORDER BY numtimes DESC";

which will sort them from the user with the highest count to the one ith the lowest count.

newmand2
10-14-2004, 07:13 PM
ahhh thanks so much, thats been bugging me all day :)

I was using ORDER BY UserID :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum