Syed Nizamudeen Syed Nizamudeen - 1 month ago 6
MySQL Question

leading 0 not displaying in mysql table result

just concatenating two values to one column in a mysql table. First Value from mysql table and second value from PHP constant saved as

define('ACCOUNTCODESUFFIX','016');
.

MySql query somewhat looks like

SELECT CONCAT_WS(\' \', mbrs.firstname, mbrs.lastname) AS fullname,
CONCAT_WS(\'-\',gl.code, '.ACCOUNTCODESUFFIX.') AS code
FROM allTbls
INNER JOIN generalledgertbl gl ON (allTbls.claim_type=gl.claimtype
AND allTbls.claim_category=gl.claimtitle
AND allTbls.claimoption=gl.claimoption)


Output:

fullname | code

name1 | 12345-16

name2 | 23456-16



How to have Output like this without using
LPAD()



fullname | code

name1 | 12345-016

name2 | 23456-016

Answer

This will do it, and without all that escaping that is probably causing the confusion

define('ACCOUNTCODESUFFIX','016');

$sql = "SELECT CONCAT_WS(' ', mbrs.firstname, mbrs.lastname) AS fullname, 
       CONCAT_WS('-',gl.code, '" . ACCOUNTCODESUFFIX . "') AS code 
        FROM allTbls 
            INNER JOIN generalledgertbl gl ON (allTbls.claim_type=gl.claimtype 
                                AND allTbls.claim_category=gl.claimtitle 
                                AND allTbls.claimoption=gl.claimoption)";

echo $sql;

RESULT:

SELECT CONCAT_WS(' ', mbrs.firstname, mbrs.lastname) AS fullname, 
       CONCAT_WS('-',gl.code, '016') AS code 
        FROM allTbls 
            INNER JOIN generalledgertbl gl ON (allTbls.claim_type=gl.claimtype 
                                AND allTbls.claim_category=gl.claimtitle 
                                AND allTbls.claimoption=gl.claimoption)
Comments