Jump to content


Photo

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


  • Please log in to reply
6 replies to this topic

#1 jasonc

jasonc
  • Members
  • PipPipPip
  • Advanced Member
  • 841 posts

Posted 11 June 2006 - 09:48 PM

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.

#2 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 11 June 2006 - 11:46 PM

try this:

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

this might also work

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


#3 jasonc

jasonc
  • Members
  • PipPipPip
  • Advanced Member
  • 841 posts

Posted 12 June 2006 - 09:29 AM

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) View Post[/div][div class=\'quotemain\'][!--quotec--]
try this:

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

this might also work

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


#4 radalin

radalin
  • Members
  • PipPipPip
  • Advanced Member
  • 179 posts

Posted 12 June 2006 - 09:52 AM

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

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 :)
Roy Simkes
Yet Another Parkyeri Developer

#5 jasonc

jasonc
  • Members
  • PipPipPip
  • Advanced Member
  • 841 posts

Posted 12 June 2006 - 10:15 AM

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) View Post[/div][div class=\'quotemain\'][!--quotec--]
Hmmm I'm not quite sure but instead of
$number = mysql_result($res,0);
try this
$number[0] = mysql_result($res,0);

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]

#6 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 12 June 2006 - 12:39 PM

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).

#7 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 12 June 2006 - 05:01 PM

[!--quoteo(post=382789:date=Jun 12 2006, 06:15 AM:name=jasonc)--][div class=\'quotetop\']QUOTE(jasonc @ Jun 12 2006, 06:15 AM) View Post[/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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users