1

I have an SQL Query

SELECT COUNT(*) AS tot_std  
FROM `login`  
WHERE `login_account_type` =
'STUDENT'

The output of this query is 9.

What I want is to add:

  • Three zeros if the number is small than 10, like 0009.
  • Two zeros if the number is small than 99, like 0099,
  • One zero if the number is smaller than 999, like 0999.
  • If the number is equal to or greater than 1000, don't add any zero.

Note that I am using PHP Codeigniter and MySQL for the project.

Please Help.

Gilad Green
  • 36,708
  • 7
  • 61
  • 95
Ajmal Razeel
  • 1,663
  • 7
  • 27
  • 51

2 Answers2

3

Try the below code

In your query, you can do like

SELECT LPAD(COUNT(*),4,'0') AS tot_std FROM login WHERE login_account_type = 'STUDENT'

In PHP

$your_value = 9;
$your_value = str_pad($your_value,4,'0', STR_PAD_LEFT);
echo $your_value;

str_pad() function will add leading zeros. The second parameter shows the total number. As you need it for thousands, I add 4. The 3rd parameter represent which character added at the beginning. The fourth parameter is the side where you need to add the zero

Arun
  • 3,640
  • 7
  • 44
  • 87
1

Use this :

  "SELECT lpad(tot_std,4,0) as tot_std FROM login WHERE login_account_type='STUDENT'" ;
User2403
  • 317
  • 1
  • 5
  • I tried this query but it does not work: SELECT COUNT(*) AS tot_std, LPAD(tot_std, 4, 0) AS lpad_std FROM `login` WHERE `login_account_type` = 'STUDENT' – Ajmal Razeel Jul 11 '16 at 12:41
  • "SELECT lpad(tot_std,4,0) as tot_std FROM login WHERE login_account_type='STUDENT'" ; – User2403 Jul 11 '16 at 12:46