stack stack - 4 months ago 12
SQL Question

How can I convert a number based on the kilo?

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 |
+----+--------+------------+


Now I want to convert the value of
reputation
to a number based on the kilo. Currently I do that by SQL. Like this:

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`


Yes it works, I want to know how can I do that by PHP? I guess doing by that would be more flexible and faster. Well is there any PHP function?




EDIT: Here is the output of query above:

// newtable
+----+--------+------------+
| id | name | reputation |
+----+--------+------------+
| 1 | jack | 534k |
| 2 | peter | 334 |
| 3 | amos | 1.3k |
| 4 | carter | 13.4k |
| 5 | basil | 1.3k |
+----+--------+------------+

Answer

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.

Comments