I have a table like this:
// table
+----+--------+------------+
| id | name | reputation |
+----+--------+------------+
| 1 | jack | 534431 |
| 2 | peter | 334 |
| 3 | amos | 1300 |
| 4 | carter | 13490 |
| 5 | basil | 1351 |
+----+--------+------------+
reputation
SELECT id, name,
CASE WHEN value >= 1000 THEN
CONCAT(TRIM(TRAILING '.' FROM SUBSTR(TRUNCATE(number/1000, 1), 1, 4)), 'k')
ELSE value
END as reputation
FROM `table`
// newtable
+----+--------+------------+
| id | name | reputation |
+----+--------+------------+
| 1 | jack | 534k |
| 2 | peter | 334 |
| 3 | amos | 1.3k |
| 4 | carter | 13.4k |
| 5 | basil | 1.3k |
+----+--------+------------+
One reason you might be opting to do it PHP is that it will give some flexibility when you want to show some other value like M(for million), or maybe show different suffix for each level of value e.g when you are in thousands you show K, and in millions you show M.
Here is some code which you can use in PHP to do that:
function getPrettyNumber($n) {
if($n>=1000000000000) return round(($n/1000000000000),1).'T';
else if($n>=1000000000) return round(($n/1000000000),1).'B';
else if($n>=1000000) return round(($n/1000000),1).'M';
else if($n>=1000) return round(($n/1000),1).'K';
return number_format($n);
}
use of round()
with 1 as second parameter is optional.
You can easily modify the above code to fit your use case.