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

Code: Select all

SELECT MAX(A) FROM tableName

Posted: Tue May 16, 2006 5:01 am
by $phpNut

Code: Select all

SELECT MAX(A) FROM table;
:)

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