-2

I have a cell with a great number, like 650400300. I need to SELECT this number and separate to receive next format - 650,400.300. Would you mind to help me, please. Thanks in advance! I'm using PHP and MySQL.

UPDATE: Now I think the correct way to do so is to use PHP, but thank you "hd1" your answer is perfectly fits and works correctly with what I asked here.

UPDATE2: I don't need to add zero's to the end, but I need to split the whole number into peaces with commas and dots.

  • 2
    You can update your post by adding the code that you have been working on. On your current post, it will receive negative votes and might be flagged for closing. – Logan Wayne Jun 16 '15 at 07:10
  • In what language, php? – AsConfused Jun 16 '15 at 07:10
  • 1
    @AsConfused - OP might want to achieve this using PHP, jQuery and mysql which you might see in his/her tags. – Logan Wayne Jun 16 '15 at 07:11
  • How do you define how said number will be formatted? – Geoff Atkins Jun 16 '15 at 07:13
  • Right @LoganWayne i see tags was hoping to not do all 3 – AsConfused Jun 16 '15 at 07:15
  • I'm sorry, i didn't notice - PHP with MySQl. And i didn't write jquery, have no idea why it was added. –  Jun 16 '15 at 07:20
  • possible duplicate of [PHP: show a number to 2 decimal places](http://stackoverflow.com/questions/4483540/php-show-a-number-to-2-decimal-places) – Logan Wayne Jun 16 '15 at 07:38
  • The problem with doing it in mysql above what i mentioned in my answer is that it violates n-tier development principles of presentation layer, and makes a hefty stored proc unusable to consumers of data that doesn't want a string returned but rather a value. So then the developer has to duplicate code and remember to make changes in other procs. Which of course is overlooked until the help desk calls. – AsConfused Jun 16 '15 at 07:56

4 Answers4

1

Use the MySQL format function to do this:

MySQL> SELECT FORMAT(12332.2,0);
12,332
hd1
  • 33,938
  • 5
  • 80
  • 91
  • You are right. But what is the best way - use PHP or MySQL query for it? –  Jun 16 '15 at 07:31
0

Let's assume you want php and not turning mysql into a gui tool, then

number_format($number)
AsConfused
  • 325
  • 2
  • 7
  • Thank you sir, it works. PHP is a correct way to modify data from MySQL - I agree. –  Jun 16 '15 at 07:27
  • Mysql needs to be shown its propers, and other concurrent users too, by not bogging it down and turning it into a report engine – AsConfused Jun 16 '15 at 07:36
0

Note:

  • First, you must fetch that data:
  • Then use substr_replace to format the number

Code:

$number = "650400300";
$number = substr_replace($number, ",", 3, 0);
$number = substr_replace($number, ".", 7,0); /* RETURNS 650,400.300 */

Using number_format() to your given example (650400300) will give this result:

$number = number_format($number); /* 650,400,300 */
Logan Wayne
  • 6,001
  • 16
  • 31
  • 49
0

Doing this with MySQL is not really nice, it is supposed to give you what is in the database. If the database holds an integer, MySQL should give you an integer.

You can do it with php, after you have fetched the data using number_format:

$formatted_number = number_format($number);
Jorick Spitzen
  • 1,559
  • 1
  • 13
  • 25