Find highest number in field WHERE userid=$userid in mysql database

[jasonc]

please can someone tell me how i can do a mysql search something like this... i know the following is the wrong but it is to give you the idea of what i am trying to do.


mysql_query("SELECT number FROM items WHERE userid='".$_SESSION['userid']."' AND number='the highest number in this field where the userid is $userid'");

can this be done using a method like this?

hope so :-)

thanks for your help in advance.

[michaellunsford]

try this:

[code]"SELECT `number` FROM `items` WHERE `userid`='".$_SESSION['userid']."' ORDER BY `number` DESC LIMIT 1"[/code]

this might also work

[code]"SELECT MAX(`number`) FROM `items` WHERE `userid` = '".$_SESSION['userid']."'"[/code]

[jasonc]

hi thanks.

both of these provide the same results...


'number'

but i do not get to see the highest number in that field where userid is matched, just the field name in the results.

i get results like this...


$res = "SELECT MAX(`number`) FROM `items` WHERE `userid` = '".$_SESSION['userid']."'";
$number = mysql_result($res, 0);

and number = "itemid"

how do i get the actual number in the field?

thanks



[!--quoteo(post=382689:date=Jun 12 2006, 12:46 AM:name=michaellunsford)--][div class=\'quotetop\']QUOTE(michaellunsford @ Jun 12 2006, 12:46 AM) [snapback]382689[/snapback][/div][div class=\'quotemain\'][!--quotec--]
try this:

[code]"SELECT `number` FROM `items` WHERE `userid`='".$_SESSION['userid']."' ORDER BY `number` DESC LIMIT 1"[/code]

this might also work

[code]"SELECT MAX(`number`) FROM `items` WHERE `userid` = '".$_SESSION['userid']."'"[/code]
[/quote]

[radalin]

Hmmm I'm not quite sure but instead of
[code]
$number = mysql_result($res,0);
[/code]
try this
[code]
$number[0] = mysql_result($res,0);
[/code]

as the result returns an array the type of number is an array and as there is no index pointing, it could cause a problem but not very sure about that.

But you could also try instaling pear::mdb2 which has a queryOne method which returns the first coloumn of the first row which is generaly the one you need :)

[jasonc]

i put the following into sql directly on my server.

SELECT MAX(`number`) FROM `items` WHERE `userid` = '123'

and get this as the result in number from the following command.

$number = mysql_result($res,0);

now number = "number"




[!--quoteo(post=382786:date=Jun 12 2006, 10:52 AM:name=radalin)--][div class=\'quotetop\']QUOTE(radalin @ Jun 12 2006, 10:52 AM) [snapback]382786[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Hmmm I'm not quite sure but instead of
[code]
$number = mysql_result($res,0);
[/code]
try this
[code]
$number[0] = mysql_result($res,0);
[/code]

as the result returns an array the type of number is an array and as there is no index pointing, it could cause a problem but not very sure about that.

But you could also try instaling pear::mdb2 which has a queryOne method which returns the first coloumn of the first row which is generaly the one you need :)
[/quote]

[michaellunsford]

if the field `number` is actually a number, then it should be returning the largest value. If it's varchar containing alphanumeric, then it will return numbers first, then start returning the alphabetical stuff (in alphabetical order).

[fenway]

[!--quoteo(post=382789:date=Jun 12 2006, 06:15 AM:name=jasonc)--][div class=\'quotetop\']QUOTE(jasonc @ Jun 12 2006, 06:15 AM) [snapback]382789[/snapback][/div][div class=\'quotemain\'][!--quotec--]
i put the following into sql directly on my server.

SELECT MAX(`number`) FROM `items` WHERE `userid` = '123'

and get this as the result in number from the following command.

$number = mysql_result($res,0);

now number = "number"
[/quote]
That's not directly into an SQL console -- run the query in PHPMyAdmin, or something similar, if you don't have console access. You should also drop the backticks, in case you have them as single quotes instead, which could cause this problem.