Selecting MAX number from MySQL table not working - PHP

Question

I have a table like below (a piece);

| Sl | NUMBER | Name |
----------------------
| 7  | 00007  | Name |
----------------------
| 8  | 00008  | Name |
----------------------
| 9  | 00009  | Name |
----------------------
| 10 | 00010  | Name |
----------------------
| 11 | 00011  | Name |
----------------------
| 12 | 00012  | Name |
----------------------
| 13 | 00013  | Name |
----------------------

and I have mysql statement like this;

$con = mysql_connect("localhost","root","password");
if (!$con){
  die('Could not connect: ' . mysql_error());
}
mysql_select_db("dbname", $con);
$query = 'SELECT MAX(NUMBER) FROM tablename';//varchar 15 entry
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
    $maxnumber = $row['MAX(NUMBER)'];
}
echo $maxnumber;

But it returns 9 instead of 13. Why?

Answer 1

You indicate that the column data type is varchar. MAX works with numbers. You will need to cast it or change the datatype.

$query = 'SELECT MAX(CAST(NUMBER as UNSIGNED)) FROM tablename';

Answer 2

Your number field doesn't appear to be a number data type, but a string data type instead. You must convert it to a number data type to get the right result. Try the code below:

$query = 'SELECT MAX(CAST(NUMBER as Int(12))) FROM tablename';