Page 1 of 1
Highest number in a query column
Posted: Tue May 16, 2006 3:54 am
by rocknrisk
Hi all,
Ouick question! How do you run a query to find the highest number in a field, using PHP and MySQL?
ie:
Code: Select all
A B C
1 a v
1 g t
2 b j
3 y u
3 o i
so the highest number in column A is 3.
Thank you in advance.
Clinton
Posted: Tue May 16, 2006 5:00 am
by GM
Posted: Tue May 16, 2006 5:01 am
by $phpNut
Posted: Tue May 16, 2006 5:22 am
by rocknrisk
Thank you...
but, unfortunately I already have that. Here's my code as I see it...
Code: Select all
// 1. check wishlist table for latest numWishlist for numUserID
require_once('dfdconx/bus.php');
mysql_select_db($database_dfdbiz, $dfdbiz);
$query_rstCheckWishList = ' SELECT MAX(numWishlist)
FROM mtblwishlist
WHERE numUser = "'.$numUserID.'"';
$rstCheckWishList = mysql_query($query_rstCheckWishList, $dfdbiz) or die(mysql_error());
// 2. add 1 to numWishlist
$numWishlist = (int)$rstCheckWishList + 1;
echo $numWishlist.'<br>';
I get 15 (fifteen) when I expect 2 (using my test data - I only have 1 existing row)
Can you see an error? Please.
Cheers.
Clinton
Posted: Tue May 16, 2006 5:35 am
by JayBird
what is the data in your tabe and your table structure?
Moced to Databases
Posted: Tue May 16, 2006 5:49 am
by rocknrisk
Thanks hey... ;o)
I have headers:
idsWishlist,
dteCreated,
numUser,
numWishlist,
numSessID,
txtPurchase,
numPorSNumber,
numQuantity,
curPrice, and
txtSize
I one line of test data is:
1,
2006-05-16 06:00:18,
3,
1,
1234567890,
Item,
2,
1,
50.00, and
NULL
... so MAX(numWishlist) to me is 1. Then $numWishlist is one greater to add the next Wishlist.
NIce one...
Posted: Tue May 16, 2006 5:54 am
by JayBird
You have executed the query, but you haven't actually returned any results. Your code should look more like this
Code: Select all
// 1. check wishlist table for latest numWishlist for numUserID
require_once('dfdconx/bus.php');
mysql_select_db($database_dfdbiz, $dfdbiz);
$query_rstCheckWishList = 'SELECT MAX(numWishlist) as maxWishList
FROM mtblwishlist
WHERE numUser = "'.$numUserID.'"';
$rstCheckWishList = mysql_query($query_rstCheckWishList, $dfdbiz) or die(mysql_error());
$line = mysql_fetch_array($rstCheckWishList, MYSQL_ASSOC);
$maxWishList = $line['maxWishList'];
// 2. add 1 to numWishlist
$numWishlist = (int)$maxWishList + 1;
echo $numWishlist.'<br>';
Posted: Tue May 16, 2006 6:09 am
by rocknrisk
Thank you. It worked great.
Cheers,
Clinton