This is my table layout

[Username] | [Points]

David | 6
Smith |78
Berry | 12

I wish to see which member got the highest points and then print his/her name and her points next to it

I've started on this, but it isnt working, this is what i tried

<?php

include ("db.php");

$query = "SELECT MAX(Points) FROM table";
$result = mysql_query($query);
$arr = mysql_fetch_array($result);
$user = $arr['Username'];
$points = $arr['Points'];

echo "$user - $points";

?>

Try

SELECT `username`,`points` FROM `table` ORDER BY `points` DESC LIMIT 1

That should work, IIRC.

Change your query to:


$query = "select Username, Points FROM table ORDER BY Points DESC, Username ASC LIMIT 1";


I've tested the code and it works.

The above code takes care of an interesting case. What if you have the following data in the table:

user points
----- -------

David 30
Charles 30
Xavier 30

They all have the same number of points! Which user do you want? The query I gave you above returns the FIRST alphabetical name (in this case, Charles).

HOWEVER, what if you have a user named "david" and a user named "Charles" (notice the mixed case on the first letters).

You'll probably want to configure your sorting for "telephone order" sorting (i.e., case insensitive), but I don't remember off the top of my head how to do that in mysql. Easy enough to look up, tough.

Looks like DanX and I posted at about the same time!

Lolz, i dont mind, both are excellent suggestions, thanks allot guys

sea otter: The default in MySQL is to sort text/char fields in a case-insensitive way, according to your character set's definition. You can use a BINARY sort or binary fields for a byte value sort.

When all of this is over, DanX and I want a cut of whatever it is we've been writing for you :stickout:

Originally posted by hiryuu
sea otter: The default in MySQL is to sort text/char fields in a case-insensitive way, according to your character set's definition. You can use a BINARY sort or binary fields for a byte value sort.

Ah, great! I guess that's what I couldn't remember. That's actually pretty cool!

That's the curse of having to jump between Sybase, Oracle, PostgreSQL and MySQL sometimes within the course of even a single day! (Don't ASK!)

Thanks again, hiryuu! I love learning new stuff here.

Originally posted by sea otter
When all of this is over, DanX and I want a cut of whatever it is we've been writing for you :stickout:

hehehe, sure, why not, but that is if only my site is a succes :p

We will make sure it is. Just keep posting questions!

A lot of the answers I post here often end up helping me with my own coding projects as well.

You can never have too many code snippets lying around!