In this tutorial, we will learn how to add a dollar ($) sign for currency data. We will see different solutions that you can use in your application to add the $ dollar sign. So without further ado, let’s dive into it!
Introduction
There are multiple circumstances where you have to deal with the users and the currency. Let’s say you are building a side project or a live project which is going to be used in multiple countries, then the application must handle different currency information.
Therefore, it is important to show the relevant currency information on the user’s end as well. For this, you can have multiple options. First, you can insert the currency sign in the database straight with the value. Second, you can store the currency name and its symbol in another table which later you can map using the join.
We will see the above two approaches in this tutorial.
Method 1: Inserting Dollar Sign with the Value
This is the simplest method to add a dollar sign in the MySQL currency data. Let’s see how can we do it.
For this, let’s create a table to store the currency data. Note that, we are going to insert a dollar symbol or any other currency symbol with the value. Therefore, we must set the column type as varchar and not any number type.
CREATE TABLE transactions(
id INT PRIMARY KEY AUTO_INCREMENT,
value VARCHAR(10),
time DATETIME DEFAULT NOW()
);Code language: SQL (Structured Query Language) (sql)
Here we have created a simple table to store the id, value and time stamp of the insert operation.
Now let’s insert some values into the table with the dollar sign.
INSERT INTO transactions(value) VALUES
("$100"), ("$200"), ("₹3000"), ("€50"), ("€100");Code language: SQL (Structured Query Language) (sql)
Note that, we are inserting a value from the command line. If you want to insert a value using a programming language such as PHP or Java, you can simply use the concatenation functions.
The other option we have is to create a trigger. Using the before-insert trigger, we can modify the value and use the CONCAT function to add the dollar symbol.
Method 2: Using a Different Table to Store Currency Details
The above method is a little bit complicated when it comes to performing operations on the table data. In that case, you have to trim the value and extract the other part without the first character and again convert it to the number format. This is a very lengthy process.
In this method, we will create a separate table for the currency details. When we want to display the table data, we will perform the joins and show the appropriate data with the currency symbols.
Let’s write a code so that you will understand it better.
CREATE TABLE currencyDetails(
code int(5) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(10),
symbol VARCHAR(1)
);
INSERT INTO currencyDetails(name,symbol)
VALUES('Dollar','$'),('Rupee','₹'),('Euro','€');
CREATE TABLE transactions(
id INT AUTO_INCREMENT PRIMARY KEY,
value FLOAT,
currencyCode INT,
FOREIGN KEY(currencyCode) REFERENCES currencyDetails(code)
);
INSERT INTO transactions(value,currencyCode)
VALUES(1000,1),(200,3),(300,1),(400,2),(500,3),(600,3);Code language: SQL (Structured Query Language) (sql)
Here, we have created two tables. In the first table, we have stored the currency details along with the symbols. In the second table, we have stored the transaction information.
Now let’s write a query to display the data.
SELECT t.id,CONCAT(symbol,value)
FROM transactions
t JOIN currencyDetails c
ON t.currencyCode = c.code
ORDER BY t.id;Code language: SQL (Structured Query Language) (sql)Here, we have used the CONCAT function to concatenate the actual transaction value and the symbol according to that value’s currency information.
Let’s run the query and see the result.
As you can see, correct currency symbols are added to the values.
Wrapping Up
In this tutorial, we have seen two methods to add a dollar symbol as well as other currency symbols to the currency data in MySQL. As I said in the tutorial, you can try the before-insert trigger approach to modify the value before getting it stored in the table. You can try finding any other approach that you would think will be better than these two and share it with your friends!!.