Issue
I’m building this small real estate website so I can train my PHP + MySQL knowledge.
I want to insert the price of a house which could be, for example, 3,500,000$. Now as it isn’t possible to add a number in a row with multiple commas (whether using DECIMAL or FLOATS options), I’ve searched and found out that using FORMAT() was the best way to do it as the browser automatically filled the number with the adequate number of commas but how can I do this without making two queries?
I have the following code:
$sql_all_houses = "SELECT * FROM houses";
$get_all = mysqli_query($dbconnect, $sql_all_houses);
Is it possible to use the FORMAT() function in this same query or do I need to select every row in my database and make another statement utilizing the FORMAT() function just for my price row?
Solution
I understand that you have a numeric value in the database, that you want to display in a particular format when outputing it to your application.
In MySQL, you can indeed use format(), directly within your query:
select h.*, format(price, 0, 'en_us') as formatted_price from houses h;
This adds another column to the resultset, called formatted_price, that contains a string representation of the numeric price, with commas as thousands separators, and two decimal places (you can change 2 to 0 if you want no decimals at all).
You can also add the dollar sign if you like:
concat(format(price, 0, 'en_us'), '$')
Answered By – GMB
Answer Checked By – Jay B. (BugsFixing Admin)