[SOLVED] How to get the largest number from a MySQL database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ljCharlie
Forum Contributor
Posts: 289
Joined: Wed May 19, 2004 8:23 am

How to get the largest number from a MySQL database?

Post 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
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Code: Select all

select max(SectionID) from table_name
ljCharlie
Forum Contributor
Posts: 289
Joined: Wed May 19, 2004 8:23 am

Post by ljCharlie »

Nice! I'll give that a try.

Thank you very much!

ljCharlie
ljCharlie
Forum Contributor
Posts: 289
Joined: Wed May 19, 2004 8:23 am

Post 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
jabbaonthedais
Forum Contributor
Posts: 127
Joined: Wed Aug 18, 2004 12:08 pm

Post by jabbaonthedais »

Can't you just use select, and sort the Section ID column descending?
swdev
Forum Commoner
Posts: 59
Joined: Mon Oct 25, 2004 8:04 am

Post 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
ljCharlie
Forum Contributor
Posts: 289
Joined: Wed May 19, 2004 8:23 am

Post by ljCharlie »

swdev, many thanks for your help! You are one very clever dude! It works!

Again thanks!

ljCharlie
swdev
Forum Commoner
Posts: 59
Joined: Mon Oct 25, 2004 8:04 am

Post by swdev »

Your more than welcome

Keep up the coding :D
Post Reply