Add row to mysql result

Question

I need the result of mySql with some prefixes and suffixes. This is my code:

SELECT bk_id, bk_rtype, villas_db.v_name AS villa_name FROM booking_db INNER JOIN villas_db ON booking_db.bk_vid = villas_db.v_id WHERE '2012-11-02' BETWEEN bk_date1 AND bk_date2 ORDER BY bk_id DESC LIMIT 0 , 30 

Result:

 bk_id bk_rtype villa_name 30 2 T2 29 3 V1 

So, I need a result like:

 bk_id bk_rtype villa_name booking_no 30 2 T2 B2-00030 29 3 V1 B3-00029 

While B is the reservation prefix, separated by the symbols "-" and sprintf ("% 05d", bk_id);

Please offer.

Answer 1

I found my solution:

 SELECT bk_id, bk_rtype, villas_db.v_name AS villa_name, concat( 'B', bk_rtype, '-', lpad( bk_id, 5, 0 ) ) AS booking_no FROM booking_db INNER JOIN villas_db ON booking_db.bk_vid = villas_db.v_id WHERE '2012-11-02' BETWEEN bk_date1 AND bk_date2 ORDER BY bk_id DESC LIMIT 0 , 30 

Answer 2

You can use the CONCAT and LPAD functions as shown below:

  SELECT bk_id, bk_rtype, villas_db.v_name AS villa_name, CONCAT('B',bk_rtype,'-', LPAD(bk_id, 5, '0')) AS booking_no FROM booking_db INNER JOIN villas_db ON booking_db.bk_vid = villas_db.v_id WHERE '2012-11-02' BETWEEN bk_date1 AND bk_date2 ORDER BY bk_id DESC LIMIT 0 , 30 ;