Add string to mysql result
10,356
Solution 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
Solution 2
You may want to use CONCAT
and LPAD
functions as 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 ;
Related videos on Youtube
Comments
-
Wilf over 1 year
I need mySql result 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
The result are:
bk_id bk_rtype villa_name 30 2 T2 29 3 V1
So I need the result as:
bk_id bk_rtype villa_name booking_no 30 2 T2 B2-00030 29 3 V1 B3-00029
While B is a booking prefix separate by "-" and sprintf("%05d",bk_id);
Please suggest.
-
Wilf over 11 yearsyour answer is good but a bit close to the best. I found my solution anyway.
-
Yogendra Singh over 11 years@Wilf: How your solution is different than my answer?
-
Yogendra Singh over 11 years@RocketDonkey: Thanks for highlighting. In fact I answered 10+ minutes before the response. OP says, he got the solution but I couldn't notice the difference :)
-
RocketDonkey over 11 yearsHaha, I've had that happen before too - I spent 10 minutes scratching my head until I realized that there was no difference at all :)
-
Wilf over 11 yearsAppreciated for both of you. The point of the different is I use "LPAD" for the leading zeros (while your code was '000' - before you're edited) and having the number not more than 5 digits. In fact I tried googled these solution while I left a question here. But yeah, your solution is also right. Regards.
-
Yogendra Singh over 11 years@Wilf: That's correct, but even the edit using
LPAD
was made soon after posting the answer(before your input). Anyway, your problem is resolved, that's the good thing. Enjoy :-)