Jump to content

Archived

This topic is now archived and is closed to further replies.

jasonc

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

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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 :)

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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).

Share this post


Link to post
Share on other sites
[!--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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.