mysql - sorting and calling help

[Tekton]

Basically what I want to do is get the top 7 people with the most 'gold'.

This gives me the top person, but I need a way to get all 7. Any help? Thanks.

PHP Code:

$temp = $DB_site->query_first("SELECT username, gold FROM ".TABLE_PREFIX."user ORDER BY gold DESC LIMIT 7 ");
$top_one = $temp[username];
$top_one2 = $temp[gold]; 


[CarCdr]

Use query rather than query_first, then loop over the results The class DB_site offers the function query_first as a shortcut for a) regular query, b) free mysql result storage, c) return the single row.

[Colin F]

Originally Posted by Tekton

Basically what I want to do is get the top 7 people with the most 'gold'.

This gives me the top person, but I need a way to get all 7. Any help? Thanks.

PHP Code:

$temp = $DB_site->query_first("SELECT username, gold FROM ".TABLE_PREFIX."user ORDER BY gold DESC LIMIT 7 ");
$top_one = $temp[username];
$top_one2 = $temp[gold]; 


You might also make that "LIMIT 0, 6"

[CarCdr]

Originally Posted by Colin F

You might also make that "LIMIT 0, 6"

What would that achieve Colin? That would return the first 6 rows. LIMIT 0,6 is equivalent to LIMIT 6.

[Colin F]

Originally Posted by CarCdr

What would that achieve Colin? That would return the first 6 rows. LIMIT 0,6 is equivalent to LIMIT 6.

really?

I would have thought LIMIT 0,6 would return rows 0 through 6, making the first seven rows. Good to have learned something

[Dean C]

Yes Colin, I'm pretty sure what you said is right. LIMIT 0,6 would return the first 7 seven rows.

[Tekton]

Can I get a quick example on how to loop the results, and store them each in SEPERATE variables? Thanks. ^__^;;

[Colin F]

PHP Code:

$winners = $DB_site->query("SELECT username, gold FROM ".TABLE_PREFIX."user ORDER BY gold DESC LIMIT 7 "); 
while ($winner = $DB_site->fetch_array($winners))
{
$id++;
$uname = "name_".$id;
$ugold = "gold_".$id;
${$uname} = $winner[username]; 
${$ugold} = $winner[gold];
} 


That should work. you can then use the variables $name_1 - $name_7 as well as $gold_1 - $gold_7

[Tekton]

Hey, wow! Thanks a lot! I'm prolly trying to do something too big as my first home-made hack of sorts, but this is one problem out of the way. Thanks again!

[CarCdr]

Originally Posted by Dean C

Yes Colin, I'm pretty sure what you said is right. LIMIT 0,6 would return the first 7 seven rows.

It is as I said...

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must be integer constants. With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return.