Page 1 of 1

How to get the largest number from a MySQL database?

Posted: Thu Oct 28, 2004 11:15 am
by ljCharlie
I have a table that have multiple columns. One of those columns is the Section ID column. In this column, the section ID start from 1, 2, 3, 4, 5 and so on. Can anyone show me a query that will give me the highst section ID from this column? For example, the Section IDs that I have are 1, 2, 3, 4, 5, 6, 7. What is the select query that will give me 7?

ljCharlie

Posted: Thu Oct 28, 2004 11:17 am
by Weirdan

Code: Select all

select max(SectionID) from table_name

Posted: Thu Oct 28, 2004 11:19 am
by ljCharlie
Nice! I'll give that a try.

Thank you very much!

ljCharlie

Posted: Mon Nov 01, 2004 10:20 am
by ljCharlie
Here's what I tried:

Code: Select all

$query_SelectMax = "SELECT max(sectionID) FROM photoGallery";
			$rsMAXID = mysql_query($query_SelectMax, $alumniConnection) or die(mysql_error());
			$totalRows_rsMAXid = mysql_num_rows($rsMAXID);
			if($totalRows_rsMAXid > 0){
				//while($row_rsMAXid = mysql_fetch_assoc($rsMAXID)){
					$row_rsMAXid = mysql_fetch_assoc($rsMAXID);
					$sectionID = $row_rsMAXid["sectionID"];
					$sectionID +=1;
					echo "Section ID: ".$sectionID."<br>";
					//}
				}
It's not giving me the largest number. The query gave me nothing. However, when I run the query in phpMyAdmin, it does give me the correct value. What's going on with my code?

ljCharlie

Posted: Mon Nov 01, 2004 10:33 am
by jabbaonthedais
Can't you just use select, and sort the Section ID column descending?

Posted: Mon Nov 01, 2004 10:34 am
by swdev
Line 7 is the problem line

Code: Select all

$sectionID = $row_rsMAXid["sectionID"];
change this to

Code: Select all

$sectionID = $row_rsMAXid['max(sectionID)'];
That is because SQL has named the coulmn 'max(sectionID)' as you did not explicity give it a name.
If you want the column to have a more meaningful name, say LastId, then change the following 2 lines

Your code

Code: Select all

$query_SelectMax = "SELECT max(sectionID) FROM photoGallery";#
.
.
.
$sectionID = $row_rsMAXid["sectionID"];
to

Code: Select all

$query_SelectMax = "SELECT max(sectionID) as LastId FROM photoGallery";
.
.
.
$sectionID = $row_rsMAXid['LastId];
You can be even cleverer than that and name the column NewId as follows, and it will have the newId in it

Code: Select all

$query_SelectMax = "SELECT max(sectionID)+1 as NewId FROM photoGallery";
.
.
.
$sectionID = $row_rsMAXid['NewId];
Hope this helps

Posted: Mon Nov 01, 2004 10:43 am
by ljCharlie
swdev, many thanks for your help! You are one very clever dude! It works!

Again thanks!

ljCharlie

Posted: Mon Nov 01, 2004 10:46 am
by swdev
Your more than welcome

Keep up the coding :D