MySQL/PHP Help [ordering distinctly]

[marcus]

Here's my code:

 

echo "<table border=0 cellspacing=3 cellpadding=3 width=400>\n";
echo "<tr><td colspan=2 align=center class=bar><font class=barfont>OMG CHART</font></td></tr>\n";

$sql = "SELECT DISTINCT `uid` FROM `trophies` LIMIT 10";
$res = mysql_query($sql) or die(mysql_error());


while($row = mysql_fetch_assoc($res)){
$sql2 = "SELECT COUNT(DISTINCT `id`) AS `counted` FROM `trophies` WHERE `uid`=$row[uid]";
$res2 = mysql_query($sql2) or die(mysql_error());
$row1 = mysql_fetch_assoc($res2);
$num = mysql_num_rows($res2);
$row2 = mysql_fetch_assoc(mysql_query("SELECT * FROM `users` WHERE `uid`=$row[uid]"));
echo "<tr><td align=center>$row2[username]</td><td align=center>".mysql_result($res2,counted)."</td></tr>\n";
}
echo "</table>\n";

 

What I'm trying to do is order the "$sql2" query by how many trophies they have. I can't seem to do that, I've tried; ORDER BY COUNT(DISTINCT `id`) [that didn't work]

 

Without ordering I get something like:

 

angela 3

kim 3

Sparts 4

egle 3

blue_flame_2k6 3

hxcChaos 5

jason 3

sadie_bledsoe 3

Chad 3

marcus 5

 

All randomly placed, I want it to order ascending. How can I do this?

[teng84]

simply add order by from the query where you are viewing those name

[marcus]

That makes no sense to me [iMO]. Explain further.

[teng84]

sample

SELECT * FROM `users` WHERE `uid`=$row[uid] order by name asc

or

SELECT * FROM `users` WHERE `uid`=$row[uid] order by name desc

[marcus]

I'm trying to order it by the amount of trophies they have... not by their name.

[teng84]

that was the idea you can apply them on any case

 

SELECT COUNT(DISTINCT `id`) AS `counted` FROM `trophies` WHERE `uid`=$row[uid]

oder by the field name you want

[marcus]

You're getting no where with me, the amount of trophies, I know how to ORDER by a specific field name, but while trying to order from the AMOUNT of something is completely different.

[Yesideez]

Whichever field you have as the number of trophies simply order by that.

 

How is this number of trohies stored?

[teng84]

you place those query inside the loop so what else do you expect

[Yesideez]

Any chance you can show the structure of the trophies table?

[marcus]

Sure thing.

 

  `id` int(11) NOT NULL auto_increment,
  `uid` int(11) NOT NULL default '0',
  `place` int(11) NOT NULL default '0',
  `score` int(255) NOT NULL default '0',
  `game` varchar(255) NOT NULL default '',
  `src` varchar(255) NOT NULL default '',
  `text` text NOT NULL,
  PRIMARY KEY  (`id`)

[teng84]

SELECT distinct(nick), count(distinct(nick))FROM `PHPAUCTION_users` group by nick order by nick

 

that another sample that i query from my db maybe thats better that doing those repeated query use that as prototype

[Yesideez]

Now from that how do you store the total number of trophies? I'm presuming that uid is the user's ID number?

[marcus]

That's not going to help me... the only reason I call the user query is to get the username.

 

 

Now from that how do you store the total number of trophies? I'm presuming that uid is the user's ID number?

 

Each trophy is stored INDIVIDUALLY. Each row is unique, so if I have 5 trophies, I would have 5 rows in the database.

[Yesideez]

SELECT COUNT(DISTINCT `id`) AS `counted` FROM `trophies` WHERE `uid`=$row[uid] ORDER BY `counted`

 

Would that work?

 

Never done a query sorting data like that as I've organised my databases very differently!

[marcus]

That wouldn't work because that would ORDERING it by that number, not by a field in the database.

[Yesideez]

Yeah I can see that now I look at your code a little more - was just about to say it won't work and you beat me to it!

[Yesideez]

I'm a little confused here as you've got your `id` field in the `trophies` table set to auto_increment so as you enter a trophy the number will increment by one.

 

Yet with your query to sort the trophy scores you seem to be going by that same field.

 

Shouldn't there be a `score` field or something instead?

[marcus]

We don't use scores, trophies are given at a certain score, but no score is recorded.

 

I'm counting the unique IDs PER user