Converting ids into order id in join statement.

  convering id into order_id from mysql in join

    I faced a task to show orderId from normal primary key id in mysql DB. I am working on Node js on backend the orderId is the one I need  to send to frontend to display in alphanumeric type. ( eg : SPK00020, SPK00023) 20 and 23 being the primary key of the table in mysql.

Approach : 

        

• I researched about the ways to generate random number and concat it with the id and lot of ways to do in backend itself but that didn't turn out to be a optimal approach.   

  After lot of reasearch I finally found a way to generate order id from mysql itself in the same statement I need to use the join statement itself. 

LPAD method : 

syntax : LPAD(str,len,padstr);

SELECT LPAD(7, 3, 0);

+---------------+
| LPAD(7, 3, 0) |
+---------------+ 

| 007 |

we can use non-zero values also in this method.

SELECT LPAD(7, 10, '.');   

+------------------+
| LPAD(7, 10, '.') |
+------------------+
| .........7       |
+------------------+

SELECT LPAD('Cat', 21, 'Meow! ') AS Result;
+-----------------------+
| Result                |
+-----------------------+
| Meow! Meow! Meow! Cat |
+-----------------------+

I implemented this statement along with join statement.,
in my project that image is given below.
select concat("spk",lpad(ord.id,5,0)) as order_id,us.name,us.image, camp.title, camp.price, camp.created_at from users us join orders ord on ord.user_id = us.id
join campaigns camp on camp.id = ord.camp_id
where ord.brand_id = 11