PHP Mysql MAX() returning wrong result

Question

PHP:

$result = mysql_query("SELECT MAX(something) AS something FROM users");
$row = mysql_fetch_assoc($result);
$max =  $row["something"];
echo $max;

Mysql:

+-----------+----------+
| Something | Name     |
+-----------+----------+
| 9         | John     |
| 984       | Somebody |
| 1         | Who      |

Code results: 9, the question is why? Mysql "something" type is "text". My mysql table is bigger than this above, but it still results not the biggest "something".

There's your answer; it's not an int, it's text. MySQL doesn't do math on that type. Try and add ABC then multiply by JOHN ;-) — Funk Forty Niner, Aug 26 '15 at 13:43
When you learn to count as a kid, you use numbers, not "text". Programming is the same, the maximum of a string means absolutly nothing... — Hearner, Aug 26 '15 at 13:44
I agree with @Fred-ii- in order to do any mathematical operations you need to set them to `INT` not `TEXT` — CodeGodie, Aug 26 '15 at 13:44
I was going to edit my comment earlier about casting it. Just saying. Wondering why you're using integers in a text column though to start with. Big question ? mark there. — Funk Forty Niner, Aug 26 '15 at 13:47
If you can, you should [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) [statements](http://php.net/manual/en/pdo.prepared-statements.php) instead, and consider using PDO, [it's really not hard](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Aug 26 '15 at 13:51
Until we get it hammered into reality @CodeGodie ¯\\_(ツ)_/¯ — Jay Blanchard, Aug 26 '15 at 14:01

juergen d · Accepted Answer · 2015-08-26T13:45:23.833

1

Either do a math operation on the column to trigger an auto-cast

SELECT MAX(something * 1) AS something FROM users#

or cast explicitly with

SELECT MAX(cast(something as signed)) AS something FROM users

but even better - if that column only contains numbers then change the data type of that field ti int for instance.

edited Aug 26 '15 at 13:45

answered Aug 26 '15 at 13:44

juergen d

201,996
37
293
362

care to explain this? I'm having a danish myself to satisfy my hungry tummy ;-) – Funk Forty Niner Aug 26 '15 at 13:45
why not set the columns to `INT` instead? – CodeGodie Aug 26 '15 at 13:45
@CodeGodie I'm wondering that myself and why OP is using integers to start with. – Funk Forty Niner Aug 26 '15 at 13:49
1

Even if there's a reason why OP needs them as `TEXT`, it is just bad design from my perspective. – CodeGodie Aug 26 '15 at 13:50
2

@ArnasMarnas As stated; it's bad design. This will mean that you will have to cast everytime you want to perform a mathematical operation, using MySQL's other aggregate functions. If you're in an early stage, best to rethink this. *My 2 cents of course*, which may be worth a pound of gold later on. Having to "cast", means more code for what's needed, including resources. – Funk Forty Niner Aug 26 '15 at 13:57

PHP Mysql MAX() returning wrong result

1 Answers1